I had a weird problem with Drupal the other day.
I’m using a module I wrote which uses the views_query_alter() hook to change a views’ query from doing “node.title = blah” to “node.title like blah” for a super-simple search engine. Should be simple enough. Well, it turned out that people testing it were having erratic results depending on what they tried to search. At first it was thought that the search was not being case insensitive because searching for “silly” returned no results while searching for “Silly” worked fine, but the database tables were configured correctly (MySQL, table collation set to “utf8_general_ci”), so it had to be something else.
The original query WHERE statement was “node.title = ‘%s’”, so I wrote a function that looked something like this:
function mymodule_views_query_alter(&$query, $view, $summary, $level) {
if ($view->name == ‘mycustomsearch’) {
$query->where[2] = “node.title like ‘%%%s%%’”;
}
}
Normally this would be processed by the Views module and would turn the query WHERE statement into “node.title like ‘%silly%’”, which is what we wanted. Except that in certain circumstances it wasn’t happening like that.
After some digging, thanks to the Devel module, I discovered that the query was actually ending up as “node.title like ‘nodeilly%’” if the word entered was “silly”. You’ll note, first off, that instead of the first percentage sign it says “node”. You’ll also note that the first “s” is missing. As soon as I saw this I realized what was going on - somewhere the Views module was ending up with the string “node.title like ‘%silly%’” and for some reason the “%s” part was getting replaced with the table name, i.e. “node”. Go figure.
So then came the fix.
As I was in a slight time crunch I didn’t feel I had the time to dig further unto the Views code to work out a fix, so for now I just searched for alternative SQL syntax. This seemed odd to me, though, as I was basically looking for another way in MySQL for doing an SQL “LIKE” statement. As it turns out, there is another way - regular expressions.
MySQL supports a set of commands for doing regular-expression-based comparisons using the command REGEXP or RLIKE (one is an alias for the other). The syntax ended up like the following:
$query->where[1] = “node.title rlike ‘.*%s.*’”;
Before I could unleash that upon the world I wanted to filter the input a bit more to avoid potential for security problems. In a rather heavy-handed fashion I set the input string to be filtered as follows, which only allows numbers, letters and spaces through:
$query->where_args[4] = ereg_replace(”[^A-Za-z0-9 ]“, “”, trim($query->where_args[$]));
Putting it all together I finally had a search engine that finally let people search for the word “silly”. Go figure.
Note: I do intend delving into the code to work out a hopeful fix for the root problem rather than a workaround in my module, but I felt completing the project sooner was more important than having a 100% correct fix, which will come later.
Those wonderful people in the Drupal community have been awesome citizens and have stepped up to the plate after the Drupal documentation team opened the doors for anyone to contribute to the official online “handbook” documentation.