YouTipIf you obtain user input data through a web page and insert it into a MySQL database, there is a potential risk of SQL injection security issues.
\n\nSQL injection refers to the insertion of SQL commands into web form submissions, input domains, or page request query strings, ultimately deceiving the server into executing malicious SQL commands.
\n\nMySQL injection occurs when an attacker successfully executes malicious SQL queries through maliciously constructed input. This typically happens when user input is not properly validated or escaped. Attackers attempt to insert SQL code into the input to execute unexpected queries or compromise the database.
\n\nWe should never trust user input. We must assume that all user input data is insecure and requires filtering and processing.
\n\nConsider a login system where users authenticate by entering a username and password:
\n\nSELECT * FROM users WHERE username = 'input_username' AND password = 'input_password';Without proper input validation and preventive measures, an attacker could enter a username similar to the following:
\n\n' OR '1'='1'; --In this case, the SQL query becomes:
\n\nSELECT * FROM users WHERE username = '' OR '1'='1'; --' AND password = 'input_password';This would cause the query to return all users because 1=1 is always true. The comment symbol -- is used to comment out the rest of the original query to ensure correct syntax.
\n\nPreventing SQL Injection:
\n\n- \n
- 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 execution. \n
- Input validation and escaping: Properly validate user input and use appropriate escaping functions (such as
mysqli_real_escape_string) to handle input, preventing malicious injection. \n - Principle of least privilege: Grant database users the minimum necessary permissions, ensuring they can only perform required operations to reduce potential damage. \n
- 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. \n
- 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. \n
\n\n
Practical Examples
\n\nIn the following examples, the input username must be a combination of letters, numbers, and underscores, with a length between 8 and 20 characters:
\n\nExample
\n\nIF(preg_match("/^w{8,20}$/", $_GET['username'], $matches)){\n\n$result = mysqli_query($conn,"SELECT * FROM users \n\n WHERE username=$matches");\n\n}\n\nELSE\n\n{\n\necho "username Input exception";\n\n}Let's look at the SQL situation when special characters are not filtered:
\n\n// Setting$name Inserted unnecessary 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. Unwanted SQL statements were inserted into $name, which would delete all data in the users table.
\n\nIn PHP, mysqli_query() does not allow executing multiple SQL statements, but SQLite and PostgreSQL can execute multiple SQL statements simultaneously. Therefore, we need to strictly validate data from these users.
To prevent SQL injection, we need to pay attention to the following key points:
\n\n- \n
- 1. Never trust user input -- Validate user input using regular expressions, limit length, escape single quotes and double equals, etc. \n
- 2. Never use dynamically assembled SQL -- Use parameterized SQL or directly use stored procedures for data query and storage. \n
- 3. Never use database connections with administrator privileges -- Use separate, limited-privilege database connections for each application. \n
- 4. Do not store confidential information directly -- Use hash encryption for passwords and sensitive information. \n
- 5. Application exception messages should provide minimal hints -- It's best to use custom error messages to wrap original error information. \n
- 6. SQL injection detection methods generally use auxiliary software or website platforms for detection -- Use specialized vulnerability scanning tools (such as sqlmap, Acunetix, Netsparker) for automated SQL injection detection of applications. \n
Preventing SQL Injection
\n\nIn scripting languages like Perl and PHP, you can escape user input data to prevent SQL injection.
\n\nPHP's MySQL extension provides the mysqli_real_escape_string() function to escape special input characters.
Example
\n\nIF(get_magic_quotes_gpc()){\n\n $name = stripslashes($name);\n\n}\n\n $name = mysqli_real_escape_string($conn, $name);\n\nmysqli_query($conn,"SELECT * FROM users WHERE name='{$name}'");\n\n
Injection in LIKE Statements
\n\nWhen using LIKE queries, if the user input contains _ and %, this situation can occur: the user originally only wanted to query abcd_, but the query results include "abcd_", "abcde", "abcdf", etc.; similar issues arise when users want to query "30%" (meaning thirty percent).
\n\nIn PHP scripts, we can use the addcslashes() function to handle the above situation, as shown in the following example:
Example
\n\n$sub=addcslashes(mysqli_real_escape_string($conn,"%s omething_"),"%_");\n\n// $sub == %something_\n\nmysqli_query($conn,"SELECT * FROM messages WHERE subject LIKE '{$sub}%'");The addcslashes() function adds backslashes before specified characters.
Syntax:
\n\naddcslashes(string,characters)| Parameter | Description |
|---|---|
| string | Required. Specifies the string to check. |
| characters | Optional. Specifies the character or range of characters affected by addcslashes(). |
For specific applications, see: PHP addcslashes() Function