YouTip

SQL Injection (SQL Injection) is a common network attack method where attackers inject malicious SQL statements into input fields or requests to manipulate databases into performing 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 web applications fail to properly validate user input, attackers can insert SQL code into input fields. * **Concatenating SQL Statements**: Application backends typically concatenate user input with SQL queries to form complete database query statements. * **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 exploit 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. Here is a normal SQL query code: SELECT * FROM users WHERE username = 'user1' AND password = 'password1'; ### Injection Attack If an attacker inputs: Username: admin' -- Password: anything The SQL query becomes: SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'; Where -- is the SQL comment symbol, ignoring the password condition and directly bypassing authentication. * * * ## Common SQL Injection Types ### 1. Basic SQL Injection Directly embedding malicious SQL code into user input to affect query logic. Input username: admin' OR '1'='1 Input password: anything Executed SQL query: SELECT * FROM users WHERE username = 'admin' OR '1'='1' AND password = 'anything'; Result: OR '1'='1' is always true, allowing authentication bypass. ### 2. UNION Query Injection Using UNION to merge attacker-constructed query results with legitimate query results to obtain 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 username and password data from the users table as results. ### 3. Error-Based SQL Injection Deliberately triggering database errors and 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 database version or other information. ### 4. Blind Injection When query results cannot be directly obtained, attackers gradually infer data by judging page responses (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: Judging 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 is true, the server will delay response by 5 seconds, thereby leaking information. ### 5. Stacked Query Injection Allowing multiple SQL statements to execute 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 multi-statement execution by default. ### 6. Stored Procedure Injection Exploiting input parameters of stored procedures to inject malicious SQL. Input: '; EXEC xp_cmdshell('dir'); -- Executed SQL: EXEC LoginProcedure 'username', ''; EXEC xp_cmdshell('dir'); --' Result: Executing system commands (such as listing directories). ### 7. Cookie Injection Exploiting modified Cookie values stored in the browser for injection. Cookie: session_id=' OR '1'='1; The server executes malicious SQL when parsing the Cookie. * * * ## 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 through injection commands. * **Data Tampering:** Database content is modified or deleted. * **Service Interruption:** Malicious SQL code may cause database crashes, 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 concept: ORM (such as Hibernate, Sequelize, etc.) automatically generates SQL queries, greatly reducing opportunities for manual SQL concatenation, thereby avoiding injection. // Using Sequelizeconst user = await User.findOne({ where: { username: 'admin', password: 'password123' }}); ### 3. Input Validation Strictly check whether user input meets expectations, rejecting input that does not conform to rules. Use regular expressions for usernames, emails, 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, ''); // Clean special characters ### 4. Limiting Database Permissions Assign minimum permissions to database users, only allowing necessary operations. * **Limit Write Permissions:** Only allow users who insert and update to operate corresponding tables, disallowing high-risk operations such as DROP and ALTER. * **Separate Read and Write Permissions:** Use read-only accounts to access databases. Creating 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 code for potential SQL injection vulnerabilities through 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. * SQLMap official website: [https://sqlmap.org/](https://sqlmap.org/) * SQLMap open-source repository: [https://github.com/sqlmapproject/sqlmap](https://github.com/sqlmapproject/sqlmap)