How to store data which contains quotes in MySQL

Question

In one of my forms I use the rich text editor from Yahoo!. Now i want to store the data from that textarea in a MySQL database.

The user can enter anything in that textarea, e.g. many double or single quotes.

How can I store that data?

Normally we store by adding that data in one variable and then put that in sql, but the quotes cause problems.

Answer 1

You can use mysql_real_escape_string().

Escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

mysql_real_escape_string() calls MySQL's library function mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

This function must always (with few exceptions) be used to make data safe before sending a query to MySQL.

e.g.

$value = mysql_real_escape_string(" ' \" etc ");
$sql = "INSERT INTO blah VALUES ('$value')";

But a better solution is to use PDO and prepared statements.

Answer 2

You use a PDO prepared statement (or mysql_real_escape_string)

Answer 3

If PDO isnt an option you might be able to use mysqli instead of course with a prepared statement.

Answer 4

Better yet! When submitting the content to the database, use addslashes();

When retrieving and displaying the string use stripslashes();

$string = "That's awesome!";

addslashes($string); will come out as That\'s Awesome in the database (and won't break anything)

Then stripslashes($string); will return it to normal.

http://php.net/manual/en/function.addslashes.php

I use this all the time - simple and straight-forward.