php 如何将序列化数据插入数据库？

how to insert serialized data into database?

提问by ppp

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

输出-
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??

这意味着我正在正确获取记录，但为什么它没有保存在 db 中？

回答by jensgram

You forgot quotes around $things:

你忘记了周围的引号$things：

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

The mysql_real_escape_string()is really the leastyou should everdo!

该mysql_real_escape_string()是真正的至少你应该永远做！

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

同样正如@sanders 所提到的，您应该始终输出完整的查询（通过print_r()或var_dump()）作为调试的第一步。

---

---

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 haveto build and escape them myself. You should really have a look at PDO.

也就是说，每当我绝对必须自己构建和逃脱它们时。你真的应该看看PDO。

---

---

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

编辑
此线程中的注释表明 OP 实际上想要插入651603.jpg,7184512.jpg,3659637.jpg,569839.jpg到数据库中。在这种情况下implode()可以使用（前提是$_POST['things']只包含要插入的项目！）：

$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.)

请注意，我在$_POST['things']这里直接使用。没有serialize()。（然而，我直到现在才意识到这个错误。）

回答by SoonDead

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_stringis the leastyou could do implies there is a much better solution. Well there is.

这个问题已经很老了，但我觉得是时候来点死灵法了。通过@jensgram接受的答案是没有错，但说mysql_real_escape_string是最少的，你可以做意味着有一个更好的解决方案。那么有。

PHP Data Objects

PHP 数据对象

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.

PDO。这些坏家伙为您的数据库访问提供了一个抽象层，因此它可以与许多其他数据库一起使用，而不仅仅是 MySQL，并且可以在多次运行相同查询时提高性能。坚果这不是你需要它们的原因。

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.

正确使用 PDO（例如没有字符串连接）将确保您不会在构建数据库查询时正确地转义内容。

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

您将需要阅读以下内容：（唯一正确的）PDO 教程。

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.

基本上，您可以在替换其中的任何参数之前准备一个 sql 语句。SQL 语法将被修复，不会被错误/无转义或恶意伪造请求破坏。

So how to do this

那么如何做到这一点

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

首先你需要一个连接，就像在常规的 mysql 驱动程序中一样。

$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.

如您所见，有设置、错误模式、获取模式。值得阅读每个设置的作用，但现在就让它保持原样。最后，您可以使用 $pdo 变量。

$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.

此时我们得到了我们的声明。还没有来自外部来源，它是一个 INSERT 语句。

$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.

还有其他方法可以将 ?-s 绑定到参数，为了清楚起见，您也可以使用命名参数，但这现在就足够了。

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

就是这样：没有花哨的转义，但也没有 SQL 注入的可能性。

回答by humble_coder

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

通常，我将序列化然后 base64_encode 以消除意外并“标准化”输入。例如：

$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']);

回答by sanders

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

放置一个var_dump($q)before 然后mysql_query(...)
您可以检查您的查询。

回答by Simon

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.

还要确保过滤字符串以避免 SQL 注入攻击。