This is MySQL’s Bad Idea #573 (after #384, which I’ve blogged about before) I’ve just had a terrible experience with a bug report from the jOOQ User Group, related to escaping of backslashes in string literals in MySQL. First, I thought to myself, whatever. SQL doesn’t escape backslashes. The only escape character within a string literal according to the early SQL standards is the quote as in quote quote. Citing from SQL-1992 (slightly simplified):
<character string literal> ::=
<quote> [ <character representation>... ] <quote>
<character representation> ::=
<nonquote character>
| <quote symbol><nonquote character> ::= !! See the Syntax Rules.
<quote symbol> ::= <quote><quote>
Alright? Crystal clear. There’s no escaping other than <quote><quote>
The only time when you will need to be able to escape something is with the LIKE predicate, in case you want to escape % and _ symbols. You can then use the ESCAPE clause:
<like predicate> ::=
<match value> [ NOT ] LIKE <pattern>
[ ESCAPE <escape character> ]
I have now learned, that MySQL (and of course MariaDB) unlike any other database also support quoting with backslashes, similar to Java and other languages. That’s not a problem per se, although from a cross-vendor compatibility perspective, it’s quite nasty. But then, I’ve discovered there is a flag called NO_BACKSLASH_ESCAPES:
- https://dev.mysql.com/doc/refman/5.7/en/server-sql-mode.html#sqlmode_no_backslash_escapes
- https://stackoverflow.com/questions/9428087/disable-escape-characters-in-a-mysql-query
This just reminds me of PHP’s horrible magic quotes. In fact, combine arbitrary configurations of PHP and MySQL on your server and good luck to you of ever getting string literals right. Sigh.