#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    99
    Rep Power
    11

    Passing strings as variables to Postgres regular expression SELECT statements


    I've written a DBI subroutine that looks up the value of a key based on a match of a value assigned to the key. The code for the subroutine is:

    Code:
    sub lookupValueSQL
    {
    	my ($lookup_field, $table, $match_field, $match_value) = @_;
    	my $match;
    
    	my $sth = $dbh->prepare( "SELECT $lookup_field From $table WHERE $match_field ILIKE \'$match_value\'" );
    	$sth->execute();
    	$sth->bind_col(1, \$match);
    	$sth->fetch;
    	$sth->finish();
    
    	return $match;
    }
    The subroutine as written works in the application for which I've written it. However, I can anticipate wanting to execute similar statements with matches complicated enough to require the use of regular expressions. So I tried substituting the equivalent regular expression ~* for ILIKE. When I did so I got the following error statement: "Invalid regular expression: empty expression or subexpression at script.cgi line 229." Line 229 is the line reading $sth->execute();.

    I tried fiddling with various escape combinations and quotation marks to no avail -- I can get rid of the error statement but not pass a string to match. It appears the problem lies in trying to pass a variable containing the string to match.

    Thus my question: Is there a way to pass strings as variables to Postgres statements containing regular expressions using DBI?

    Thanks for your help.

    -- Chris
  2. #2
  3. 'fie' on me, allege-dly
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2003
    Location
    in da kitchen ...
    Posts
    12,889
    Rep Power
    6444
    the dbi allow for the use of placeholders, try that and see how you get on, also you could build your SQL up conditionally

    Not tested, just sketched
    Code:
    $sql="select ";
    foreach (@fields) {
      $sql.=$_;
      if ($_ ne @fields[-1]) {
        $sql.= ", ";
      } else {
        $sql.= " "
      }
    }
    $sql.=" where ";
    foreach (@criteria) {
      ($field, $operator, $criterion)=split (/:/, $_); # criterion here could be a placeholder, and push it onto to an array for the execution call
      $sql.="$field $operator $criterion "; #need some to way to integrate logical operaors
    }
    HTH
    --Ax
    without exception, there is no rule ...
    Handmade Irish Jewellery
    Targeted Advertising Cookie Optout (TACO) extension for Firefox
    The great thing about Object Oriented code is that it can make small, simple problems look like large, complex ones


    09 F9 11 02
    9D 74 E3 5B
    D8 41 56 C5
    63 56 88 C0
    Some people, when confronted with a problem, think "I know, I'll use regular expressions." Now they have two problems.
    -- Jamie Zawinski
    Detavil - the devil is in the detail, allegedly, and I use the term advisedly, allegedly ... oh, no, wait I did ...
    BIT COINS ANYONE
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2004
    Posts
    99
    Rep Power
    11

    Using placeholders did the trick!


    Turns out I actually had two problems: passing the variable string and actually passing something!

    To pass a string to a regex in a select statement, use a placeholder:

    Code:
    my $sth = $dbh->prepare( "SELECT $lookup_field FROM $table WHERE $match_field ~* ?" );
    	$sth->execute($match_value);
    To insure that the match expression isn't empty, feed the regex a string that won't match if it would be fed an empty expression otherwise:

    Code:
    my $sth = $dbh->prepare( "SELECT $lookup_field FROM $table WHERE $match_field ~* ?" );
    	$sth->execute($match_value =~ /.+/ ? $match_value : 'something that should not match');
    Thanks for your help. The link to placeholders did the trick!

    -- Chris

IMN logo majestic logo threadwatch logo seochat tools logo