SQL Injection Attack Principles and Defense

SQL Injection Attack Principles and Defense

Description
SQL injection is an attack technique where malicious SQL code is inserted into an application's input parameters, tricking the backend database into executing unauthorized operations. It commonly occurs in web applications that do not adequately filter user input. Attackers can exploit this vulnerability to read, modify, or delete sensitive data in the database.

I. Conditions for SQL Injection

  1. Dynamic SQL Statement Concatenation: The application directly concatenates user input into SQL queries.

II. Attack Principle Analysis
Taking a login scenario as an example, a normal SQL statement is:

SELECT * FROM users WHERE username='admin' AND password='123456'

If an attacker enters ' OR 1=1 -- in the username input field, the concatenated statement becomes:

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

(-- represents a comment in SQL, causing subsequent conditions to be ignored.)
At this point, the WHERE condition is always true, allowing the attacker to log in successfully without a password.

III. SQL Injection Classification

  1. Union Query Injection: Using UNION SELECT to merge query results.
  2. Error-based Injection: Exploiting database error messages to obtain data (e.g., using the extractvalue() function).
  3. Boolean-based Blind Injection: Inferring data based on true/false states returned by the page.
  4. Time-based Blind Injection: Using functions like sleep() to determine data based on response delays.
  5. Stacked Queries: Executing multiple SQL statements (requires database support).

IV. Manual Injection Steps Demonstration
Using union query injection as an example:

  1. Detect Injection Points: Add a single quote ' after a parameter and observe if a database error occurs.
  2. Determine the Number of Fields: Use ORDER BY 4, gradually increasing the number until an error identifies the column count.
  3. Identify Display Positions: Use UNION SELECT 1,2,3 to observe the numeric positions displayed on the page.
  4. Obtain Database Information: Replace display positions with functions like database(), version(), etc.
  5. Extract Table Data: Query information_schema.tables to obtain table names, then further query field content.

V. Automation Tool Assistance
Using Sqlmap for automated detection:

  1. Basic Detection: sqlmap -u "http://example.com?id=1"
  2. Obtain Database Names: sqlmap -u "url" --dbs
  3. Extract Table Data: sqlmap -u "url" -D dbname -T users --dump

VI. Defense Solutions

  1. Prepared Statements (Preferred): 
    PreparedStatement stmt = conn.prepareStatement(
    "SELECT * FROM users WHERE username=? AND password=?"
);
stmt.setString(1, username); // Parameterized queries avoid concatenation
  2. Input Validation:
    • Whitelist filtering (e.g., email format validation).
    • Escaping special characters (e.g., MySQL's mysql_real_escape_string()).
  3. Principle of Least Privilege: Assign database accounts only necessary permissions; prohibit using root accounts.
  4. Web Application Firewall (WAF): Deploy rules to filter common injection patterns.
  5. Error Message Handling: Use custom error pages to avoid exposing database structure.

VII. Advanced Protection Measures

  1. Use ORM frameworks (e.g., Hibernate) to automatically handle parameterized queries.
  2. Regularly conduct code security audits and penetration testing.
  3. Encrypt sensitive data storage (e.g., salted hashing for passwords).
  4. Set up database operation logs to monitor abnormal query behavior.

By understanding the complete attack chain of SQL injection, developers can establish a multi-layered defense system from code writing, database configuration, operation monitoring, and more.