How do I escape special characters in MySQL?

by

The information provided in this answer can lead to insecure programming practices.

The information provided here depends highly on MySQL configuration, including (but not limited to) the program version, the database client and 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 Bill Karwin notes below, using double quotes for string delimiters isn’t standard SQL, so it’s good practice to use single quotes. This simplifies things:

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

More Related Contents:

  1. Insert into a MySQL table or update if exists
  2. mysql error 1364 Field doesn’t have a default values
  3. SQL: What is the default Order By of queries?
  4. How to group by week in MySQL?
  5. Can I use a function for a default value in MySql?
  6. Access Denied for User ‘root’@’localhost’ (using password: YES) – No Privileges?
  7. Mysql order by specific ID values
  8. How do I check if a column is empty or null in MySQL?
  9. MySQL versus PDO [duplicate]
  10. Mysql string split [duplicate]
  11. Increment a database field by 1
  12. Measuring actual MySQL query time
  13. Am I Using JDBC Connection Pooling?
  14. MySQL VARCHAR Lengths and UTF-8
  15. Row number per group in mysql
  16. Find and replace entire MySQL database
  17. Resetting ROOT password in MySQL 5.6
  18. MySQL #1093 – You can’t specify target table ‘giveaways’ for update in FROM clause
  19. Field ‘id’ doesn’t have a default value?
  20. is there a mysql function to decode html entities?
  21. MySQL fails on: mysql “ERROR 1524 (HY000): Plugin ‘auth_socket’ is not loaded”
  22. MySQL – SQL_BIG_SELECTS
  23. How to Prevent Connection Timeouts for Large MySQL Imports
  24. Can I launch a trigger on select statement in mysql?
  25. How to use MySQL DECIMAL?
  26. MySQL: return updated rows
  27. SQL doesnt differentiate u and ü although collation is utf8mb4_unicode_ci
  28. Laravel Eloquent Ignore Casing
  29. How to get back Lost phpMyAdmin Password, XAMPP
  30. ERROR 1115 (42000): Unknown character set: ‘utf8mb4’

Leave a Comment