How to escape special SQL characters in regular expression in Mysql

Question

I have a text field to accept regular expressions from the UI. For these regular expressions, I have a search capability and want to do a search. I am using prepared statements and the DB is mysql. When I do a search on '%', I only want search regex starting with '%'. But, since '%' is wildcard in mysql, I get all the regex in the search. How to escape it.

Answer 1

Just use a backslash before the character, as shown in the MySQL documentation section 9.1:

\0  An ASCII NUL (0x00) character.  
\'  A single quote ("'") character.  
\"  A double quote (""") character.  
\b  A backspace character.  
\n  A newline (linefeed) character.  
\r  A carriage return character.  
\t  A tab character.  
\Z  ASCII 26 (Control+Z). See note following the table.  
\\  A backslash ("\") character.  
\%  A "%" character. See note following the table.  
\_  A "_" character. See note following the table.  

Note (from the MySQL documentation):

If you use "\%" or "\_" outside of pattern-matching contexts, they evaluate to the strings "\%" and "\_", not to "%" and "_".

Answer 2

If you are using PHP, you may escape %, _ and characters using this code:

$escaped = addcslashes($str, "%_");

The \ (backslash) and quotes you of course must also escape (as always! To prevent SQL injection), e.g. by mysql_real_escape_string().