mysql should i use apostrophe in mysql queries?

Question

What is the correct way of writing a query to a MySQL database on numeric data-types:

  SELECT * FROM accounts WHERE id = 5;

or

  SELECT * FROM accounts WHERE id = '5';

Mainly I prefer the last one, using ' because it is more consistent with text data-types.

Does it effect the performance?

Answer 1

Quotes are for strings, MySQL is going to read those quotes and then cast it to an integer, this is slower then just handing it an int to begin with.

Honestly the performance difference is minor, but it is just like writing a program that stores numbers in strings and then casts to int when it needs to do some math. This is bad practice.

Answer 2

I doubt that you can measure any noticable difference between the speed of the two queries. If you care about the performance you should ensure that you have an index on the id column. If you do, both queries will be very fast.

However there are security considerations.

The official MySQL opinion

The MySQL client security guidelines recommend that you do use quotes.

A common mistake is to protect only string data values. Remember to check numeric data as well. If an application generates a query such as SELECT * FROM table WHERE ID=234 when a user enters the value 234, the user can enter the value 234 OR 1=1 to cause the application to generate the query SELECT * FROM table WHERE ID=234 OR 1=1. As a result, the server retrieves every row in the table. This exposes every row and causes excessive server load. The simplest way to protect from this type of attack is to use single quotation marks around the numeric constants: SELECT * FROM table WHERE ID='234'.

Emphasis mine.

My opinion

Although the documentation recommends the use of quotes, it is neither necessary nor sufficient to prevent the attack it describes. For example, changing the attacker's string to 234' OR '1'='1 would defeat their approach.

In my opinion, a better way to make your application secure is to use parameterized queries instead of putting user values directly into the string.

If for some reason you can't use parameterized queries, then don't use quotes but ensure that the variable does in fact contain an integer by using the intval function.