防止sql注入 php_PHP-防止SQL注入

防止sql注入 php

SQL injection vulnerabilities have been described as one of the most serious threats for Web applications. Web applications that are vulnerable to SQL injection may allow an attacker to gain complete access to their underlying databases.

SQL注入漏洞已被描述为Web应用程序最严重的威胁之一。 容易受到SQL注入攻击的Web应用程序可能允许攻击者完全访问其基础数据库。

Because these databases often contain sensitive consumer or user information, the resulting security violations can include identity theft, loss of confidential information, and fraud. In some cases, attackers can even use an SQL injection vulnerability to take control of and corrupt the system that hosts the Web application.

由于这些数据库通常包含敏感的消费者或用户信息，因此导致的安全违规行为可能包括身份盗用，机密信息丢失和欺诈。 在某些情况下，攻击者甚至可以使用SQL注入漏洞来控制和破坏托管Web应用程序的系统。

SQL injection refers to a class of code-injection attacks in which data provided by the user is included in an SQL query in such a way that part of the user's input is treated as SQL code.By lever-aging these vulnerabilities, an attacker can submit SQL commands directly to the database. These attacks are a serious threat to any Web application that receives input from users and incorporates it into SQL queries to an underlying database.

SQL注入是一类代码注入攻击，其中用户提供的数据包含在SQL查询中，从而将用户输入的部分内容视为SQL代码。通过利用这些漏洞，攻击者可以直接将SQL命令提交到数据库。 这些攻击对于从用户接收输入并将其合并到基础数据库SQL查询中的任何Web应用程序都是严重的威胁。

The cause of SQL injection vulnerabilities is relatively simple and well understood: insufficient validation of user input.

SQL注入漏洞的原因相对简单并且容易理解：用户输入验证不足。

______________________________

__________________________ ____

Types of SQL injection attacks:

SQL注入攻击的类型：

______________________________

__________________________ ____

Union Query:

联合查询：

In union-query attacks, an attacker exploits a vulnerable parameter to change the data set returned for a given query. With this technique, an attacker can trick the application into returning data from a table different from the one that was intended by the developer.

在联合查询攻击中，攻击者利用易受攻击的参数来更改为给定查询返回的数据集。 借助这种技术，攻击者可以诱使应用程序从与开发人员意图不同的表中返回数据。

Attackers do this by injecting a statement of the form: UNION SELECT <rest of injected query>.

攻击者通过注入以下形式的语句来做到这一点：UNION SELECT <剩余的注入查询>。

Because the attackers completely control the second/injected query, they can use that query to retrieve information from a specified table. The result of this attack is that the database returns a dataset that is the union of the results of the original first query and the results of the injected second query.

由于攻击者完全控制了第二个查询/注入的查询，因此他们可以使用该查询从指定的表中检索信息。 攻击的结果是数据库返回一个数据集，该数据集是原始第一个查询的结果和注入的第二个查询的结果的并集。

SELECT accounts FROM users WHERE login='' UNION
SELECT cardNo from CreditCards where
acctNo=10032 -- AND pass='' AND pin=

In many applications, the effect of this operation is that the value for cardNo is displayed along with the account information.

在许多应用程序中，此操作的效果是显示cardNo的值以及帐户信息。

Piggy-Backed Queries:

存根查询：

In this attack type, an attacker tries to inject additional queries into the original query. We distinguish this type from others because, in this case, attackers are not trying to modify the original intended query; instead, they are trying to include new and distinct queries that piggy-back on the original query. As a result, the database receives multiple SQL queries.

在这种攻击类型中，攻击者尝试将其他查询插入原始查询中。 我们将这种类型与其他类型区分开来，因为在这种情况下，攻击者没有试图修改原始的预期查询； 相反，他们正在尝试包含新的且与众不同的查询，这些查询背负于原始查询。 结果，数据库接收到多个SQL查询。

The first is the intended query which is executed as normal; the subsequent ones are the injected queries, which are executed in addition to the first. This type of attack can be extremely harmful. If successful, attackers can insert virtually any type of SQL command, including stored procedures,1 into the additional queries and have them executed along with the original query.

第一个是正常执行的预期查询；第二个是正常执行的查询。 后续查询是注入查询，除了第一个查询之外，还将执行这些查询。 这种类型的攻击可能非常有害。 如果成功，攻击者几乎可以将任何类型SQL命令（包括存储过程）1插入附加查询中，并使它们与原始查询一起执行。

