使用准备好的语句比普通的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屋！