与普通 mysqli 语句相比，使用准备好的语句的优势是什么? [英] Advantages of using prepared statements over normal mysqli statements?

问题描述

我已经完成了我的研究并决定在我的查询中使用准备好的语句，我只问是否有什么我应该知道的，关于切换到普通 mysqli 查询到准备好的语句的好坏.

I have done my research and have decided to use prepared statements in my queries, all I ask if there is anything I should know, good or bad about switching to normal mysqli queries to prepared statements.

我也不明白为什么不需要转义坏字符的逻辑?

Also I don't understand the logic how the need for escaping bad characters is not needed?

推荐答案

仍然需要转义坏字符，但库会自动为您绑定的所有参数执行此操作.它只是稍微方便一些，并且可以防止程序员忘记清理值.

Escaping bad characters is still needed, but the library does it automatically for all parameters you bind. It's just slightly more convenient, and prevents the programmer from forgetting to sanitize a value.

但是，请注意，这种自动化仅限于参数！

However, note that this automatism is limited to parameters!

以下查询是安全的，因为 bind_param() 负责转义:

The following query is safe, because bind_param() takes care of escaping:

$code = $_GET["code"];
$name= $_GET["name"];
$percentage= $_GET["percentage"];

$stmt = $mysqli->prepare("INSERT INTO items VALUES (?, ?, ?)");
$stmt->bind_param('iss', code, $name, $percentage);
$stmt->execute();

以下查询是不安全的，因为您直接放入查询中的任何内容都不会自动转义:

$tablename = $_GET["prefix"]."_items";  
$code = $_GET["code"];
$name= $_GET["name"];
$percentage= $_GET["percentage"];

                                    ---- UNSAFE! ----
$stmt = $mysqli->prepare("INSERT INTO `$tablename` VALUES (?, ?, ?)");
$stmt->bind_param('iss', $code, $name, $percentage);
$stmt->execute();

也就是说，无论如何都不应该使用本示例中所示的动态表名.但重点是:小心，即使是参数化查询！

that said, one shouldn't be using dynamic table names like shown in this example anyway. But the point stands: Be careful, even with parametrized queries!

我能想到的唯一缺点是您无法再看到用于调试的最终查询(因为它仅在服务器端组装).

The only downside I can think of is that you can't see the final query any more for debugging (because it gets assembled only on server side).

这篇关于与普通 mysqli 语句相比，使用准备好的语句的优势是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！