SELECT accounts FROM users WHERE login='doe' AND
pass="; drop table users -- ' AND pin=123

Magic String:

魔术弦：

The magic string is a simple string of SQL used primarily at login pages. The magic string is

魔术字符串是主要用于登录页面的简单SQL字符串。 魔术弦是

'OR''='

Alternate Encodings:

备用编码：

In this attack, the injected text is modified so as to avoid detection by defensive coding practices and also many automated prevention techniques. This attack type is used in conjunction with other attacks. In other words, alternate encodings do not provide any unique way to attack an application; they are simply an enabling technique that allows attackers to evade detection and prevention techniques and exploit vulnerabilities that might not otherwise be exploitable. These evasion techniques are often necessary because a common defensive coding practice is to scan for certain known bad characters, such as single quotes and comment operators.

在这种攻击中，修改了注入的文本，以避免通过防御性编码实践以及许多自动预防技术进行检测。 此攻击类型与其他攻击结合使用。 换句话说，备用编码不会提供攻击应用程序的任何独特方法。 它们只是使攻击者逃避检测和预防技术并利用可能无法利用的漏洞的简单使能技术。 这些规避技术通常是必需的，因为常见的防御性编码做法是扫描某些已知的不良字符，例如单引号和注释运算符。

SELECT accounts FROM users WHERE login='legalUser';
exec(char(0x73687574646f776e)) -- AND pass=" AND pin=

Inference:

推理：

In this attack, the query is modified to recast it in the form of an action that is executed based on the answer to a true/- false question about data values in the database. In this type of injection, attackers are generally trying to attack a site that has been secured enough so that, when an injection has succeeded, there is no usable feedback via database error messages.

在这种攻击中，查询被修改为以基于数据库中有关数据值的对/错问题的答案所执行的操作的形式重铸。 在这种类型的注入中，攻击者通常会尝试攻击已经足够安全的站点，以便在注入成功后，不会通过数据库错误消息提供可用的反馈。

Since database error messages are unavailable to provide the attacker with feedback, attackers must use a different method of obtaining a response from the database.

由于无法使用数据库错误消息向攻击者提供反馈，因此攻击者必须使用其他方法从数据库获取响应。

There are two well-known attack techniques that are based on inference:

有两种基于推断的著名攻击技术：

Blind Injection: In this technique, the information must be inferred from the behavior of the page by asking the server true/- false questions.

盲注：在这种技术中，必须通过询问服务器是非题来从页面的行为中推断出信息。

SELECT accounts FROM users WHERE login='legalUser'
and 1=0 -- ' AND pass=" AND pin=0
SELECT accounts FROM users WHERE login='legalUser'
and 1=1 -- ' AND pass=" AND pin=0

Timing Attacks: A timing attack allows an attacker to gain information from a database by observing timing delays in the response of the database. This attack is very similar to blind injection, but uses a different method of inference.

定时攻击：定时攻击使攻击者可以通过观察数据库响应中的定时延迟来从数据库中获取信息。 这种攻击与盲注非常相似，但是使用了不同的推理方法。

SELECT accounts FROM users WHERE login='legalUser' and
ASCII(SUBSTRING((select top 1 name from sysobjects),1,1))
> X WAITFOR 5 -- ' AND pass=" AND pin=0

________________________

________________________

Defenses from SQL Injection attacks

防止SQL注入攻击

________________________

________________________

To defend against SQL Injection attacks, user input must not directly be embedded in SQL statements. Instead, you can use parameterized statements, and Escaping functions to check user input and storage procedure.

为了抵御SQL注入攻击，不得将用户输入直接嵌入SQL语句中。 而是可以使用参数化语句和转义功能来检查用户输入和存储过程。

Using Parameterized Statements:

使用参数化语句：

Prepared queries (also called prepared statements) solve a great many of the aforementioned risks. Prepared queries are query templates: the structure of the query is pre-defined and fixed and includes placeholders that stand-in for real data.

准备好的查询（也称为准备好的语句）解决了许多上述风险。 预先准备的查询是查询模板：查询的结构是预先定义和固定的，并包含可代替真实数据的占位符。

The placeholders are typically type-specific (for example, int for integer data and text for strings) which allows the database to interpret the data strictly. For instance, a text placeholder is always interpreted as a literal, avoiding exploits such as the query stacking SQL injection. A mismatch between a placeholder's type and its incoming datum causes execution errors, adding further validation to the query.

