MySQL REGEXP Validation / Error Handling

Link: http://dev.mysql.com/doc/refman/5.0/en/sql-syntax-prepared-statements.html

Allowing users to run regular expression (REGEXP) searches through a web interface provides excellent search capabilities with very little engineering. One need only change WHERE `field`=’value’ to WHERE `field` REGEXP ‘regex’.

A problem arises if the regex entered is invalid. MySQL will report an error.

One solution that works well is to wrap the mysql_query string in a function or class method. Then, when testing for an error, check (use stristr) to see if the error was a regex error. Regex errors should be handled as user errors, not application errors. They should be logged to allow later analysis (if there are alot of regex errors, help should be provided).

Another approach is to use the PREPARE STATEMENT (see link above). This will throw an error if there are any invalid regexes.