Avoid special SQL characters in regex in Mysql

Question

I have a text box for receiving regular expressions from the user interface. For these regular expressions, I have the ability to search and you want to search. I use prepared statements, and the database is mysql. When I search on "%", I want the search regular expression to start with "%". But, since "%" is a wildcard in mysql, I get all the regex in the search. How to avoid this.

Answer 1

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

\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 MySQL documentation):

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

Answer 2

If you use PHP, you can escape%, _ and characters using this code:

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

\ (backslash) and quotation marks, of course, should also escape (as always! To prevent SQL injection), for example. on mysql_real_escape_string() .