How does MySQL prevent sql injection? SQL injection principle and prevention

MySQLHow to prevent sql injection? SQL injection principle and prevention

MySQL and SQL injection

If you take the data entered by the user through a web page and insert it into aMySQL database, then there may be SQL injection security problems.

This chapter will introduce how to prevent SQL injection and filter the injected characters in SQL through scripts.

The so-called SQL injection is to deceive the server to execute malicious SQL commands by inserting SQL commands into the web form to submit or input the query string of the domain name or page request.

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

In the following example, the username entered must be a combination of letters, numbers, and underscores, and the username 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 that occurs when no special characters are 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 variable of $name, and the SQL statement that we do not need is inserted into $name, which will delete all the data in the users table.

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

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

• 1. Never trust user input.Check the user's input, you can use regular expressions, or limit the length; convert single quotes and double "-", etc.
• 2. Never use dynamic assembly sql, you can use parameterized sql or directly use stored procedures for data query and access.
• 3. Never use database connections with administrator privileges, use separate database connections with limited privileges for each application.
• 4. Do not store confidential information directly, encrypt or hash out passwords and sensitive information.
• 5. The exception information of the application should give as few hints as possible, and it is best to use custom error information to wrap the original error information
• 6. The detection method of sql injection generally adopts auxiliarysoftwareOr the website platform to detect, the software generally uses the sql injection detection tool jsky, and the website platform has the Yisi website security platform detection tool. MDCSOFT SCAN et al.Using MDCSOFT-IPS can effectively defend against SQL injection, XSS attacks, etc.

Prevent SQL Injection

In scripting languages ​​such as Perl and PHP you can escape the data entered by the user to prevent SQL injection.

The MySQL extension for PHP provides the mysqli_real_escape_string() function to escape special input characters.

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

Injection in Like Statements

When querying like, if the user enters values ​​with "_" and "%", this will happen: the user originally wanted to query "abcd_", but the query results include "abcd_", "abcde", and "abcdf" Etc.; the problem also occurs when the user wants to query "30%" (note: thirty percent).

In the PHP script we can use the addcslashes() function to handle the above situation, as in the following example:

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

The addcslashes() function adds a backslash before the specified character.

Syntax format:

addcslashes(string,characters)