Bug #76146 Grave accent character (`) is not followed with backslash when escaping it

• Submitted: 4 Mar 2015 15:09
• Modified: 18 Mar 2015 17:49
• Reporter: Marek Szymczak
• Status: Closed
• Category: MySQL Server: C API (client library)
• Severity: S3 (Non-critical)
• OS: Any
• CPU Architecture: Any

[4 Mar 2015 15:09] Marek Szymczak

Description:
Newly added mysql_real_escape_string_quote function escapes the string depending on NO_BACKSLASH_ESCAPES flag of the sql_mode variable. This is invalid for grave accents, which should be always doubled. The implementation of the mysql_real_escape_string_quote should always double grave accents.

How to repeat:
mysql_query(mysql, "set sql_mode=''");

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

mysql_query(mysql, "drop table if exists `mysql`.`t\`2`"); // FAIL (the server does not understand \`)
mysql_query(mysql, "create table `mysql`.`t\`2` (id int)"); // FAIL (the server does not understand \`)

mysql_query(mysql, "drop table if exists `mysql`.`t``2`"); // OK (`` are converted to ` by the server)
mysql_query(mysql, "create table `mysql`.`t``2` (id int)"); // OK (`` are converted to ` by the server)

mysql_query(mysql, "set sql_mode='NO_BACKSLASH_ESCAPES'");

DIE_UNLESS(mysql->server_status & SERVER_STATUS_NO_BACKSLASH_ESCAPES);

mysql_query(mysql, "drop table if exists `mysql`.`t``3`"); // OK (`` are converted to ` by the server)
mysql_query(mysql, "create table `mysql`.`t``3` (id int)"); // OK (`` are converted to ` by the server)

Conclusion: Backtick should not be escaped using the backslash - the server expects doubled ` no matter what sql_mode is.

Suggested fix:
If the string contains the grave accents, we should double every its occurrence in the string, no matter what is the current sql_mode set:

ulong STDCALL
mysql_real_escape_string_quote(MYSQL *mysql, char *to, const char *from,
                               ulong length, char quote)
{
+  if (quote != '`')
+     escape_quotes_for_mysql(mysql->charset, to, 0, from, length, '`'); // DOUBLE GRAVE ACCENTS

  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);
}

[16 Mar 2015 10:31] Marek Szymczak

Strings placed between identifiers, quoted with grave accents (`) only, are not backslashed when NO_BACKSLASH_ESCAPES is off. Grave accent characters are doubled in the string.

[18 Mar 2015 17:49] Paul DuBois

Noted in 5.7.8, 5.8.0 changelogs.

The mysql_real_escape_string_quote() C API function failed to escape
backtick (`) characters when the NO_BACKSLASH_ESCAPES SQL mode was
disabled.