Using backticks around field names

Question

After reading a couple of answers and comments on some SQL questions here, and also hearing that a friend of mine works at a place which has a policy which bans them, I'm wondering if there's anything wrong with using backticks around field names in MySQL.

That is:

SELECT `id`, `name`, `anotherfield` ...
-- vs --
SELECT id, name, anotherfield ...

Answer 1

If you ask to me, backticks should always be used. But there are some reasons why a team may prefer not to use them.

Advantages:

• Using them, there are no reserved words or forbidden chars.
• In some cases, you get more descriptive error messages.
• If you avoid bad practices you don't care, but... in real word, sometimes they are a decent way to avoid SQL injections.

Disadvantages:

• They are not standard and usually not portable. However, as long as you don't use a backtick as part of an identifier (which is the worst practice I am able to imagine), you can port your query by automatically removing backticks.
• If some of your query come from Access, they may quote table names with " (and maybe you can't remove all the " blindly). However, mixtures of backticks and double quotes are allowed.
• Some stupid software or function filters your queries, and has problems with backticks. However, they are part of ASCII so this means that your software/function is very bad.

Answer 2

if you are using some field names as default mysql or mssql values for example "status", you have to use backticks ( "select status from table_name" or "select id from table_name where status=1" ). because mysql returns errors or doesnt work the query.

Answer 3

Well, as far as I know, the whole purpose of using backticks is so you can use names that coincide with reserved keywords. So, if the name isn't colliding with a reserved keyword, I don't see any reason to use backticks. But, that's no reason to ban them, either.

Answer 4

It's a lot easier to search your code-base for something in backticks. Say you have a table named event. grep -r "event" * might return hundreds of results. grep -r "\`event\`" * will return anything probably referencing your database.