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 value234
, the user can enter the value234 OR 1=1
to cause the application to generate the querySELECT * 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 #'
)
Best Answer
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!