SQL Injection

SQL Injection is a common network attack technique where attackers inject malicious SQL statements into input fields or requests to manipulate the database into executing unintended operations.

Its goals typically include:

  • Stealing sensitive data
  • Bypassing authentication
  • Modifying or deleting database content
  • Executing system commands, etc.

How SQL Injection Works

  • Insufficient input validation: When a web application fails to properly validate user input, attackers can insert SQL code into input fields.

  • Concatenating SQL statements: The application backend typically concatenates user input with SQL queries to form a complete database query statement.

  • Executing malicious SQL: If the application does not properly sanitize or escape input, malicious SQL code will be executed by the database server.

  • Data leakage or destruction: Attackers can use SQL injection to query, modify, or delete data in the database, or execute system commands of the database management system.

Normal Query

When users log in to a website, they typically enter a username and password.

The following is a normal SQL query code:

SELECT * FROM users WHERE username = 'user1' AND password = 'password1';

Injection Attack

If an attacker inputs:

用户名： admin' --
密码： anything

The SQL query becomes:

SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything';

Where--is SQL's comment symbol, which ignores the password condition and directly bypasses authentication.

Common SQL Injection Types

1. Basic SQL Injection

Directly embedding malicious SQL code into user input to affect query logic.

输入用户名：admin' OR '1'='1
输入密码：anything

Executed SQL query:

SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = 'anything';

Result:

OR '1'='1' is always true, which can bypass authentication.

2. UNION Query Injection

Using UNION to merge the attacker's crafted query results with legitimate query results, thereby obtaining sensitive data.

Input:

' UNION SELECT null, username, password FROM users --

Executed SQL query:

SELECT id, name FROM products WHERE id = '' UNION SELECT null, username, password FROM users --';

Result:

Returns the username and password data from the users table as the result.

3. Error-Based SQL Injection

By intentionally triggering database errors, using error messages to infer table names, column names, or data.

Input:

' AND 1=CONVERT(int, (SELECT @@version)) --

Executed SQL query:

SELECT * FROM users WHERE username = '' AND 1=CONVERT(int, (SELECT @@version)) --';

Result:

Error messages may expose the database version or other information.

4. Blind SQL Injection

When query results cannot be obtained directly, attackers infer data step by step by judging the response of the returned page (such as boolean values or time delays).

Boolean-based blind injection, input:

' AND (SELECT 1 WHERE SUBSTRING((SELECT database()), 1, 1)='t') --

Executed SQL query:

SELECT * FROM users WHERE username = '' AND (SELECT 1 WHERE SUBSTRING((SELECT database()), 1, 1)='t') --';

Result:

Determine whether the first letter of the database name is 't' based on the returned result.

Time-based blind injection, input:

' AND IF(1=1, SLEEP(5), 0) --

Executed SQL query:

SELECT * FROM users WHERE username = '' AND IF(1=1, SLEEP(5), 0) --';

Result:

If the condition holds, the server delays the response by 5 seconds, thereby leaking information.

5. Stacked Queries Injection

Allows multiple SQL statements to be executed simultaneously.

Input:

'; DROP TABLE users; --

Executed SQL query:

SELECT * FROM users WHERE username = ''; DROP TABLE users; --';

Result:

The users table is deleted.

Some databases (such as MySQL) do not support multiple statement execution by default.

6. Stored Procedure Injection

Injecting malicious SQL using the input parameters of stored procedures.

Input:

'; EXEC xp_cmdshell('dir'); --

Executed SQL:

EXEC LoginProcedure 'username', ''; EXEC xp_cmdshell('dir'); --'

Result:

Executes system commands (such as listing directories).

7. Cookie Injection

Injecting by modifying the Cookie values stored in the browser.

Cookie: session_id=' OR '1'='1;

The server executes malicious SQL when parsing Cookies.

Dangers of SQL Injection

  • Data leakage:Attackers obtain sensitive information such as usernames, passwords, and bank card numbers from the database.
  • Privilege escalation:Attackers may gain higher access privileges by injecting commands.
  • Data tampering:Database content is modified or deleted.
  • Service interruption:Malicious SQL code may cause the database to crash, affecting system availability.
  • Executing system commands:Through database extension functions, attackers may directly manipulate the operating system.

Prevention Measures

1. Parameterized Queries and Prepared Statements

Use parameterized queries or prepared statements to separate user input from SQL statements, preventing user input from being directly parsed as SQL code.

Java code:

Example

String sql = "SELECT * FROM users WHERE username = ? AND password = ?";
PreparedStatement pstmt = connection.prepareStatement(sql);
pstmt.setString(1, username);
pstmt.setString(2, password);
ResultSet rs = pstmt.executeQuery();

Node.js (MySQL module):

Example

const query = "SELECT * FROM users WHERE username = ? AND password = ?";
connection.query(query, [username, password], (err, results) => {
    if (err) throw err;
    // Handle results
});

2. Using ORM Frameworks

Core idea: ORMs (such as Hibernate, Sequelize, etc.) automatically generate SQL queries, greatly reducing the chance of manually concatenating SQL, thereby avoiding injection.

// 使用 Sequelize
const user = await User.findOne({
    where: { username: 'admin', password: 'password123' }
});

3. Input Validation

Strictly check whether user input meets expectations and reject input that does not conform to the rules.

Use regular expressions for usernames, email addresses, etc., and only allow numeric input for numeric type fields.

Escape special characters (such as converting " to \").

const username = req.body.username.replace(/[^a-zA-Z0-9]/g, ''); // 清理特殊字符

4. Restrict Database Permissions

Assign minimal permissions to database users, allowing only necessary operations.

  • Restrict write permissions:Only allow INSERT and UPDATE operations on corresponding tables, and disallow high-risk operations such as DROP and ALTER.
  • Separate read/write permissions:Use a read-only account to access the database.

Create a read-only user:

CREATE USER 'readonly_user'@'%' IDENTIFIED BY 'secure_password';
GRANT SELECT ON database_name.* TO 'readonly_user'@'%';

5. Regular Security Testing

Regularly check for potential SQL injection vulnerabilities in the code using security scanning tools or manual testing.

We can use the open-source tool SQLMap for testing.

SQLMap is a penetration testing tool specifically designed for automated SQL injection detection and exploitation.

SQLMap is widely used in network security assessments and penetration testing to help discover and fix SQL injection vulnerabilities.

Other Extensions