MySQL Bad Idea #573

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:

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.

FluentDOM, another mimick of jQuery DOM manipulation, in PHP

The triumph of jQuery over any other XML API seems prominent, in many languages. Here is another example of a nice jQuery-port to PHP: FluentDOM.

http://fluentdom.github.com/

Similar to jOOX, FluentDOM aims to combine a jQuery-like fluent API with XPath and general DOM XML manipulation. Here are some simple examples taken from the FluentDOM documentation:

// read a file and set the message tag's content
echo FluentDOM($xmlFile)
  ->find('/message')
  ->text('Hello World!');

// Find the <root> first then the second element in it
var_dump($fd->find('/root')->find('*[2]')->item(0)->textContent);

// Append elements to an object
$menu
  ->append('<li/>')
  ->append('<a/>')
  ->attr('href', '/sample.php')
  ->text('Sample');

I’m in contact with the developers of FluentDOM. As always with OSS, there is great potential for synergy, which in the end will make both products better. For jOOX, this means that loading of files/streams is going to be a nice plus. XPath is already implemented in the upcoming release 0.9.2. On the other hand, maybe FluentDOM can get inspiration from jOOQ’s document creation syntax (which isn’t part of jQuery):

$("root",
  $("element",
    $("child", "text"),
    $("child", "more text")));

… which will create

<root>
  <element>
    <child>text</child>
    <child>more text</child>
  </element>
</root>

Excited as always, let’s get back to hacking! :-)

See the latest progress here: http://code.google.com/p/joox/