MySQL – escaping text

admin

November 1, 2011

This blog posting is about the importance of escape the escape character in MySQL. The escape character in MySQL is the backslash (\) character.

If you are attempting to add the following text into a database “\frac\{9\}\{5\}”, the insert command will strip the backslashes out before inserting into the table. We will demonstrate this in this blog post.

Consider the following table

CREATE TABLE testtext(
  id INT AUTO_INCREMENT ,
  data TEXT,
  PRIMARY KEY ( id )
) ENGINE = InnoDB;

And then we insert into this table the following text “\frac\{9\}\{5\}”.

mysql> INSERT INTO testtext (data) VALUES ('\frac\{9\}\{5\}');
Query OK, 1 row affected (0.00 sec)

mysql> select * from testtext;
+----+------------+
| id | data       |
+----+------------+
|  1 | frac{9}{5} | 
+----+------------+
1 row in set (0.00 sec)

We can see that the backslashes were stripped out during the insert. In order to insert data to maintain the backslashes we need to escape character these values too. So \ will need to become \\. As follows:

mysql> INSERT INTO testtext (data) VALUES ('\\frac\\{9\\}\\{5\\}');
Query OK, 1 row affected (0.02 sec)

mysql> select * from testtext;
+----+-----------------+
| id | data            |
+----+-----------------+
|  1 | frac{9}{5}      | 
|  2 | \frac\{9\}\{5\} | 
+----+-----------------+
2 rows in set (0.01 sec)

MySQL