I save data in my DB (mysql) with "serialize($array);". This data came from a form with an input field. I want to know what happen if i insert something like "a:4:{i:1;s:7:"fdsfdsf";i" in the form field. could break my data stored in the DB?
Thanks!!
Add a comment
|
3 Answers
I tested your example on my system, and after serialization, the following value is returned:
string(42) "a:1:{i:0;s:24:"a:4:{i:1;s:7:"fdsfdsf";i";}"
This is what will be added to the database. But, storing user input plain in database is highly discouraged. You should first format the plain user input with mysql_real_escape_string() as it will escape critical characters.
Apart from that, if unserialize() is called on the serialized text read back from database, the array is properly returned. It should be safe, but can produce unexpected results.
Be extremely careful with storing serialized arrays in a database. Serialization returns a string, so the field you store the data in is usually VARCHAR or TEXT. If you simply overwrite the stored array with a new one, the old data will be completely lost. To update the database, make sure you first read the data from the database into an array, and update it, and only then write it back to the database.
While it is not forbidden, using and storing stuff serialized in database usually creates a lot of issues. The database has a lot of datatypes known by default, and big serialized arrays create overhead and complicates execution, and is just simply a pain in the ass if the system later needs to be modified. And you cannot use relation queries on serialized fields.
3 Comments
Dag Sondre Hansen
As php serialized data returns binary data, do not use VARCHAR or TEXT but rather VARBINARY or BLOB.
Jekis
PHP serialize() returns a string containing a byte-stream representation of value. Note that this is a binary string which may include null bytes, and needs to be stored and handled as such. For example, serialize() output should generally be stored in a BLOB field in a database, rather than a CHAR or TEXT field. php.net/manual/en/function.serialize.php
gumuruh
what about the length of VARBINARY or the BLOB it self if i stored a very big serialized data?
The old way
When you're still using mysql_ you could write queries like this:
$sql = sprintf("INSERT INTO mytable (a) VALUES ('%s')",
mysql_real_escape_string(serialize($myvar))
);
mysql_query($sql) or die("oh no!");
The recommended way
For PDO and mysqli you get the option to use prepared statements, which comes highly recommended for exactly the purpose of preventing SQL injection attack vectors. An example in PDO:
$stmt = $db->prepare('INSERT INTO mytable (a) VALUES (:myvar)');
$stmt->execute(array(
':myvar' => serialize($myvar),
));
Field lengths
Also, make sure the length of your serialized data doesn't exceed the column size of the table field; a truncated serialized variable is pretty much useless.
3 Comments
specializt
um .... how exactly do prepared statements prevent injections, again? Exactly - they dont. At all.
Ja͢ck
@specializt Prepared statements isn't magical in that respect, people still can (and do) screw it up, evidenced by the somewhat recent Drupal advisory. However, if you feel that the statement in my answer can be exploited, do let me know how this could be achieved.
gumuruh
when i made a serialized data submitted, sometimes the length is unpredictable. What should i do then? Because it's made dynamically. Wouldn't be okay if i just use TEXT data type because of the un-predictable serialized data length ?
A way to block this is escaping quotes before inserting data into the database.
You could do this with mysqli_real_escape_string() http://www.php.net/manual/en/mysqli.real-escape-string.php
