What are the dangerous characters that should be replaced in user input when the users' input will be inserted in a MySQL query? I know about quotes, double quotes, \r and \n. Are there others?
(I don't have the option of using a smart connector that accepts parameters so I have to build the query myself and this will be implemented in multiple programming languages, including some obscure ones so solutions such as mysql_real_escape_string
in PHP are not valid)
views:
1113answers:
2What languages do you need to support? It is much better to use a language's built-in sanitization than to write your own.
Edit: Looking at mysql_real_escape_string
on php.net:
mysql_real_escape_string()
calls MySQL's library functionmysql_real_escape_string
, which prepends backslashes to the following characters:\x00
,\n
,\r
,\
,'
,"
and\x1a
.
mysql_real_escape_string() from mysql.com docs:
The string in from is encoded to an escaped SQL string, taking into account the current character set of the connection. The result is placed in to and a terminating null byte is appended. Characters encoded are NUL (ASCII 0), “\n”, “\r”, “\”, “'”, “"”, and Control-Z (see Section 8.1, “Literal Values”). (Strictly speaking, MySQL requires only that backslash and the quote character used to quote the string in the query be escaped. This function quotes the other characters to make them easier to read in log files.)
mysql_real_escape_string() is character set aware, so replicating all its abilities (especially against multi-byte attack issues) is not a small amount of work.
From http://cognifty.com/blog.entry/id=6/addslashes_dont_call_it_a_comeback.html:
AS = addslashes() MRES = mysql_real_escape_string() ACS = addcslashes() //called with "\\\000\n\r'\"\032%_" Feature AS MRES ACS escapes quote, double quote, and backslash yes yes yes escapes LIKE modifiers: underscore, percent no no yes escapes with single quotes instead of backslash no yes*1 no character-set aware no yes*2 no prevents multi-byte attacks no yes*3 no