How to avoid special characters in MySQL?

Question

For example:

select * from tablename where fields like "%string "hi" %"; 

Mistake:

You have an error in the SQL syntax; check the manual for your version of MySQL server for the correct syntax to use next to "hi" "" on line 1

How do I build this query?

Answer 1

The information provided in this answer may lead to unsafe programming practices.

The information provided here is highly dependent on the configuration of MySQL, including (but not limited to) the version of the program, the database client, and the character encoding used.

See http://dev.mysql.com/doc/refman/5.0/en/string-literals.html.

 MySQL recognizes the following escape sequences.
 \ 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.

So you need

 select * from tablename where fields like "%string \"hi\" %"; 

Although, as noted by Bill Carvin below , the use of double quotes for line separators is not standard SQL, so single quotes are recommended. This simplifies things:

 select * from tablename where fields like '%string "hi" %'; 

Answer 2

I developed my own MySQL escape code method in Java (if that is useful to everyone).

See class code below.

Warning: invalid if NO_BACKSLASH_ESCAPES SQL is enabled.

 private static final HashMap<String,String> sqlTokens; private static Pattern sqlTokenPattern; static { //MySQL escape sequences: http://dev.mysql.com/doc/refman/5.1/en/string-syntax.html String[][] search_regex_replacement = new String[][] { //search string search regex sql replacement regex { "\u0000" , "\\x00" , "\\\\0" }, { "'" , "'" , "\\\\'" }, { "\"" , "\"" , "\\\\\"" }, { "\b" , "\\x08" , "\\\\b" }, { "\n" , "\\n" , "\\\\n" }, { "\r" , "\\r" , "\\\\r" }, { "\t" , "\\t" , "\\\\t" }, { "\u001A" , "\\x1A" , "\\\\Z" }, { "\\" , "\\\\" , "\\\\\\\\" } }; sqlTokens = new HashMap<String,String>(); String patternStr = ""; for (String[] srr : search_regex_replacement) { sqlTokens.put(srr[0], srr[2]); patternStr += (patternStr.isEmpty() ? "" : "|") + srr[1]; } sqlTokenPattern = Pattern.compile('(' + patternStr + ')'); } public static String escape(String s) { Matcher matcher = sqlTokenPattern.matcher(s); StringBuffer sb = new StringBuffer(); while(matcher.find()) { matcher.appendReplacement(sb, sqlTokens.get(matcher.group(1))); } matcher.appendTail(sb); return sb.toString(); } 

Answer 3

You must use single quotes for line separators. A single quote is a standard SQL string delimiter, and double quotes are identifier delimiters (so you can use special words or characters in table or column names).

In MySQL, double quotes work (non-standard) as the default line separator (unless you set ANSI SQL mode). If you ever use a different brand of SQL database, it will be useful for you to get used to using quotation marks as standard.

Another convenient advantage of using single quotes is that the double quote literal characters inside your string should not be escaped:

 select * from tablename where fields like '%string "hi" %'; 

Answer 4

MySQL has a QUOTE string function, and it should solve this problem:

Answer 5

You can use mysql_real_escape_string . mysql_real_escape_string() does not exit % and _ , so you should avoid MySQL wildcards ( % and _ ) separately.

Answer 6

For such strings, the most convenient way for me to do this is to double the 'or', as explained in the MySQL manual:

There are several ways to include quotation marks in a string:

 A "'" inside a string quoted with "'" may be written as "''". A """ inside a string quoted with """ may be written as """". Precede the quote character by an escape character ("\"). A "'" inside a string quoted with """ needs no special treatment and need not be doubled or escaped. In the same way, """ inside a 

Quoted strings do not need special processing.

This is from http://dev.mysql.com/doc/refman/5.0/en/string-literals.html .

Answer 7

If you use a variable when searching in a string, mysql_real_escape_string() is suitable for you. Just my suggestion:

 $char = "and way 'hihi'"; $myvar = mysql_real_escape_string($char); select * from tablename where fields like "%string $myvar %"; 

Answer 8

To check how to insert double quotes in MySQL using the terminal, you can use the following method:

TableName (Name, DString) -> Schema
insert into tableName values ​​("Name", "My QQDoubleQuotedStringQQ")

After inserting the value, you can update the value in the database with double or single quotes:

 update table TableName replace(Dstring, "QQ", "\"")