WL#8077: extend mysql_real_escape() to be aware of the string type it's escaping for

mysql_real_escape_string() now tries to escape content properly for both single-quoted and double-quoted strings in a single function. This can be done when using back-slashes, but when using the traditional SQL delimiter doubling it can't work without knowing the context.

This can lead to severe confusions for apps that expect this to work reliably.

Suggest adding a second argument to the function :

mysql_real_escape(<text>,<quote type: "|'>).

This will break the ABI so it needs to be done in trunk.

Functional Requirements

F-1 Extend the API that can escape single and double quoted strings depending on the sql mode used by the server and the string quotation used in the SQL query.

F-2 Existing API function responsible for escaping strings will exit with error when the server mode will be in a mode that not guarantees correct single and double quote escape without knowing the context, in which the string is used in the SQL query.

Non-Functional Requirements

NF-1 Existing API functions must remain the same to provide compatibility of declaration.

NF-2 API function escape_string_for_mysql usage should be limited in the client code since it does not support NO_BACKSLASH_ESCAPES server mode and strings delimited by double-quotes.

NF-3 Server code and protocol should not be modified.

Escaping the string

The string that is used in the SQL queries must be escaped in order to support correct interpretation of the special characters, which are single and double quotes.

Method of escaping the string depends on the server's sql mode.

• NO_BACKSLASH_ESCAPES mode not specified. All special sharacters, such as '\n', '\r', '\', ' ' ', '"' etc. are escaped with backslash inserted before each character, e.g.:

aa'bb" >>turns into>> aa\'bb\"

In this mode, the string is insensitive to the context, in which the string sppear (string quote character). Both queries will execute correctly:

INSERT INTO table1 VALUES('aa\'bb\"');
INSERT INTO table1 VALUES("aa\'bb\"");

• NO_BACKSLASH_ESCAPES mode is specified. In this mode, single and double quote characters that appear in the string must be escaped. Escape is done by doubling single or double quote characters, depending on the context of the SQL query.

INSERT INTO table1 VALUES('aa''bb"'); // Doubled single quotes (single-quote is a string character)
INSERT INTO table1 VALUES("aa'bb"""); // Doubled double quotes (double-quote is a string character)

SQL mode

The mode can be set globally or it it can be set per user session (per connection).

mysql> select @@global.sql_mode;
+------------------------------------------------------------------------------------+
| @@global.sql_mode                                                                  |
+------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> select @@session.sql_mode;
+------------------------------------------------------------------------------------+
| @@session.sql_mode                                                                 |
+------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,NO_BACKSLASH_ESCAPES,STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

C API

The mysql_real_escape_string function escapes the string taking into consideration sql mode in which the server operates:

• NO_BACKSLASH_ESCAPES mode not specified:

mysql_real_escape_string("aa'bb\"") produces "aa\'bb\\\"" (backslash escape)

• NO_BACKSLASH_ESCAPES mode specified:

mysql_real_escape_string("aa'bb\"") produces "aa''bb\"" (doubled single quote)

The limitation of the mysql_real_escape_string function is that it cannot escape the string, which contains double quote characters. In the result, the string cannot be placed in the SQL query, where the string is put between double-quotes:

INSERT INTO table1 VALUES("<sql_injection_sensitive_string>");

Such SQL query can lead to the SQL injection.

C API Extension

As presented above, the string quote character must be explicitly specified when escaping the string in the NO_BACKSLASH_ESCAPES mode. This enforces adding a mysql_real_escape_string like function taking an extra parameter , which will escape the string depending on the SQL string quoting character, e.g.:

• For the string to be placed between single quoted SQL string:

mysql_real_escape_string_quote("aa'bb\"", '\'') 

produces "aabb\"" and goes into:

"INSERT INTO table1 VALUES('aa''bb\"');" (single quotes are doubled)

• For the string to be placed between double quoted SQL string:

mysql_real_escape_string_quote("aa'bb\"", '"')

produces "aa'bb\"\"" and goes into:

"INSERT INTO table1 VALUES("aa'bb\"\"\");" (double quotes are doubled)

Changes to the existing API

Existing function mysql_real_escape_string should be backward compatible on the clients connecting to the MySQL server, where the NO_BACKSLASH_ESCAPES mode is not set. This mode escapes the characters in the string by inserting backslashes.

The mysql_real_escape_string function is not backward compatible, when the server mode has NO_BACKSLASH_ESCAPES flag set. In this case, the function returns error code.

Existing mysql_real_escape_string C API function

• mysql_real_escape_string function behaviour will remain the same if connected to MySQL server, where sql NO_BACKSLASH_ESCAPES mode is not set.

• mysql_real_escape_string function will exit with error code if connected to MySQL server, where sql NO_BACKSLASH_ESCAPES is set.

ulong STDCALL
mysql_real_escape_string(MYSQL *mysql, char *to,const char *from,
                         ulong length)
{
  if (mysql->server_status & SERVER_STATUS_NO_BACKSLASH_ESCAPES)
  {
    DBUG_PRINT("error", 
               ("NO_BACKSLASH_ESCAPES sql mode requires usage of the "
                "mysql_real_escape_string_quote function"));
    return (ulong)-1;
  }

  return (uint)mysql_real_escape_string_quote(mysql, to, from, length, '\'');
}

All mysql_real_escape_string function calls should be replaced with mysql_real_escape_string_quote function calls.

New mysql_real_escape_string_quote C API function

mysql_real_escape_string_quote function introduces additional parameter (char quote) comparing to mysql_real_escape_string function. The parameter will take effect when MySQL server sql mode is set to NO_BACKSLASH_ESCAPES. The string will be escaped in accordance with the specified parameter.

ulong STDCALL
mysql_real_escape_string_quote(MYSQL *mysql, char *to, const char *from,
                               ulong length, char quote)
{
  if (mysql->server_status & SERVER_STATUS_NO_BACKSLASH_ESCAPES)
    return (uint)escape_quotes_for_mysql(mysql->charset, to, 0, from, length, quote);
  return (uint)escape_string_for_mysql(mysql->charset, to, 0, from, length);
}

Example

  ASSERT(!(mysql->server_status & SERVER_STATUS_NO_BACKSLASH_ESCAPES));

  len= mysql_real_escape_string(mysql, out, "a'b\\c", 5);
  ASSERT(memcmp(out, "a\\'b\\\\c", len) == 0);

  mysql_query(mysql, "set sql_mode='NO_BACKSLASH_ESCAPES'");
  ASSERT(mysql->server_status & SERVER_STATUS_NO_BACKSLASH_ESCAPES);

  len = mysql_real_escape_string(mysql, out, "a'b\\c", 5);
  ASSERT(len == -1);
  
  len= mysql_real_escape_string_quote(mysql, out, "a'b\\c", 5, '\'');
  ASSERT(memcmp(out, "a''b\\c", len) == 0);

  len = mysql_real_escape_string_quote(mysql, out, "a'b\\c", 5, '"');
  ASSERT(memcmp(out, "a'b\\c", len) == 0);

  len = mysql_real_escape_string_quote(mysql, out, "a\"b\\c", 5, '"');
  ASSERT(memcmp(out, "a\"\"b\\c", len) == 0);