占位符通常是特定于类型的（例如，用于整数数据的int和用于字符串的文本），这允许数据库严格解释数据。 例如，文本占位符始终被解释为文字，从而避免了诸如查询堆栈SQL注入之类的漏洞。 占位符的类型与其传入的基准之间的不匹配会导致执行错误，从而进一步增加了对查询的验证。

In PHP version 5 and above, you have multiple choices for using parameterized statements. PDO database layer is one of them:

在PHP 5及更高版本中，使用参数化语句有多种选择。 PDO数据库层是其中之一：

$db = new PDO('pgsql:dbname=database');
$stmt = $db->prepare("SELECT priv FROM testUsers WHERE username=:username AND password=:password");
$stmt->bindParam(':username', $user);
$stmt->bindParam(':password', $pass);
$stmt->execute();

$db = new mysqli("localhost", "user", "pass", "database");
$stmt = $db -> prepare("SELECT priv FROM testUsers WHERE username=? AND password=?");
$stmt -> bind_param("ss", $user, $pass);
$stmt -> execute();

Using Escaping:

使用转义：

A straight-forward, though error-prone way to prevent injections is to escape dangerous characters. One of the reasons for it being error prone is that it is a type of blacklist which is less robust than a whitelist. For instance, every occurrence of a single quote (') in a parameter must be replaced by two single quotes ('') to form a valid SQL string literal. In PHP, for example, it is usual to escape parameters using the function mysql_real_escape_string before sending the SQL query.

防止注入的直接但容易出错的方法是逃避危险字符。 它容易出错的原因之一是，它是一种黑名单，其强度不如白名单。 例如，参数中每次出现的单引号（'）都必须替换为两个单引号（''），以形成有效SQL字符串文字。 例如，在PHP中，通常在发送SQL查询之前使用mysql_real_escape_string函数对参数进行转义。

1. mysql_real_escape_string escapes special characters in the unescaped_string, taking into account the current character set of the connection so that it is safe to place it in a mysql_query(). If binary data is to be inserted, this function must be used.

1. mysql_real_escape_string将unescaped_string中的特殊字符转义，并考虑到连接的当前字符集，以便可以安全地将其放置在mysql_query（）中。 如果要插入二进制数据，则必须使用此功能。

2. mysql_real_escape_string() calls MySQL's library function 3. mysql_real_escape_string, which prepends backslashes to the following characters: \x00, \n, \r, \, ', " and \x1a.

2. mysql_real_escape_string（） 调用MySQL的库函数3。mysql_real_escape_string，它在反斜杠前添加以下字符：\ x00，\ n，\ r，\，'，“和\ x1a。

<form  enctype="application/x-www-form-urlencoded" 
method="post" action="<?php $_SERVER['PHP_SELF']?>">
<input type="text" name="name" value="">
<input type="submit" value="submit">
</form>
<?php
$link = mysql_connect('localhost', 'root', '');
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
$item = $_POST['name'];
$escaped_item = mysql_real_escape_string($item, $link);
echo"Escaped string: \n", $escaped_item; 
// input: Zak's and Derick's Laptop
// output:  Zak\'s and Derick\'s Laptop
 
// input: a';DROP TABLE users; SELECT * FROM data WHERE name LIKE
// output: a\';DROP TABLE users; SELECT * FROM data WHERE name LIKE  
?>

Most techniques for the treatment of SQL Injection refer to the functions of escape, but few know that these techniques are ineffective to prevent such an attack. The simple addition of a backslash ( "\") in a string containing an apostrophe ( ') will not eliminate the possibility of exploitation of attacks based on SQL Injection

大多数用于处理SQL Injection的技术都涉及转义功能，但很少有人知道这些技术无法有效地防止此类攻击。 在包含撇号（'）的字符串中简单添加反斜杠（“ \”）不会消除使用基于SQL注入的攻击的可能性

Now I will show you a good example to prevent sql injection

现在，我将向您展示一个防止sql注入的好例子

<?php 
function anti_injection($sql){
   $sql = preg_replace(sql_regcase("/(from|select|insert|delete|where|drop table|like|show tables|\'|'\| |=|-|;|,|\|'|<|>|#|\*|--|\\\\)/"), "" ,$sql);
  
   $sql = trim($sql);
   $sql = strip_tags($sql);
   $sql = (get_magic_quotes_gpc()) ? $sql : addslashes($sql);
   return $sql;
} 
?>
<form  enctype="application/x-www-form-urlencoded" method="post" action="<?php $_SERVER['PHP_SELF']?>">
<input type="text" name="name" value="">
<input type="submit" value="submit">
</form>
<?php 
$name = anti_injection($_POST['name']);
echo $name 
// input: Zak's and Derick's Laptop
// output: Zaks and Dericks Laptop
 
// input: a';DROP TABLE users; SELECT * FROM data WHERE name LIKE 
// output: a users data name  
?>

Unlike the mysql_real_escape_sting that filters only the following characters \ x00, \ n, \ r, \, ', "and \ x1a, this enables not only filter the same characters as the mysql_real_escape_string but filtering words like" DROP TABLE ", "LIKE", "INSERT" and more.

与mysql_real_escape_sting仅过滤以下字符\ x00，\ n，\ r，\，'，“和\ x1a不同，这不仅允许过滤与mysql_real_escape_string相同的字符，而且还过滤诸如” DROP TABLE“，” LIKE“之类的单词，“ INSERT”等。

One of the great advantages of this example is that you can add more characters and words to the function to be filtered, just to put it between || the value name. Example: |drop table|\'|*|+|_|like|

此示例的一大优点是，您可以在要过滤的函数中添加更多字符和单词，只需将它们放在||之间即可。 值名称。 例如：| drop table | \'| * | + | _ | like |

Conclusion:

结论：

Many web developers are unaware of how SQL queries can be tampered with, and assume that an SQL query is a trusted command. It means that SQL queries are able to circumvent access controls, thereby bypassing standard authentication and authorization checks, and sometimes SQL queries even may allow access to host operating system level commands.

许多Web开发人员都不知道如何篡改SQL查询，并假定SQL查询是受信任的命令。 这意味着SQL查询能够绕过访问控制，从而绕过标准的身份验证和授权检查，有时SQL查询甚至可能允许访问主机操作系统级别的命令。

Direct SQL Command Injection is a technique where an attacker creates or alters existing SQL commands to expose hidden data, or to override valuable ones, or even to execute dangerous system level commands on the database host. This is accomplished by the application taking user input and combining it with static parameters to build a SQL query.

直接SQL命令注入是一种技术，攻击者可以创建或更改现有SQL命令来公开隐藏数据，覆盖重要数据，甚至在数据库主机上执行危险的系统级命令。 这是通过应用程序获取用户输入并将其与静态参数组合以构建SQL查询来完成的。

The following examples are based on true stories, unfortunately. Owing to the lack of input validation and connecting to the database on behalf of a superuser or the one who can create users, the attacker may create a superuser in your database.

不幸的是，以下示例基于真实故事。 由于缺乏输入验证，并且代表一个超级用户或可以创建用户的用户连接到数据库，因此攻击者可能在您的数据库中创建一个超级用户。

You may plead that the attacker must possess a piece of information about the database schema in most examples. You may be right, but you never know when and how it can be taken out, and if it happens, your database may be exposed.

您可能会辩称，在大多数示例中，攻击者必须拥有有关数据库架构的一条信息。 您可能是正确的，但您永远不知道何时以及如何将其删除，如果发生，数据库可能会暴露出来。

If you are using an open source, or publicly available database handling package, which may belong to a content management system or forum, the intruders can easily produce a copy of a piece of your code. It may be also a security risk if it is a poorly designed script.

如果您使用的是开放源代码或可公开使用的数据库处理程序包（可能属于内容管理系统或论坛），则入侵者可以轻松地生成一段代码的副本。 如果脚本设计不当，也可能带来安全风险。

These attacks are mainly based on exploiting the code not being written with security in mind.

这些攻击主要基于利用未考虑安全性而编写的代码。

My advice:

我的建议：

Never trust any kind of input, especially that which comes from the client side, even though it comes from a select box, a hidden input field or a cookie.  

永远不要信任任何类型的输入，尤其是来自客户端的输入，即使它来自选择框，隐藏的输入字段或cookie。

Regards

问候

André Bolinhas

安德烈·波利尼亚斯（AndréBolinhas）

翻译自: https://www.experts-exchange.com/articles/686/PHP-Prevent-SQL-Injection.html

防止sql注入 php