Does putting single quotation marks around numeric constants really protect from SQL injection in MySQL?

Question

The manual seems to suggest that using quotes around numbers is sufficient to protect from SQL injection.

According to section 5.3.1. General Security Guidelines of the MySQL 5.1 Reference Manual:

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'. If the user enters extra information, it all becomes part of the string. In a numeric context, MySQL automatically converts this string to a number and strips any trailing nonnumeric characters from it.

Does that mean that the user is protected if the user enters the value 234' OR 1=1 # ? (i.e. to generate the query SELECT * FROM table WHERE ID='234' OR 1=1 #')

Answer 1

Short answer, no. The quoting trick is easily defeated by including your own closing quote and then a comment symbol to eliminate the final concatenated quote, precisely as in your example.

To protect yourself from SQL injection you must use bind variables. Changing your example to SELECT * FROM table WHERE ID = :X and then binding the user's input to X solves the problem instantly and completely. It is impossible to over-emphasize how important this practice is!

Answer 2

User just has to enter 234' OR 1=1 -- (with the trailing space) to break this.

Answer 3

The excerpt provided above will not prevent SQL injections. Even with single quotes the input can be modified to manipulate the quotes. With quotes the query will be:

SELECT * FROM table WHERE ID='234', where the input is 234

Now consider the injected input: 234'; select * from passwords;select * from table where id='234. This will create a query block of:

SELECT * FROM table WHERE ID='234'; select * from passwords;select * from table where id='234'

Depending on the front end language used, in my case PHP, there are built in functions like mysql_real_escape_string that can be used to reduce SQL injections. The only comment I can say about the documentation (apart from @mrdenny's humorous one) is that without the quotes, there are more injection possible.

Answer 4

Generally, it's not such a bad "guideline" if you are using some kind of magic_quotes mechanism (which is bypassable only if you are using GBK / Big5 or familiar character set(s) at the backend DBMS). It should properly filter/preformat all occurrences of character ' which can easily enter the SQL injection into that statement (e.g. with payload you've stated 234' OR 1=1 #).

But, to be 100% safe you should definitely use ONLY "parameterized" SQL statements!