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 ...

Icterus

backticks are really handy if you want to have column names like count, type, table or similar – Net

see also #23446877 – Disjoined

@Net I think the question is, should you have column names like count, type, and table. Those are awfully ambiguous terms and in almost every case those names could be improved to be more specific. Naming your columns things like that is also dangerous and a potential source of errors, as you never know when someone might forget to add the backticks or not realize they have to. I think it's better practice to just avoid using reserved terms as column names. – Profane

I use them always and so I am not running in danger having used reserved keywords at any time. – Koel

Answer 1

Using backticks permits you to use alternative characters. In query writing it's not such a problem, but if one assumes you can just use backticks, I would assume it lets you get away with ridiculous stuff like

SELECT `id`, `my name`, `another field` , `field,with,comma` 

Which does of course generate badly named tables.

If you're just being concise I don't see a problem with it, you'll note if you run your query as such

EXPLAIN EXTENDED Select foo,bar,baz 

The generated warning that comes back will have back-ticks and fully qualified table names. So if you're using query generation features and automated re-writing of queries, backticks would make anything parsing your code less confused.

I think however, instead of mandating whether or not you can use backticks, they should have a standard for names. It solves more 'real' problems.

Issacissachar

Do we need to use them in PostgreSQL also? – Shippee

There's no need, only recommendation. It is useful to represent them quoted to avoid ambiguity with SQL keywords if in future, an SQL keyword is added that shares your fields name. The only time you /need/ to quote is when a field does share a keyword name, for instance, select count from foo vs select "count" from foo will give very different results. But postgres differs from mysql in 2 ways: 1. Fields are quoted by "". 2. Unquoted fields are case insensitive postgresql.org/docs/current/static/… – Issacissachar

Answer 2

The only problem with backticks is that they are not ANSI-SQL compliant, e.g. they don't work in SQL Server.

If there is a chance you would have to port your SQL to another database, use double quotes.

Doodlebug

Yep. Use MySQL's ANSI mode - dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html - to enable double-quotes in MySQL and thus regain cross-database compatibility. Backticks/quotes are also necessary because you never know what's going to become a reserved word in future DBMS versions. – Incondite

That's very true! One of our server applications was running fine until we applied an upgrade to our database engine, which added a new keyword. Suddenly everything that queried a particular table broke. – Choosy

@Incondite When I was new to dev, I named a column range or something like that. When we upgraded to MySQL 5 it failed because it was a new reserved word! – Woolpack

Do not use double quotes. It will not always work. For instance... DELETE FROM app_key_stores WHERE ("key" = 'c5cc4f30-31f3-0130-505e-14dae9da9fc5_range'); Query OK, 0 rows affected (0.00 sec) DELETE FROM app_key_stores WHERE (key = 'c5cc4f30-31f3-0130-505e-14dae9da9fc5_range'); Query OK, 5 rows affected (0.00 sec) – Relucent

Answer 3

To me it makes a lot of sense to use them at all times when dealing with field names.

• Firstly, once you get into the habit, it doesn't hurt to just hit the backtick key.
• Secondly, to me, it makes it easier to see what exactly are the fields in your query, and what are keywords or methods.
• Lastly, it allows you to use whatever field name you wish when designing your table. Sometimes it makes a lot of sense to name a field "key", "order", or "values"... all of which require backticks when referring to them.

Icterus

You should also add that it protects you from any future reserved words being used (which has bitten me before). – Woolpack

I actually had someone edit the extra backticks out of one of my question once, which upset me, since this reason is the exact reason I surround every variable with them – Jeunesse

It also allows for safe use of non English labels, which alone is enough to encourage the use of backticks. – Kirstinkirstyn

Answer 4

Backticks aren't part of standard ANSI SQL. From the mysql manual:

If the ANSI_QUOTES SQL mode is enabled, it is also allowable to quote identifiers within double quotes

So if you use backticks and then decide to move away from MySQL, you have a problem (although you probably have a lot bigger problems as well)

Jaysonjaywalk

Answer 5

There isn't anything wrong if you keep using MYSQL, except maybe the visual fuziness of the queries. But they do allow the use of reserved keywords or embedded spaces as table and column names. This is a no-no with most database engines and will prevent any migration at a later time.

As for easy reading, many people use caps for SQL keywords, eg.

SELECT some_fied, some_other_field FROM whatever WHERE id IS NULL;

Bandaranaike

Answer 6

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.

Gearalt

Using backticks has absolutely nothing to do with avoiding SQL injections. – Avatar

@andy it might help, since an attacker has to close it with another backtick to inject. It does little, but that's still something – Schaper

Answer 7

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.

Hendrik

In general it's not really a benefit. The tables one professionally comes across are named more like new_users_info than "general". – Pepsin

Answer 8

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.

Chouinard

Answer 9

Simple Thing about backtick `` is use for denote identifier like database_name, table_name etc, and single quote '', double quote "" for string literals, whereas "" use for print value as it is and '' print the value variable hold or in another case print the text his have.

i.e 1.-> use `model`;   
    here `model` is database name not conflict with reserve keyword 'model'
2- $age = 27;
insert into `tbl_people`(`name`,`age`,`address`) values ('Ashoka','$age',"Delhi");

here i used both quote for all type of requirement. If anything not clear let me know..

Atheistic

Answer 10

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.

Clipboard

Answer 11

The main use of backticks (`) in SQL is to use them in situations where you are going to call them again in upcoming clauses. In every other time it is recommended to use double quotes("").

For example

SELECT CONCAT(Name, ' in ', city, ', ', statecode) AS `Publisher and Location`,
    COUNT(ISBN) AS "# Books",
    MAX(LENGTH(title)) AS "Longest Title",
    MIN(LENGTH(title)) AS "Shortest Title"
FROM Publisher JOIN Book
ON Publisher.PublisherID = Book.PublisherID WHERE INSTR(name, 'read')>0
GROUP BY `Publisher and Location`
HAVING COUNT(ISBN) > 1;

In the above statement do you see how Publisher and Location is used again in GROUP BY clause.

Instead of using

GROUP BY Name, city, statecode

I just used

GROUP BY Publisher and Location

Only when such situations arise, it is useful to use backticks. In all other times using double quotes is recommended.

Pearlene