I have the following data as an example:
$a = addslashes('hello\'s');
$b = serialize($a);
// As you know, $b looks like this s:8:"hello\'s";
Now when I insert $b to MySQL, the data now looks like this s:8:"hello's" inside MySQL. MySQL removes the \ and now I have an invalid serialized data.
What's the best way to fix this? Thanks
For escaping parameters to go into an SQL query you do not use addslashes, but mysql_real_escape_string.
Example:
<?php
$param = mysql_real_escape_string($_GET['param']);
$query = "SELECT f1, f2 FROM atable WHERE f3 = '$param' ";
// these single quotes here are essential !! ^ ^
// if you leave out the quotes you **will** suffer SQL-injection.
This is the correct way to escape SQL-parameters.
Or even better use PDO with prepared statements, then you don't have to escape at all.
use PDO, then you don't have to escape at all - a misleading statement, unless you replace PDO with prepared statements.
First serialize the value you want and then use the mysql_real_escape_string. That's the string you are going to put in the database after all. Try to avoid addslashes...
If you don't want to have an active connection at the time, try this function:
function mysql_escape_no_conn( $input ) {
if( is_array( $input ) ) {
return array_map( __METHOD__, $input );
}
if( !empty( $input ) && is_string( $input ) ) {
return str_replace( array( '\\', "\0", "\n", "\r", "'", '"', "\x1a" ),
array( '\\\\', '\\0', '\\n', '\\r', "\\'", '\\"', '\\Z' ),
$input );
}
return $input;
}
use mysql_real_escape_string function!
As others have said, mysql_real_escape_string() should be called!
I just wanted to add that I always find it safe to base64_encode() all serialized arrays/objects I store in a database. All you then have to do is call base64_decode() upon retrieval of the stored value. I do this because ; and other characters are liable to cause "Warning: Error at offset..." errors.
You need to escape the string in $b by using mysql_real_escape_string().
$a = addslashes('hello\'s');
$b = serialize($a);
$sql = "UPDATE `table` SET `field`='".mysql_real_escape_string($b)."'" ;
You should always escape input into a db to stop SQL injection.
