MySQL Regex

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

I've got a MySQL Regex problem, but only when I pass it from cgi script. This code from within phpMyAdmin

SELECT id,title FROM table WHERE title REGEXP '^\\['
[download]

returns anything where the title starts with a bracket.

When this from my CGI script

my $sti = $dbh->prepare("SELECT id,title FROM table WHERE title REGEXP
+ '^\\[');
[download]

Gives me
"DBD::mysql::st execute failed: Got error 'brackets ( ) not balanced' from regexp at"
And a big ole effing headache. Any reason why the escape characters aren't being passed properly?

Comment on MySQL Regex Select or Download Code

Replies are listed 'Best First'.
Re: MySQL Regex by yosefm (Friar) on Apr 13, 2004 at 09:09 UTC
You can avoid escaping problems by using placeholders: `my $sth = $dbh->prepare("... where title regexp ?"); $sth->execute('^\[');` [download] The '?' is replaced by the value passed to 'execute'. `perl -e'$b=unpack"b",pack"H","59dfce2d6b1664d3b26cd9969503";\ for(;$a<length$b;$a+=9){print+pack"b8",substr$b,$a,8;}'` [download] My public key	[reply] [d/l] [select]
Re: MySQL Regex by bart (Canon) on Apr 13, 2004 at 09:59 UTC
Double your backslashes. Perl reduces the two backslashes you typed, to just one, as you can see if you'd print out the string. It'll reduce 4 to 2, and that's what you want.	[reply]
Re: MySQL Regex by pelagic (Priest) on Apr 13, 2004 at 09:01 UTC
Did you just miss the closing " ? pelagic	[reply]