4

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)

Tom
  • 6,991
  • 13
  • 60
  • 78
  • @eyelidlessness: PHP, C# .NET 3.0, Java, VB and C (actually a language developed in-house which is just terrible at doing anything it wasn't designed for but it supports injecting C functions for "special" code) – Tom Oct 23 '08 at 14:29
  • I actually went to find language-specific built-in escape functions, but can't find any relevant documentation for C# .NET. How the hell are new developers supposed to work with it without good online documentation? – eyelidlessness Oct 23 '08 at 14:43
  • If you come back at any point, please switch the accepted answer. micahwittman's is better than mine. – eyelidlessness Mar 06 '09 at 08:42
  • Thanks for your objectivity, eyelidlessness. And, glad it helped, Tom. – micahwittman Mar 26 '09 at 05:31

2 Answers2

7

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
micahwittman
  • 12,356
  • 2
  • 32
  • 37
  • I have found more information regarding this: http://www.owasp.org/index.php/SQL_Injection_Prevention_Cheat_Sheet#MySQL_Escaping – Tom Jun 23 '10 at 08:25
4

What 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 function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

eyelidlessness
  • 62,413
  • 11
  • 90
  • 94