MySQL and SQL Injection

If you obtain user input data through a web page and insert it into a MySQL database, SQL injection security issues may occur.

The so-called SQL injection is the process of inserting SQL commands into query strings submitted via web forms or entered in domain names or page requests, ultimately tricking the server into executing malicious SQL commands.

MySQL injection refers to an attacker successfully executing malicious SQL queries through maliciously crafted input. This usually occurs when user input is not properly validated or escaped. The attacker attempts to insert SQL code into the input to execute unintended queries or damage the database.

We should never trust user input. We must assume that all user input data is insecure, and we need to filter and process all user input data.

Suppose there is a login system where users authenticate by entering a username and password:

SELECT * FROM users WHERE username = 'input_username' AND password = 'input_password';

Without proper input validation and preventive measures, an attacker can enter a username similar to the following:

' OR '1'='1'; --

In this case, the SQL query becomes:

SELECT * FROM users WHERE username = '' OR '1'='1'; --' AND password = 'input_password';

This causes the query to return all users because1=1is always true, and the comment symbol -- is used to comment out the rest of the original query to ensure correct syntax.

Preventing SQL Injection:

• Use parameterized queries or prepared statements:Using parameterized queries (Prepared Statements) can effectively prevent SQL injection because they separate input data from the query statement before executing the query.

• Input validation and escaping:Properly validate user input and use appropriate escaping functions (such asmysqli_real_escape_string) to process the input and prevent malicious injection.

• Principle of least privilege:Give database users the least privileges, ensuring they can only perform necessary operations to reduce potential damage.

• Use ORM frameworks:Using Object-Relational Mapping (ORM) frameworks (such as Hibernate, Sequelize) can help abstract SQL queries, thereby reducing the risk of SQL injection.

• Disable error message display:In production environments, disable the display of detailed error messages to prevent attackers from obtaining sensitive information about the database structure.

Application Examples

In the following example, the entered username must be a combination of letters, digits, and underscores, and the username length must be between 8 and 20 characters:

Let's look at the SQL situation that occurs when special characters are not filtered:

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

In the above injection statement, we did not filter the $name variable. An unwanted SQL statement was inserted into $name, which will delete all data from the users table.

In PHP, mysqli_query() does not allow executing multiple SQL statements, but in SQLite and PostgreSQL, multiple SQL statements can be executed at the same time. Therefore, we need to strictly validate the data for these users.

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

• 1. Never trust user input-- Validate user input using regular expressions, restricting length, escaping single quotes, double quotes, etc.
• 2. Never use dynamic SQL concatenation-- Use parameterized SQL or directly use stored procedures for data query and storage.
• 3. Never use database connections with administrator privileges-- Use a separate database connection with limited privileges for each application.
• 4. Do not store confidential information directly-- Use hash to encrypt passwords and sensitive information.
• 5. Application exception messages should provide as little information as possible-- It is best to use custom error messages to wrap the original error messages.
• 6. SQL injection detection methods generally use auxiliary software or website platforms for detection-- Use specialized vulnerability scanning tools (such as sqlmap, Acunetix, Netsparker) to perform automated SQL injection detection on applications.

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.

Injection in LIKE Statements

In a LIKE query, if the value entered by the user contains_and%, the following situation may occur: the user originally only wanted to queryabcd_, but the query results include "abcd_"、"abcde"、"abcdf"and so on; problems also occur when the user wants to query "30%" (note: thirty percent).

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

The addcslashes() function adds backslashes before specified characters.

Syntax:

addcslashes(string,characters)

For specific applications, see:PHP addcslashes() Function