MySQL Bugs: #400: Security problems with backslash

Bug #400	Security problems with backslash
Submitted:	8 May 2003 10:53	Modified:	9 Mar 2005 18:52
Reporter:	Peter Deacon	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S4 (Feature request)
Version:	4.1	OS:	Windows (win32)
Assigned to:		CPU Architecture:	Any

Description:
From your documentation... IMHO Supporting \' is dangerous and needs to be removed as it allows circumventing escape parsers which simply double up single quotes.  You can't get away with this on oracle, sqlserver or sybase.

mysql> SELECT 'hello', '"hello"', '""hello""', 'hel''lo', '\'hello';
+-------+---------+-----------+--------+--------+
| hello | "hello" | ""hello"" | hel'lo | 'hello |
+-------+---------+-----------+--------+--------+

mysql> SELECT "hello", "'hello'", "''hello''", "hel""lo", "\"hello";
+-------+---------+-----------+--------+--------+
| hello | 'hello' | ''hello'' | hel"lo | "hello |
+-------+---------+-----------+--------+--------+

How to repeat:

Suggested fix:
Do not allow \' or \" syntaxes or make it a configurable option.

All backslash escape characters are ambiguous in the context of ODBC and should be removed from the ODBC interfaces.

This is a bit hard to change as most MySQL clients assumes that one has to escape \ in MySQL strings.

I have put on our todo in 4.1 to add an option to not espace things with \.  The main problem is just to get the client mysql_escape_string() function to know when to escape and when to not escape strings.

Looks like bug 6368 is a progess on this bug.

I agree, only single quotes should need escaping, in keeping with ISO/ANSI rules, and that special backslash treatment will blindside developers who come from Oracle, Sybase, MS etc. 

Legacy client are not a concern for me; I intend to only run using the NO_BACKSLASH_ESCAPE mode, and hopefull the PreparedStatement side of JDBC/ODBC/etc drivers will be able to tell what mode the server's in (I personally use raw statements more often that PrpdStmts). And the php clients I have will have to be coerced into not using the escapecslashes function, in favor of a more standard escape function.