4 messages in com.mysql.lists.perlRe: escaping strings properly for use...
FromSent OnAttachments
Mark Stosberg02 May 2003 06:27 
Garry Williams02 May 2003 16:21 
Mark Stosberg03 May 2003 13:00 
Garry Williams03 May 2003 14:36 
Subject:Re: escaping strings properly for use with REGEXP
From:Mark Stosberg (ma@summersault.com)
Date:05/03/2003 01:00:18 PM
List:com.mysql.lists.perl

In article <2003@zvolve.com>, Garry Williams wrote:

On Fri, May 02, 2003 at 13:27:28 +0000, Mark Stosberg wrote:

I'm writing a web application using DBD::mysql. I would like for the user to enter some keyword search terms that become part of a REGEXP clause, like:

field REGEXP 'term1|term2|term3',

However, I don't want anything the user enters to have special meaning in my regular expression. For example, you can see with this test query, MySQL throws an error:

# mysql> select 'big blue truck!' regexp 'Big|Blu~e|[[.charactersfu)ck{'; # ERROR 1139: Got error 'brackets ([ ]) not balanced' from regexp

Your requirement seems to be ambiguous.

Thanks for the response, and sorry for being ambiguous. My users are just regular web users who won't have any ability to create regular expressions. Any use of a meta character should be treated as unintentional.

To clarify, I'm thinking that a user might enter the following in a search box:

"my three keywords"

I was then thinking about implementing the SQL search logic of

'If field matches "my" or "three" or "keywords", return the record'.

Maybe all I need is simple function to replace all meta characters with "\meta". This seems like a common use that someone might already have a standard routine for.

Thanks,

Mark