bind_param 有什么作用?

我正在学习如何避免 SQL 注入，但我有点困惑.

使用bind_param时，我不明白目的.在手册页上，我找到了这个例子:

$stmt = mysqli_prepare($link, "INSERT INTO CountryLanguage VALUES (?, ?, ?, ?)");mysqli_stmt_bind_param($stmt, 'sssd', $code, $language, $official, $percent);$code = 'DEU';$language = '巴伐利亚';$official = "F";$% = 11.2;

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

我也找不到对 'sssd' 的解释.它有什么作用?这就是它更安全的原因吗?

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

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

解决方案

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

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

来自这个问题:PDO 向 MySQL 发送原始查询，而 Mysqli 发送准备好的查询，两者产生相同的结果

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

服务器日志:

 130802 23:39:39 175 Connect ****@localhost on testdb175 准备 SELECT * FROM users WHERE username =?175 执行SELECT * FROM users WHERE username =0第175话

通过使用prepared statement，db server会在不带任何参数的情况下检查查询，在这个阶段，可以在绑定任何参数之前检测错误，然后，如果查询有效，参数也会被发送到服务器进行最终确定查询.

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

<块引用>

转义和 SQL 注入

绑定的变量将被服务器自动转义.这服务器在适当的位置插入他们的转义值到执行前的语句模板.必须提供提示给服务器为绑定变量的类型，创建一个合适的转换.有关更多信息，请参阅 mysqli_stmt_bind_param() 函数信息.

...

<块引用>

我也找不到对那里的sssd"的解释.它有什么作用做?这就是它更安全的原因吗?

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

i对应变量的类型为整数d对应变量的类型为 double秒对应变量的类型为字符串乙对应的变量是一个 blob，将在数据包中发送

<块引用>

最后一个问题:我在另一个问题上读到mysqli_real_escape_string 已弃用，但在手册.它是如何被弃用的?不能转义特殊字符吗出于某种原因不再使用?

可以给个参考吗?我想你误解了 (mysql_real_escape_string())

I'm learning about avoiding SQL injections and I'm a bit confused.

When using bind_param, I don't understand the purpose. On the manual page, I found this example:

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

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.

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

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?

Note: This question explained what bind_param does, but I still don't understand why it is any safer or more protected. Bind_param explanation

解决方案

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.

The main principle there is using prepared statement which is designed for sending safe query to db server, this can be done by escaping user input which is not part of the real query, and also checking the query without any (where clause) to check the validity of the query before using any parameters.

From this question: 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''";

server logs:

  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

By Using prepared statement, db server will check the query without any parameter, at this stage, errors can be detected before binding any parameter, then, if the query was valid, parameters also will be send to the server for finalizing the query.

From PHP Manual 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?

The answer is here: 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?

Can you give a reference? I think you misunderstood with (mysql_real_escape_string())