MySQL and SQL injection

MySQL and SQL Injection

If you get user-entered data through a web page and insert it into a MySQL database, then 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 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 输入异常";
}

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

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 process the data of these users. Rigorous verification.

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

• 1. Never trust user input. To verify user input, you can use regular expressions or limit the length; for single quotes and Double "-" for conversion, 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 prompts 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.

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 in Like statement

When querying like, if the values ​​entered by the user include "_" and "%", this situation will occur: the user originally just wanted to query "abcd_", and the query results However, there are "abcd_", "abcde", "abcdf", etc.; problems will also occur when users want 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:

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

Grammar format:

addcslashes(string,characters)

mysql tutorial