MySQL and SQL Injection

If you obtain user input data through a web page and insert it into a MySQL database, there is a risk of SQL injection security issues occurring.

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

SQL injection involves inserting SQL commands into web form submissions, input fields, or page request query strings to deceive the server into executing malicious SQL commands.

We should never trust user input. We must assume that all user input is unsafe and filter it.

In the following example, the input username must consist of letters, numbers, and underscores, and be between 8 and 20 characters long:

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

Let's look at the SQL situation without filtering special characters:

// Setting $name with unwanted SQL statements
$name = "Qadir'; DELETE FROM users;";
 mysqli_query($conn, "SELECT * FROM users WHERE name='{$name}'");

In the above injection statement, we did not filter the variable $name, which contains unwanted SQL statements, and will delete all data in the users table.

In PHP, mysqli_query() does not allow executing multiple SQL statements, but SQLite and PostgreSQL do. Therefore, we need strict validation for these user data.

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

• 1. Never trust user input. Validate user input with regular expressions or limit length; convert single quotes and double hyphens, etc.
• 2. Never use dynamic SQL assembly. Use parameterized SQL or stored procedures for data query and retrieval.
• 3. Never use administrator privileges for database connections. Use separate, limited-privilege database connections for each application.
• 4. Do not store confidential information directly; encrypt or hash passwords and sensitive information.
• 5. Provide minimal information in application error messages. Customize error messages to wrap the original error messages.
• 6. SQL injection detection is usually done with auxiliary software or website platforms. Software typically uses SQL injection detection tools like jsky, and website platforms include security detection tools like MDCSOFT SCAN. MDCSOFT-IPS can effectively defend against SQL injection, XSS attacks, etc.

Preventing SQL Injection

In scripting languages like 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

In LIKE queries, if the user input value contains "_" and "%", issues can arise: the user intended to search for "abcd_", but results include "abcd_", "abcde", "abcdf", etc.; searching for "30%" (note: thirty percent) can also cause problems.

In PHP scripts, we can use the addcslashes() function to handle these cases, 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 specified characters.

Syntax:

addcslashes(string, characters)

For specific applications, see: PHP addcslashes() Function