Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?

Perl and PostgreSQL regex

by chrishowe (Acolyte)
on Mar 03, 2009 at 13:10 UTC ( #747720=perlquestion: print w/replies, xml ) Need Help??
chrishowe has asked for the wisdom of the Perl Monks concerning the following question:

Hi monks,

I am currently trying to use Perl and Postgresql together.
I would like to insert a regex containing a variable into the SQL statement but am unsure of how to do this.
$sth_get_branch_list = $dbh->prepare(q( SELECT branch FROM postgres_database WHERE tree = 'first_tree' AND branch ~ /$root\.\d+/; ));

The basic aim of the script will be find sub branches of a root.
e.g. $root = stick, and what I want to find is all branches attached to $root, e.g. stick.1, stick.2, stick.300 etc...

I basically can't get the regex to work at all, I don't think it seems to work like normal Perl regexes, but I am not sure. I have tried a whole multitude of syntaxes.

All help greatly appreciated!

Replies are listed 'Best First'.
Re: Perl and PostgreSQL regex
by tilly (Archbishop) on Mar 03, 2009 at 14:33 UTC
    I would recommend that you first try this from the psql prompt, and only then try to put it in your code. I will also warn you that you are about to experience leaning toothpick syndrome.

    Suppose you want to match the pattern /branch.\d+/. Well for postgres you need to pass that in as a string and every \ needs to be doubled to escape them because \ already has meaning. So you get 'branch.\\d+'. But in Perl \ is special, so to pass that into postgres you have to write 'branch.\\\\d+'.

    If you are very sure that $root has no escape characters in it, you can write something like

    $sth_get_branch_list = $dbh->prepare(qq( SELECT branch FROM postgres_database WHERE tre +e = 'first_tree' AND branch ~ '$root.\\\\d+'; ));
    and it should work. But be warned that interpolating variables directly into SQL is a serious security risk because it leads to the possibility of SQL injection attacks. So you are better off with something like:
    $sth_get_branch_list = $dbh->prepare(q( SELECT branch FROM postgres_database WHERE tre +e = 'first_tree' AND branch ~ ? || '.\\\\d+'; ));
    and then passing $root into execute. This will still not do the right thing if $root has regular expression metacharacters in it, but at least it isn't a major security risk.
Re: Perl and PostgreSQL regex
by roboticus (Chancellor) on Mar 03, 2009 at 13:19 UTC
      See now I have tried your first solution but all I get is the following error:
      WARNING: nonstandard use of escape in a string literal LINE 3: AND branch ~ '$root\.\d... ^ HINT: Use the escape string syntax for escapes, e.g., E'\r\n'..
      Which is confusing me a little :-)
Re: Perl and PostgreSQL regex
by erix (Parson) on Mar 03, 2009 at 17:50 UTC

    Using bracket expressions to prevent ' leaning toothpick syndrome' (= the \\\\\\ misery) might be useful, and embedded (?x) lets you add comments to a regex, like so:

    select 'word.01234' ~ E'(?x) ^ # begin-of-string word # root word [.] # dot [[:digit:]]+ # digits... $ # end-of-string ' ; -- caveat: -- the (?x) option overrides any previously determined -- options including RE flavor and case sensitivity.

    tilly is right regarding placeholdering, of course - see also dbi, placeholder and postgres regular expression problem.

      I have found another solution actually :-)
      $sth_get_branch_list = $dbh->prepare(q( SELECT branch FROM postgres_database WHERE tree = 'first_tree' AND branch ~ ?; )); $sth_get_branch_list = $dbh->prepare $sth_get_branch_list->execute('^'.$root.'\.\d+$');
      Thanks for the help though :-)
Re: Perl and PostgreSQL regex
by doom (Deacon) on Mar 03, 2009 at 18:59 UTC
    Isn't the main trouble just that you're using a single q? This is the equivalent of single quotes: q{}, and this is the equivalent of double quotes: qq{}.
      Using q instead of qq stops interpolation from working, but doesn't make one whit of difference for the syntax errors that he is asking about.

        Not one whit, eh? Well, it's true that the code has multiple problems, but it would seem to me you need to have that one little detail right in order to have a hope of getting the right syntax errors...

        (By the way: is it my imagination, tilly, or did you just silently do an update to your own post to fix this problem?)

Log In?

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://747720]
Front-paged by Arunbear
[Corion]: Meh. Once again I find that SQLite doesn't support window functions and I want to use those nowadays :-)
[erix]: Look what I found! :P
[Corion]: Hmm - actually, I don't need them, even though they'd be nice. I just want the (say) 10 latest images, and that's easily done with a limit 10 offset 0 clause, as I don't need all top 10 images for all users.
[Corion]: erix: Sure, but this is for a really-lightweight application and I'm replacing a CSV file / JSON file for user configuration with SQLite (and optionally, Pg) :)
[erix]: isn't a texty format handier for configs?
[Corion]: So far, I've avoided having even a user database by storing the user information in a (signed) cookie that the browser keeps for me, but as I want to be able to lock users, I need a second storage option :)

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (12)
As of 2018-03-20 14:02 GMT
Find Nodes?
    Voting Booth?
    When I think of a mole I think of:

    Results (253 votes). Check out past polls.