[ACCEPTED]-how to insert serialized data into database?-serialization

Accepted answer
Score: 13

You forgot quotes around $things:

$q = "INSERT INTO tblslider(src) values('" . mysql_real_escape_string($things) . "')";

The mysql_real_escape_string() is really 13 the least you should ever do!

Also as @sanders mentions, you 12 should always output your complete query 11 (via print_r() or var_dump()) as a first step in debugging.


I 10 prefer to build queries like this to enhance 9 readability:

$q = sprintf(
         'INSERT INTO tblslider(src) VALUES ("%s")',
         mysql_real_escape_string($things)
     );

That is, whenever I absolutely 8 have to build and escape them myself. You should 7 really have a look at PDO.


EDIT
Comments in this 6 thread suggests that OP actually wants to 5 insert 651603.jpg,7184512.jpg,3659637.jpg,569839.jpg into the database. In that case 4 implode() could be used (provided that $_POST['things'] only contains items 3 to insert!):

$q = sprintf(
         'INSERT INTO tblslider(src) VALUES ("%s")',
         mysql_real_escape_string(implode(',', $_POST['things']))
     );

Note, that I'm using $_POST['things'] directly 2 here. No serialize(). (I did, however, not realize 1 this erro until just now.)

Score: 5

This question is quite old, but I feel like 38 it's time for a little necromancy. The accepted 37 answer by @jensgram is not wrong, but saying 36 mysql_real_escape_string is the least you could do implies there is a much 35 better solution. Well there is.

PHP Data Objects

PDOs. These bad 34 boys provide an abstraction layer for your 33 database access, so it works with a lot 32 of other databases not just MySQL, and can 31 improve performance when the same query 30 is run many times. Nut this is not why you 29 need them.

Security

Escaping stuff is hard. Either 28 it is obscure how to do it in a specific 27 context or you just forget to do it. The 26 worst thing is that you will not get any 25 errors from forgetting it, and just move 24 on like nothing happened. And you just contributed 23 to the sorry state internet security is 22 in.

With using PDOs properly (e.g. no string 21 concatenation) however will ensure that 20 you will not mess up properly escaping stuff 19 while building DB queries.

You will want 18 to read this: (The only proper) PDO tutorial.

Basically you can prepare 17 an sql statement BEFORE replacing ANY parameters 16 in it. The SQL syntax will be fixed and 15 cannot be broken by bad/no escaping or maliciously 14 forged requests.

So how to do this

At first you need a connection, just 13 like in the regular mysql driver.

$host = '127.0.0.1';
$db   = 'test';
$user = 'root';
$pass = '';
$charset = 'utf8';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$opt = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new PDO($dsn, $user, $pass, $opt);

As you 12 can see there are settings, error modes, fetch 11 modes. It worths reading what each setting 10 does but for now just leave it as it is. At 9 the end of it all, you have the $pdo variable 8 you can use.

$query = "INSERT INTO tblslider(src) VALUES (?)";
$stmt = $pdo->prepare($query); 

At this point we got our statement. Nothing 7 came from external sources yet, it is an 6 INSERT statement.

$things = serialize($_POST['things'])
$stmt->execute([ $things ]); //execute

There are other ways to 5 bind the ?-s to parameters, also you can 4 use named parameters too for clarity, but 3 this suffices for now.

That's it: no fancy 2 escaping but no possibility for SQL injection 1 either.

Score: 2

Normally, I will serialize then base64_encode 3 to eliminate surprises and "standardize" the 2 input. For example:

$things = base64_encode(serialize($_POST['things']));
$insert_query = "INSERT INTO...";

Then, when you want 1 to grab it, simply reverse the process.

$query_result = mysql_query("SELECT FROM ...");
$row = mysql_fetch_assoc($query_result);
$retrieved_value = unserialize(base64_decode($row['src']);
Score: 0

place an var_dump($q) before mysql_query(...)
You can then examine 1 your query.

Score: 0

You are writing a string to the database, so 3 don't forget to add quotes to your query:

$q = "INSERT INTO tblslider(src) values('$things')";

Also 2 make sure to filter the string to avoid 1 SQL-Injection Attacks.

More Related questions