Masem has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to keep to using placeholders for my DBI query statements to avoid problems with problem user input. Unfortunately, I'm having a problem with trying to use placeholders for items in a REGEXP. The item I am searching for is on a ;-separated list, and I'm trying to match the whole word, so /(^|\;)$word(\;|$)/ would be an equilvalent perl expression.

I've tried to use (as the regexp expression) '(^|\;)?(\;|$)', but the DBI::Mysql handler doesn't seem to recognize that ? as a placeholder (and complains that it's expecting 0 inputs when I give it one). I've also tried using "REGEXP ?", then passing the regexp expression with the desired field filled in, but the quoting that DBI does chews up the expression beyond usability.

I can do all the usual checks on the word before putting it into a DBI SQL expression that DOESN'T use placeholders, but I'd like to know if there's any possibilities for this.

Replies are listed 'Best First'.
Re: DBI Placeholders and REGEXP
by yakko (Friar) on Feb 15, 2001 at 06:40 UTC
    Well, in PHP, I solved that by assigning the RE to a variable.

    Here's how it would look in perl, somewhat: 

    my $sth=$dbh->prepare('SELECT * FROM foo WHERE something REGEXP ? ORDE
+R BY fate;');
my $re='(^|\;)' . $input . '(\;|$)';
$sth->execute($re);

    [download]
    ... in my experience, and as dws says, these regexps most likely will -not- be handled by MySQL. I tried a similar snippet using a regexp the MySQL understands, and it worked as expected. The concept of putting your RE into a string and then using that in $sth->execute() is valid.

    --
    Me spell chucker work grate. Need grandma chicken.

[reply]
[d/l]
[select]
      Gah, ok, I was pretty sure I tried this before, and this failed to work, but it's working now.

      I do know that the quoting functions that occur during the execute do try to "\" the existing backslashes as based on the mysql log, so there's some issues with other quoted characters, but since ';' has no apparent special meaning in regex, I can not worry about escaping it, and the resulting regex gets passes untouched to mysql.

      I will still need to minimize the character set that the cgi param will pass to this, of course, but forutnately, all I have is A-Za-z0-9\- for this column, so that will make it easy to do.

[reply]
Re: DBI Placeholders and REGEXP
by $code or die (Deacon) on Feb 15, 2001 at 05:45 UTC
    There is no support for Perl regular expressions in SQL. You'll need to do an SQL equivalent, e.g. % is the same as .* and ? is the same as .+ IIRC

    You'll need to convert your regular expressions to something more SQL-like

    Update: Do a search on the "SELECT .. WHERE .. LIKE" statement. Check this SQL Resource or search the mySQL site.

    Update2: I am wrong. Check here for info on REGEXP.

    $code or die
    Using perl at
    The Spiders Web
[reply]
[reply]
      I know for sure that REXEXP works -- I'm using it currently. :) This is with mysql, and the docs say it aims for Posix like functionality, so while I don't expect it to handle some of the re's that pass PM's nodes, something simple like what I have above is doable (I can enter this from the direct db connection).

      LIKE could work, but it requires 'extra' to handle the cases where I may have subset matches: eg, if I'm looking for ABC, and both ABC and ABCD exists, I'll get both. To solve the case where the items are in a ;-delimited list, I'd need 4 LIKE phases.. LIKE "ABC;%", LIKE ";ABC;", LIKE "%;ABC", and LIKE "ABC".

[reply]
        Please post a code fragment that shows how you're setting up the query. That'll give us more to go on.
[reply]

Back to Seekers of Perl Wisdom