CODEWITHSUNDEEP
phpmysqlserialization
ppp
ppp

Reputation: 303

how to insert serialized data into database?

here my code-

$things = serialize($_POST['things']);
echo $things;

require 'database.php';

$q = "INSERT INTO tblslider(src) values($things)";
mysql_query($q, $link);

if($result)
{
echo "Slider saved successfully.";
}

Output-
a:4:{i:0;s:10:"651603.jpg";i:1;s:11:"7184512.jpg";i:2;s:11:"3659637.jpg";i:3;s:10:"569839.jpg";}v

it means I am getting the record properly but why it it not getting saved in db??

Upvotes: 1

Views: 31047

Answers (5)

vinczemarton
vinczemarton

Reputation: 8156

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

PHP Data Objects

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

Security

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

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

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

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

So how to do this

At first you need a connection, just 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 can see there are settings, error modes, fetch modes. It worths reading what each setting does but for now just leave it as it is. At the end of it all, you have the $pdo variable you can use.

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

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

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

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

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

Upvotes: 5

sanders
sanders

Reputation: 10898

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

Upvotes: 0

humble_coder
humble_coder

Reputation: 2787

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

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

Then, when you want 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']);

Upvotes: 2

jensgram
jensgram

Reputation: 31508

You forgot quotes around $things:

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

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

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

I prefer to build queries like this to enhance readability:

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

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

EDIT
Comments in this thread suggests that OP actually wants to insert 651603.jpg,7184512.jpg,3659637.jpg,569839.jpg into the database. In that case implode() could be used (provided that $_POST['things'] only contains items 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 here. No serialize(). (I did, however, not realize this erro until just now.)

Upvotes: 14

Simon
Simon

Reputation: 3539

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

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

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

Upvotes: 0

Related Questions