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

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 #')

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!

StackExchange Database Administrators Q#5553, answer score: 15