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.
-
I want to even escape the other mysql wildcards like '_'.Daemonthread– Daemonthread2011-08-10 11:56:08 +00:00Commented Aug 10, 2011 at 11:56
-
are you talking about regular expressions or the LIKE operator? Because % is NOT a special character in regular expressions. Please edit your question to be more clear. And/or, write the SQL condition you want to use as an example.Tomas– Tomas2011-08-10 12:06:53 +00:00Commented Aug 10, 2011 at 12:06
-
I am storing the regular expressions in the DB. And I am using LIKE operator in SQL to search the regular expression. Sample sql : "Select expression from REGEX where expression LIKE '%';". I want to use % in the sql as normal character.Daemonthread– Daemonthread2011-08-10 12:17:38 +00:00Commented Aug 10, 2011 at 12:17
-
OK, I posted solution for PHP as an answer.Tomas– Tomas2011-08-10 12:37:12 +00:00Commented Aug 10, 2011 at 12:37
Add a comment
|
2 Answers
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 "_".
Comments
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().
