Save to MySQL serialized info with quotes

Question

Trying to save serialized string to SQL, but then i am having problems with unserializing it because of quotes.

Example, string is "te'st", after serialize we have

s:5:"te'st";

But to save it to SQL we need to add slashes, and i am doing

serialize(addslashes($string))

after this, in our MySQL db we have

s:6:"te'st";

And this is the problem. s:6 means we have 6 symbols string, but our "te'st" is only 5, so when we trying to unserialize it, we getting error.

How to solve it? Tried htmlspecialchars and mysql_real_escape_string

Update:

How i use mysql_real_escape_string

 mysql_query("INSERT INTO `table`(`string`) VALUES ('" . serialize(array('iId' =>$aSqlResult['typeID'], 'sName' => mysql_real_escape_string($sScanResultLine))) . "')");

Answer 1

You should pass the data through the escape function after the serialization, not before - which is what you are doing now.

$serialized = mysql_real_escape_string(serialize($data));

Use a parameterised query with PDO or MySQLi and you can forget about the escaping altogether.

Answer 2

You're making a mistake I've seen many making. A bit of a fundamental misunderstanding of how escaping functions and should be used.

You cannot simply chain escape functions and end up with something that's perfect for any context. Your mistake is simple..

You're doing two things:

1. Serializing an object ( a string in this case )
2. Saving that into the database.

So before you save it to the database, you must make sure that your value is properly escaped. DO THIS WITH MYSQLI! The mysql_ functions are dead.

The equivalent is mysqli::real_escape_string.

But most importantly.. (sorry for dragging this on)..

serialize modifies the output, it can return a whole bunch of things.. quotes, 0x00's and this is not allowed in mysql queries.

So real_escape_string must obviously be the last step! First serialize, and the escape the output of that function. You did the exact opposite.

Answer 3

In your case the mysql_real_escape_string() is the way to go. It have to work, unless you did it somehow wrong (note: you need to be connected to DB before calling that function). And in fact you should use mysqli_ or PDO, not a mysql_ extension which is now deprecated. Using htmlspecialchars() is simply using wrong tool for the task.

Code should be like this:

mysql_real_escape_string( serialize( $string ) );