PHP MySQLI防止SQL注入 [英] PHP MySQLI Prevent SQL Injection

问题描述

我已经建立了一个即将上线的网站，并且只对防止SQL注入有几个问题，我了解如何使用mysqli_real_escape_string，但是我只是想知道是否必须在所有变量上使用它我正在获取我的SQL语句，并且在执行选择语句时还是在插入更新和删除时是否必须使用它?另外，在我将网站投入使用之前，您还建议我实施其他什么安全措施，谢谢您的帮助！

I've build a website that will be going live soon and just have a couple questions about preventing SQL injection, I understand how to use mysqli_real_escape_string but I'm just wondering if I have to use that on all variables that I'm getting for my SQL statement and do I have to use it when I'm doing select statements also or just on insert update and delete? Also what other security would you recommend me implementing before I put the site live, thanks in advance for any help!

推荐答案

任何查询都可以被注入，无论是读取还是写入，持久性还是瞬时性.可以通过结束一个查询并运行一个单独的查询(可能使用mysqli)来执行注入操作，这会使预期的查询不相关.

Any query can be injected whether it's read or write, persistent or transient. Injections can be performed by ending one query and running a separate one (possible with mysqli), which renders the intended query irrelevant.

任何来自外部源的查询输入，无论是来自用户还是内部的输入，都应视为查询的参数以及查询上下文中的参数.查询中的任何参数都需要参数化.这将导致参数化的查询正确，您可以从中创建准备好的语句并使用参数执行.例如:

Any input to a query from an external source whether it is from users or even internal should be considered an argument to the query, and a parameter in the context of the query. Any parameter in a query needs to be parameterized. This leads to a properly parameterized query that you can create a prepared statement from and execute with arguments. For example:

SELECT col1 FROM t1 WHERE col2 = ?

?是参数的占位符.使用mysqli，可以使用prepare创建准备好的语句，使用bind_param将变量(参数)绑定到参数，然后使用execute运行查询.您根本不需要清理参数(实际上这样做是有害的). mysqli为您做到这一点.整个过程将是:

? is a placeholder for a parameter. Using mysqli, you can create a prepared statement using prepare, bind a variable (argument) to a parameter using bind_param, and run the query with execute. You don't have to sanitize the argument at all (in fact it's detrimental to do so). mysqli does that for you. The full process would be:

$stmt = $mysqli->prepare("SELECT col1 FROM t1 WHERE col2 = ?");
$stmt->bind_param("s", $col2_arg);
$stmt->execute();

参数化查询和准备好的语句之间也有重要区别.该语句在准备时并未进行参数设置，因此容易被注入:

There is also an important distinction between parameterized query and prepared statement. This statement, while prepared, is not parameterized and is thus vulnerable to injection:

$stmt = $mysqli->prepare("INSERT INTO t1 VALUES ($_POST[user_input])");

总结:

• 所有 查询应正确设置参数(除非它们没有参数)
查询的
• 所有 参数，无论其来源如何，都应视为敌对行为

这篇关于PHP MySQLI防止SQL注入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！