4.2.32. MySQL and SQL injection

If you get the data entered by the user through a web page and insert it into a MySQL database, then the security problem of SQL injection may occur.

This section will show you how to prevent SQL injection and use scripts to filter characters injected into SQL.

The so-called SQL injection is to deceive the server into executing malicious SQL commands by inserting SQL commands into the Web form or entering the query string of the domain name or page request.

We should never trust the user’s input, we must assume that the data entered by the user is unsafe, and we all need to filter the data entered by the user.

In the following example, the user name entered must be a combination of letters, numbers, and underscores, and the user name must be between 8 and 20 characters long:

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
   $result = mysqli_query($conn, "SELECT * FROM users
                          WHERE username=$matches[0]");
}
 else
{
   echo "username 输入异常";
}

Let’s take a look at the SQL situation when special characters are not filtered:

// 设定$name 中插入了我们不需要的SQL语句
$name = "Qadir'; DELETE FROM users;";
 mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

In the above injection statement, we did not filter the variables of $name, and we inserted an unneeded SQL statement in $name, which will delete all the data in the users table.

In PHP mysqli_query() Multiple SQL statements are not allowed to be executed, but multiple SQL statements can be executed at the same time in SQLite and PostgreSQL, so we need to strictly validate the data of these users.

To prevent SQL injection, we need to pay attention to the following points:

• 1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和 双”-“进行转换等。

• 2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。

• 3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。

• 4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。

• 5.应用的异常信息应该给出尽可能少的提示，最好使用自定义的错误信息对原始错误信息进行包装

• The detection method of 6.sql injection is generally detected by auxiliary software or website platform, and the software generally adopts sql injection detection tool jsky, and the website platform has Yisi website security platform detection tool. MDCSOFT SCAN et al. MDCSOFT-IPS can effectively defend against SQL injection, XSS attacks and so on.

if (get_magic_quotes_gpc())
{
  $name = stripslashes($name);
}
$name = mysqli_real_escape_string($conn, $name);
 mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

$sub = addcslashes(mysqli_real_escape_string($conn, "%something_"), "%_");
// $sub == \%something\_
 mysqli_query($conn, "SELECT * FROM messages WHERE subject LIKE '{$sub}%'");

addcslashes(string,characters)