[mysql tutorial] MySQL and SQL injection

MySQL and SQL Injection

If you obtain user-entered data through a web page and insert it into a MySQL database, SQL injection security issues may occur.

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

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 assume that the data entered by the user is not safe. We all need to filter the data entered by the user.

In the following example, the entered username must be a combination of letters, numbers, and underscores, and the username 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 see if When filtering special characters, the SQL situation that occurs:

// 设定$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 be deleted from the users table. all data.

Mysql_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. 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.

v 4. Do not store confidential information directly, encrypt or hash passwords and sensitive information.

5. The application's exception information should give as few prompts as possible. It is best to use customized error information to wrap the original error information

6. The detection method of SQL injection is generally adopted Auxiliary software or website platform is used for detection. 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.

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}'");

Injection

like query in the

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

Like statement, if the values ​​entered by the user include "_" and "%", this situation will occur: the user originally just wanted to query "abcd_ ", but there are "abcd_", "abcde", "abcdf", etc. in the query results; problems will also occur when the user wants to query "30%" (Note: 30%).

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

addcslashes(string,characters)

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

Syntax format:

rrreee

Parameters

Description

string Required. Specifies the string to check.

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

For specific applications, please view: PHP addcslashes() function

The above is the content of [mysql tutorial] MySQL and SQL injection. For more related content, please pay attention to the PHP Chinese website (www.php.cn )!

*&**&*