1

It may depend on the database type, but is there a preference (by the database and not the coder) or is it better to use quotes? Is it:

  • Faster?
  • Less error prone?
  • Helps prevent injection (if using a PDO or not)?

Assume there is nothing requiring the use (spaces, reserved words, etc.).

MySQL:

SELECT `id` FROM `table` WHERE `name` = '$name';

ANSI:

SELECT "id" FROM "table" WHERE "name" = '$name';

vs:

SELECT id FROM table WHERE name = $name;

This answer talks about the requirement to use quotes in MySQL, but I'm interested in when it's not required by the db, but it might be preferred/better for the aspects (and perhaps more) that I listed above.

Community
  • 1
  • 1
MECU
  • 770
  • 1
  • 11
  • 25
  • We started using them because it seemed cleaner as you can use any ifentifier without the risk of hitting a keyword, but then we stopped again because it made the queries much less readable. – AndreKR Jan 02 '13 at 16:30

4 Answers4

4

Quotes around identifiers are used only during the query parsing stage - a stage that goes through the SQL statement, and figures out its syntactic elements. Compared to other stages (query optimization, query execution, and passing the results back to the caller) the parsing stage is relatively short. Therefore, you should not expect any measurable speedup or slowdown from using quotes around your identifiers, regardless of your particular RDBMS.

As far as being more or less error prone goes, missing quotes around multipart identifiers become apparent very quickly during the development stage, so the practice of placing quotes everywhere it is not worth the trouble, because the readability to humans suffers significantly.

Finally, adding quotes around identifiers would not help you prevent injection attacks; same goes for not placing quotes around all identifiers. Many SQL script generators take this route to avoid if statements all over the script testing if the identifier is multipart or not.

The only situation where quoting all identifiers is a good idea is when you generate SQL programmatically, and the results are not intended for human readers.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • *...when you generate SQL programmatically, and the results are not intended for human readers* and yet practically all SQL generators use quoted identifiers – Conrad Frix Jan 02 '13 at 18:06
3

I am not a fan of using quoted identifiers. They take too long to type and they clutter the code, making it harder for humans to read.

Also, I prefer to discourage the use of reserved words as identifiers in SQL. To me, this is just good practice. I mean, who wants to read something like:

select `select`, `from` as `as`
from <some really messed up table>
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0

I use the the bottome version because I find it easier to read but it depends on the way you echoed your query. There is no difference in the performance as it reads past the quotes unless it is need so you really don't know it unless writing a WHERE clause in your query that contains more than one variable.

Dustin Koeller
  • 65
  • 1
  • 9
-1

I'd say it's faster NOT to use quotes for numeric identifiers; why add the potential overhead of a data type conversion to a query you want (presumably) to be as performant as possible?

Darth Continent
  • 2,319
  • 3
  • 25
  • 41