MySQL and SQL Injection

MySQL and SQL Injection

If you take user-entered data through a Web page and insert it into a MySQL database, sql injection security issues can occur.

This section will show you how to prevent SQL injection and script to filter the characters injected in SQL.

Sql injection is the query string that descepts the server from executing malicious SQL commands by inserting SQL commands into a Web form to submit or enter a domain name or page request.

We should never trust the user's input, we must assume that the user input data is not secure, we all need to filter the user input data processing.

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 = mysql_query("SELECT * FROM users 
                          WHERE username=$matches[0]");
}
 else 
{
   echo "username 输入异常";
}

Let's 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 injection statement above, we did not filter the variables of $name, and the SQL statement that we didn't need was inserted in $name, which deletes all the data in the users table.

mysql_query() in PHP does not allow multiple SQL statements to be executed, but in SQLite and PostgreSQL multiple SQL statements can be executed at the same time, so the data of these users needs to be rigorously validated.

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

• 1. Never trust the user's input. The user's input can be verified by regular expressions, or by limiting length, single quotes and double "-" conversions, and so on.
• 2. Never use dynamic assembly sql, you can use parametic sql or use stored procedures directly for data query access.
• 3. Never use an administrator-privileged database connection, and use a separate database connection with limited permissions for each app.
• 4. Do not store confidential information directly, encrypt or hash off passwords and sensitive information.
• 5. The applied exception information should give as few hints as possible, it is best to use custom error messages to wrap the original error messages
• 6.sql injection detection methods generally take auxiliary software or website platform to detect, software generally use sql injection detection tool jsky, website platform has Ethyme website security platform detection tools. M DCSOFT SCAN, etc. 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 mysql_real_escape_string functions 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 the Like statement

This occurs when the user enters values of " and "%": the user would have just wanted to query "abcd_" and the query results would have "abcd_," "abcde," "abcdf," and so on;

In PHP scripts, we can use the addcslashes() function to handle the above situation, as follows:

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

Grammar format:

addcslashes(string,characters)

Specific applications can be viewed: PHP addcslashes() function