在PHP中保护SQL查询的最佳方法 [英] Best Way to Secure SQL Query in PHP

问题描述

如果我正在使用PHP在MySQL数据库上运行查询，如下所示:

If I am running a query on a MySQL database using PHP as in the following:

$query="SELECT * FROM tablename";

从SQL注入之类的东西中保护这一点的最佳方法是什么?我听说过一些转义方法，但是在查询中不会留下斜线吗?

What is the best way to secure this from things like SQL Injections? I've heard about some escape methods, but won't it leave slashes in the query?

推荐答案

您在问题中显示的查询未使用用户提供的值，因此在一般情况下都不会使用SQL注入:-

The query you have shown in the question doesn't use user supplied values so there is no case of SQL Injection but in a general case:-

首先，您必须先验证所有用户输入(用户名，电子邮件等)，然后才能在查询中使用它.例如:-如果用户名中仅允许使用字母数字字符，则在继续进行数据库查询之前，必须检查输入是否实际上是字母数字字符，并且还必须检查所有输入的大小.

Firstly, you must validate all the user input(usernames,emails etc.) before using it in a query. For ex:- If you have allowed only alphanumeric characters in a username, then you must check whether the input is actually alphanumeric or not before proceeding to form a database query and you must also check the size of all the inputs.

在那之后，我认为"Prepared Statements"是防止SQL注入的最佳选择.

After that, in my opinion Prepared Statements is the best choice for preventing SQL injection.

mysql_real_escape_string()的问题:-

由于mysql_real_escape_string()根据默认字符集对字符进行转义，因此它比addslashes()函数更好，并且可以正确清理

As mysql_real_escape_string() escapes characters according to default charset, so it is better than addslashes() function and it properly sanitizes SQL injections arising out of abuse of multibyte character sets, but in another article here, a workaround-scenario is shown that explains that injection can still be done.

解决方案:-

因此，防止SQL注入的正确而更好的方法是使用准备好的语句.它是一种在插入用户输入(参数)之前对SQL语句进行预编译并将其视为可重用的SQL模板的技术.因此，它将用户输入与实际的SQL代码分开，并且SQL解析器从不解析用户输入.

So the proper and better way of preventing SQL injection is to use prepared statements. It is a technique in which SQL statements are precompiled before the insertion of the user-input (parameters) and are treated as reusable SQL templates. So, it separates the user input from actual SQL-Code and the SQL-parser never parses the user input.

除安全性外，它还优化了SQL查询的速度.如果您需要使用不同的用户输入多次运行相同的查询，这将很有帮助.

Apart from security, it also optimizes the SQL query for speed. It helps in cases where you need to run same query multiple times with different user inputs.

有关实现的详细信息，请参阅PHP手册.

You can refer to PHP manual for implementation details.

这篇关于在PHP中保护SQL查询的最佳方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！