MySQL and SQL injection

If you get the data entered by the user through the web and insert it into a MySQL database, then it is possible SQL injection security problems.

This section will tell you how to prevent SQL injection, and to filter SQL injection characters in a script.

The so-called SQL injection, is through the SQL command into the Web form submit or enter the domain name or page request query string, and ultimately deceive server to execute malicious SQL commands.

We never trust user input, we must identify the data entered by the user are insecure, we all need for user input data filtering process.

The following example, the user name entered must be a combination of letters, numbers and underscores, and the user name length is between 8-20 characters:

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 situation when there is no SQL filter special characters appear:

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

Above injection statement, we do not have to filter the variable $ name, $ name we do not need to insert the SQL statement will delete all the data in the table users.

mysql_query in PHP () is not allowed to execute multiple SQL statements, but in SQLite and PostgreSQL can execute multiple SQL statements simultaneously, so users of these data we need strict verification.

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

• 1. Never trust user input. Verification of user input, you can use regular expressions, or limit the length; single and double quotation marks "-" in conversion.
• 2. Never use dynamic assembly sql, you can use the parameters of the direct use of sql or stored procedures for data query access.
• 3. Never use administrator privileges database connection, use limited individual permissions for each application database connection.
• 4. Do not store confidential information directly, encryption or hash out passwords and sensitive information.
• 5. The application exception information should be given as few prompts, it is best to use a custom error message Error message original packaging
• Detection 6.sql injection site in general or supporting software platform to detect, the software commonly used sql injection detection tools jsky, internet site website billion thinking there is security platform detection tools. MDCSOFT SCAN like. Using MDCSOFT-IPS can be effective defense SQL injection, XSS attacks.

Prevent SQL injection

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

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

Like statement injection

like the query, if the user enters a value of "_" and "%", then this happens: The user was just trying query "abcd_", query results there "abcd _", "abcde", "abcdf" etc; user to query the "30%" (Note: thirty percent) will have problems.

In the PHP script, we can use addcslashes () function to handle the above case, the following examples:

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

addcslashes () function before specified with a backslash.

Syntax:

addcslashes(string,characters)

You can view the specific application: the PHP addcslashes () function