Re: DBI Placeholders and REGEXP

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.

Comment on Re: DBI Placeholders and REGEXP Select or Download Code

Replies are listed 'Best First'.
Re: Re: DBI Placeholders and REGEXP by Masem (Monsignor) on Feb 15, 2001 at 09:39 UTC
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]

Replies are listed 'Best First'.

Re: Re: DBI Placeholders and REGEXP
by Masem (Monsignor) on Feb 15, 2001 at 09:39 UTC

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]