php - bind_param 的作用是什么？

Question

我正在学习如何避免 SQL 注入(inject)，但我有点困惑。

使用bind_param时，我不明白其用途。在手册页上，我找到了这个示例:

$stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");
mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);

$code = 'DEU';
$language = 'Bavarian';
$official = "F";
$percent = 11.2;

现在，假设这 4 个变量是用户输入的，我不明白这如何防止 SQL 注入(inject)。据我了解，他们仍然可以在那里输入任何他们想要的内容。

我也找不到其中 'sssd' 的解释。它有什么作用？这就是它更安全的原因吗？

最后一个问题:我在另一个问题上读到 mysqli_real_escape_string 已弃用，但手册中没有这么说。它是如何被弃用的？由于某种原因它不能再转义特殊字符吗？

注意:这个问题解释了bind_param的作用，但我仍然不明白为什么它更安全或更 protected 。 Bind_param explanation

Answer 1

Now, assuming those 4 variables were user-inputted, I don't understand how this prevents SQL injections. By my understanding, they can still input whatever they want in there.

主要原理是使用预准备语句，该语句旨在向数据库服务器发送安全查询，这可以通过转义不属于实际查询一部分的用户输入来完成，并且还可以在没有任何(where子句)的情况下检查查询在使用任何参数之前检查查询的有效性。

来自这个问题:PDO sends raw query to MySQL while Mysqli sends prepared query, both produce the same result

$stmt = $mysqli->prepare("SELECT * FROM users WHERE username =?")) {
$stmt->bind_param("i", $user);
$user = "''1''";

服务器日志:

  130802 23:39:39   175 Connect   ****@localhost on testdb
    175 Prepare   SELECT * FROM users WHERE username =?
    175 Execute   SELECT * FROM users WHERE username =0
    175 Quit

通过使用准备好的语句，数据库服务器将检查不带任何参数的查询，在这个阶段，可以在绑定(bind)任何参数之前检测到错误，然后，如果查询有效，参数也会发送到服务器以最终确定查询。

摘自 PHP 手册 http://php.net/manual/en/mysqli.quickstart.prepared-statements.php :

Escaping and SQL injection

Bound variables will be escaped automatically by the server. The server inserts their escaped values at the appropriate places into the statement template before execution. A hint must be provided to the server for the type of bound variable, to create an appropriate conversion. See the mysqli_stmt_bind_param() function for more information.

..

I also can't find an explanation for the 'sssd' in there. What does it do? Is that what makes it secure-er?

答案在这里:http://php.net/manual/en/mysqli-stmt.bind-param.php

i
corresponding variable has type integer

d
corresponding variable has type double

s
corresponding variable has type string

b
corresponding variable is a blob and will be sent in packets

Final question: I read on another question that mysqli_real_escape_string is deprecated, but it doesn't say that in the manual. How is it deprecated? Can it not escape special characters anymore for some reason?

能给个引用吗？我认为您误解了 (mysql_real_escape_string())