phroggy.com: Weblog Archive: Escaping SQL With Wildcards

Escaping SQL With Wildcards (Thursday, July 14^th, 2005)

So you're doing SQL queries, and you need to search for a substring. In MySQL (I'm not familiar with other databases), this is done with LIKE and leading and trailing wildcards. For example:

SELECT * FROM Table WHERE Field LIKE '%substring%'

This starts becoming complicated when the substring you're searching for contains wildcard characters, which need to be escaped. For example, say you're looking for the string “15% more” and you want the percent sign in the substring to be treated as a literal percent sign, not as a wildcard. Your SQL query should be:

SELECT * FROM Table WHERE Field LIKE '%15\% more%'

Perl's DBI won't handle this for you; it will leave any wildcard characters unaltered. The tricky thing is, you want to escape any wildcard characters in your original substring, but not escape the leading and trailing wildcard characters you've added. A few days ago I went looking for an easy solution to this, and spent a long time trying to explain what I wanted in #perl. We couldn't find an easy solution (and most of them didn't seem to understand the problem), so I wrote my own escaping function, which I shall now share:

sub wildquote {
  # Assumes $dbh is global
  # If not, you may want to pass it as an argument
  my($string)=@_;
  $string=$dbh->quote($string);
  $string=~s/%/\\%/g;
  $string=~s/_/\\_/g;
  $string=~s/^'(.*)'$/'%$1%'/;
  return $string;
}

So there it is. Let me know if you find this helpful, or if you know of a better solution, or if you still don't understand what I'm trying to accomplish.