Mike Koepke reported a problem he was having with the Jerome’s Keywords plugin when he searched on a tag that contained a single quote:

WordPress database error: [You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 's Creek' OR jkeywords_meta.meta_value LIKE 'Wilson's Creek,%' OR jkeywords_meta.' at line 1] SELECT DISTINCT * FROM wp_posts LEFT JOIN wp_postmeta AS jkeywords_meta ON (wp_posts.ID = jkeywords_meta.post_id) WHERE 1=1 AND post_date_gmt


I was unable to reproduce the behavior on my version, but it appears from the error message that the quote is not getting “quoted“. So, I added the following line to the “keywords_postsWhere” function in jeromes-keywords.php:

$keyword = str_replace("'", "''", $keyword);

Using the simple quote-doubling escape route. This solved the problem for Mike, but it didn’t work with my version, and from the resulting error message I could see that in my version the quote was already being escaped via a backslash (\). Modifying my fix to use backslash quoting didn’t work for me, either, because then the backslash gets backslashed.

Comparing versions, I was using MySQL 3.23.54 with PHP 4.4.1. Mike is on MySQL 4.1.14 and PHP 5.0.4. Then Mike tried this out on a local server with MySQL 5.0.22 and PHP 5.14, and got the same results as I did. So we both concluded that something got broken in MySQL or PHP between my early version and Mike’s live version, but has since been fixed again. Or was there some option that needed enabling on Mike’s live server? My Google searches for an explanation of this phenomenon were fruitless.

Clusty to the rescue. Clusty lead me to this post. It turns out that PHP has a runtime option, magic_quotes_gpc, that is enabled by default. This option automagically escapes quotes, backslashes, and nulls in a GET, POST, or cookie with a backslash. Bingo.

As with most (all?) PHP options, you can query the setting of magic_quotes_gpc at runtime via (20 guesses?) get_magic_quotes_gpc(), natch. So I changed my fix as follows:

if (!get_magic_quotes_gpc()) {
$keyword = addslashes($keyword);
}

You can download the corrected version below. If Jerome would like to adopt this fix in his master version, I’ll take down my copy and point to him instead. Johannes, you might want to implement a similar test in your Jerome’s Keywords Manager plugin. Sorry to give you one more place to go after your “final destination”.

UPDATE: Jerome has released a 2.0 beta version of the plugin that appears to address this issue. Mike Koepke, can you verify? If you’re using my tag cloud widget, you’ll need version 2.0 of that as well.

I use the Jerome’s Keywords plugin for WordPress to do tagging on my blogs. I love it, but it has one problem: if you search on a tag that is contained within another tag, you get the aggregate results of both tags. That’s because Jerome uses a SQL WHERE clause containing LIKE ‘%keyword%’ to match tags that are in a comma-delimited list in the metadata value field.

I left a comment on Jerome’s blog about this issue, but it looks like it never even made it past moderation. In fact, no new comments on this post since May. Where’s Jerome?

I mentioned this problem to Johannes Jarolim, author of the Jerome’s Keywords Manager plugin. Johannes came up with the correct WHERE sub-clause (value LIKE ‘keyword,%’ OR value LIKE ‘%,keyword’ OR value LIKE ‘%,keyword,%’ OR value = ‘keyword’) and implemented that in his plugin. But alas, that did not help my widget, which relies on the ?tag=keyword link operation provided by Jerome.

So, I felt like my best alternative would be to correct Jerome’s plugin and post it here. If Jerome adopts this correction (or something equivalent) and lets me know about it, I’ll take down this download and point over to his site instead.

UPDATE: I made yet another correction to this plugin, so head on over here for the latest and greatest.

UPDATE: Jerome released a 2.0 beta of the plugin that corrects this problem. Get the whole scoop.