How to protect your website from SQL injection attacks?

How to protect a website from SQL injection attacks?

SQL injection attack means that the attacker uses the input mechanism of legitimate users in the site to send SQL code to the database for execution. The golden rule to avoid SQL injection attacks is: all data from external sources Escape it before reaching the database. This rule applies not only to INSERT and UPDATE statements, but also to SELECT queries~

Using precompiled statements in script queries can almost eliminate all SQL injection attack problems, but if you choose to use the query() method, There will be no such protection - user input added to the query must be manually escaped. The code is as follows:

<?php
    $dbms = "mysql";                                  // 数据库的类型
    $dbName ="php_cn";                                //使用的数据库名称
    $user = "root";                                   //使用的数据库用户名
    $pwd = "root";                                    //使用的数据库密码
    $host = "localhost";                              //使用的主机名称
    $dsn  = "$dbms:host=$host;dbname=$dbName";
    $name =&#39;mr&#39;;
    $name ="&#39;or Name LIKE&#39;%";
    $query="select * from user where username = &#39;".$name."&#39;";//sql语句
try {
    $pdo = new PDO($dsn, $user, $pwd);//初始化一个PDO对象，就是创建了数据库连接对象$pdo
    $result = $pdo->query($query);    //输出结果集中的数据
    foreach ($result as $row){        //输出结果集中的数据
        ?>
<tr>
    <td bgcolor="#FFFFFF"><div align="center"><?php echo $row[&#39;username&#39;];?></div></td>
</tr>
<?php
    }
}catch (Exception $e){
    echo "ERROR!!".$e->getMessage()."<br>";
}
?>

In this example, it is assumed that the $name variable in the SQL statement comes from the form submitted by the user. Usually the user will submit something like "mr", which will generate the following SQL statement:

select * from user where username = 'mr'

This will not cause any problems in the script, however, a "smart" attacker may Enter "'OR Name LIKE'%", which results in the following SQL statement:

select * from user where username = 'mr'

This will not cause any problems in the script, however, a "smart" attacker may enter "'OR Name LIKE'%", thus obtaining the following SQL statement:

select * from user where username ="OR Name LIKE'%'

This input will open the entire table for the attacker, and if it is very sensitive data, it will be completely exposed. So what method can be used to solve this problem?

Solution

To solve this problem, you can use the quote method in PDO to escape the data passed to the SQL string. The SQL code needs to be modified to:

$sql="select * from user where username = '".$pdo->quote($name)."'";

Remember that every piece of data used in the SQL query needs to be quoted. There is no shortcut for this unless you consider using the prepare() and execute() methods.

Note:

If you are using the PDO->query() method, you always need to use quote( ) method refers to the user's input, remember it is "always required".

If you choose to use the prepare() and execute() methods, you do not have to reference values ​​that need to be bound to the precompiled SQL (for example: to be inserted into the import database value), the driver will do everything for the user. However, sometimes it may not be possible to bind a variable to a precompiled SQL statement. In this case, if you are creating a dynamic SQL statement, you need to quote all the Values ​​that cannot be bound (such as GROUP BY clause or ORDER BY or table name).

So that’s all the solutions on how to protect Web sites from SQL injection attacks. I believe everyone can easily master the knowledge points in this section~

The above is the detailed content of How to protect your website from SQL injection attacks?. For more information, please follow other related articles on the PHP Chinese website!