MySQL and SQL Injection

If you obtain user input data through a webpage and insert it into a MySQL database, there may be SQL injection security issues.

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

SQL injection refers to inserting SQL commands into web form submissions, input domain names, or page request query strings, ultimately to deceive the server to execute malicious SQL commands.

We should never trust user input, we must assume that the data entered by users are not secure, and we all need to filter the data entered by users.

In the following examples, the input username must be a combination of letters, numbers, and underscores, and the length of the username must be 8 to 20 characters between:

if (preg_match("/^\w{8,20}$/", $_GET['username'], $matches))
{
　　　$result = mysqli_query($conn, "SELECT　*　FROM users　
　　　　　　　　　　　　　　　　　　　　　　　　　　WHERE username=$matches[0]");
}
　else　
{
　　　echo "username input exception";
}

Let's see the SQL situation when special characters are not filtered:}

//　Set $name to insert the unnecessary SQL statements
$name = "Qadir'; DELETE FROM users;";
　mysqli_query($conn, "SELECT　*　FROM users WHERE name='{$name}');

In the above injection statements, we did not filter the $name variable, and the $name inserted contains SQL statements we do not need, which will delete all data from the users table.

In PHP, mysqli_query() does not allow the execution of multiple SQL statements, but in SQLite and PostgreSQL, multiple SQL statements can be executed simultaneously, so we need to strictly verify these user data.

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

• 1.Never trust user input. Validate user input, which can be done through regular expressions, or by limiting length; for single quotes and Double quotes-"for conversion, etc.

• 2.Never use dynamically assembled SQL; you can use parameterized SQL or directly use stored procedures for data query and storage.

• 3.Never use database connections with administrative privileges; use separate, limited-privilege database connections for each application.

• 4.Do not store confidential information directly; encrypt or hash the passwords and sensitive information.

• 5.The application's exception information should provide as few hints as possible, and it is best to wrap the original error information with custom error messages

• 6.The detection methods for SQL injection generally involve auxiliary software or website platforms, such as the SQL injection detection tool jsky for software, and the Yisi Website Security Platform detection tool for websites. MDCSOFT SCAN, etc. Using MDCSOFT-IPS can effectively defend against SQL injection, XSS attacks, and more.

Preventing SQL Injection

In scripting languages such as Perl and PHP, you can escape user input data to prevent SQL injection.

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

if (get_magic_quotes_gpc())　
{
　　$name = stripslashes($name);
}
$name = mysqli_real_escape_string($conn, $name);
　mysqli_query($conn, "SELECT　*　FROM users WHERE name='{$name}');

Injection in Like statements

When performing a LIKE query, if the user's input contains "_" and "%", this situation may occur: The user originally intended to query "abcd_", but the query results include "abcd_", "abcde", "abcdf", and so on; the user needs to query"30%" (Note: thirty percent) may also cause problems.

We can use the addcslashes() function in PHP scripts to handle the above situations, as shown in the following example:

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

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

Syntax Format:

addcslashes(string,characters)

For specific applications, please see:PHP addcslashes() Function