MySQL and SQL injection and prevention methods

The so-called SQL injection is to insert SQL commands into Web form submissions or enter domain names or query strings for page requests, ultimately tricking the server into executing malicious SQL commands.

We should never trust user input. We must determine that the data entered by the user is not safe. We all need to filter the data entered by the user.

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

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

Let us take a look at the SQL situation that occurs when special characters are not filtered:

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

In the above injection statement, we did not filter the $name variable. An unnecessary SQL statement was inserted into $name, which will delete all data in the users table.

2. Mysql_query() in PHP does not allow the execution of 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. To verify the user's input, you can use regular expressions or limit the length; convert single quotes and double "-", etc.
2. Never use dynamic assembly of sql. You can use parameterized sql or directly use stored procedures for data query and access.
3. Never use a database connection with administrator privileges. Use a separate database connection with limited privileges for each application.
4. Do not store confidential information directly, encrypt or hash passwords and sensitive information.
5. The application’s exception information should give as few hints as possible. It is best to use custom error information to wrap the original error information
6. SQL injection detection methods generally use auxiliary software or website platforms 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 etc. Using MDCSOFT-IPS can effectively defend against SQL injection, XSS attacks, etc.

3. Prevent SQL injection

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

PHP's MySQL extension provides the mysql_real_escape_string() function to escape special input characters.

if (get_magic_quotes_gpc()) 
{
 $name = stripslashes($name);
}
$name = mysql_real_escape_string($name);
mysql_query("SELECT * FROM users WHERE name='{$name}'");

​4.Injection in Like statement

When querying like, if the values ​​entered by the user include "_" and "%", this situation will occur: the user originally only wanted to query "abcd_", but the query results include "abcd_", "abcde", and "abcdf" Wait; problems will also occur when users want to query "30%" (note: thirty percent).

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

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

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

Syntax format:

​addcslashes(string,characters)

Parameter Description

​string Required. Specifies the string to check.

characters optional. Specifies the characters or range of characters affected by addcslashes().

The above is the detailed content of MySQL and SQL injection and prevention methods. For more information, please follow other related articles on the PHP Chinese website!