SQL Injection Attacks – Complete Guide for GIAC GCIH Certification
SQL Injection Attacks: A Comprehensive Guide for GCIH Exam Preparation
Why SQL Injection Attacks Are Important
SQL Injection (SQLi) is consistently ranked among the most critical web application vulnerabilities, appearing in the OWASP Top 10 for over two decades. For GCIH candidates, understanding SQL injection is essential because:
- It remains one of the most commonly exploited attack vectors in real-world breaches
- It can lead to complete database compromise, data exfiltration, authentication bypass, and even remote code execution
- Incident handlers must be able to recognize, analyze, and respond to SQLi attacks
- It is a heavily tested topic on the GCIH exam due to its prevalence and impact
- Many high-profile breaches (e.g., Heartland Payment Systems, Sony Pictures, TalkTalk) were caused by SQL injection
What Is SQL Injection?
SQL Injection is a code injection technique that exploits vulnerabilities in the interface between a web application and its backend database. It occurs when user-supplied input is incorporated into SQL queries without proper validation, sanitization, or parameterization. An attacker can manipulate the SQL query logic by inserting malicious SQL code into input fields, URL parameters, cookies, or HTTP headers.
The fundamental problem is trust of user input. When a web application constructs SQL statements by directly concatenating user input into query strings, the boundary between data and code is broken, allowing attackers to alter the intended query behavior.
How SQL Injection Works
1. Basic Mechanism
Consider a simple login form where the application constructs this query:
SELECT * FROM users WHERE username = '[user_input]' AND password = '[password_input]'
If the application does not sanitize input, an attacker can enter:
Username: admin' --
Password: anything
The resulting query becomes:
SELECT * FROM users WHERE username = 'admin' --' AND password = 'anything'
The -- is a SQL comment that causes everything after it to be ignored. The query now only checks for the username 'admin' and completely bypasses password authentication.
Another classic technique uses the always-true condition:
Username: ' OR 1=1 --
This transforms the query into one that returns all rows because 1=1 is always true.
2. Types of SQL Injection
a) In-Band (Classic) SQL Injection
The attacker uses the same communication channel to launch the attack and retrieve results. This includes:
- Error-Based SQLi: The attacker deliberately triggers database error messages that reveal information about the database structure. Error messages may expose table names, column names, and database types.
- UNION-Based SQLi: The attacker uses the UNION SQL operator to combine the results of the original query with results from an injected query. For example: ' UNION SELECT username, password FROM users --. This requires the attacker to match the number and data types of columns in the original query.
b) Blind SQL Injection
The application does not display database errors or query results directly. The attacker infers information through indirect means:
- Boolean-Based Blind SQLi: The attacker sends queries that result in TRUE or FALSE conditions and observes differences in the application's response (e.g., different page content, different HTTP response codes). Example: ' AND 1=1 -- (returns normal page) vs. ' AND 1=2 -- (returns different page).
- Time-Based Blind SQLi: The attacker uses SQL commands that cause time delays (e.g., WAITFOR DELAY '0:0:5' in MS SQL, SLEEP(5) in MySQL, pg_sleep(5) in PostgreSQL). If the response is delayed, the injected condition was true. Example: ' IF (1=1) WAITFOR DELAY '0:0:5' --
c) Out-of-Band SQL Injection
The attacker uses alternative channels to exfiltrate data, such as DNS lookups or HTTP requests from the database server to an attacker-controlled server. This technique is used when in-band and blind techniques are not feasible. It requires certain database features to be enabled (e.g., xp_dirtree in MSSQL, UTL_HTTP in Oracle).
d) Second-Order (Stored) SQL Injection
The malicious input is first stored in the database (e.g., during user registration) and later executed when that stored data is used in a subsequent SQL query by a different function. This is particularly insidious because the injection point and the execution point are separated.
3. Common Injection Points
- URL query string parameters (GET parameters)
- Form fields (POST parameters)
- HTTP headers (Cookie, User-Agent, Referer, X-Forwarded-For)
- RESTful API parameters
- XML/JSON data in request bodies
4. What Attackers Can Achieve
- Authentication Bypass: Logging in without valid credentials
- Data Exfiltration: Extracting sensitive data from the database (usernames, passwords, credit card numbers, PII)
- Data Manipulation: Inserting, updating, or deleting records
- Privilege Escalation: Gaining administrative access within the application or database
- Remote Code Execution: Using database features like xp_cmdshell (MSSQL), LOAD_FILE() or INTO OUTFILE (MySQL) to execute operating system commands or write files
- Denial of Service: Dropping tables, corrupting data, or executing resource-intensive queries
- Pivoting: Using the compromised database server as a foothold into the internal network
5. Database-Specific Considerations
- Microsoft SQL Server: Supports stacked queries (multiple statements separated by semicolons), xp_cmdshell for OS command execution, WAITFOR DELAY for time-based attacks
- MySQL: Uses # or -- (note the trailing space) for comments, LOAD_FILE() and INTO OUTFILE for file operations, SLEEP() for time-based attacks
- Oracle: Does not support stacked queries easily, uses || for string concatenation, UTL_HTTP for out-of-band exfiltration
- PostgreSQL: Supports stacked queries, pg_sleep() for time-based attacks, COPY TO for file writing
6. Common Tools Used for SQL Injection
- sqlmap: The most popular automated SQLi exploitation tool. It can detect and exploit all types of SQLi, enumerate databases, tables, columns, dump data, access the file system, and execute OS commands. Key flags include -u (target URL), --dbs (enumerate databases), --tables, --dump, --os-shell.
- Havij: An automated SQL injection tool with a GUI interface
- Burp Suite: Used for intercepting and modifying requests to test for SQLi manually
- OWASP ZAP: Web application security scanner that can detect SQLi
7. Detection of SQL Injection Attacks
As an incident handler, you should know how to detect SQLi:
- Web Application Firewall (WAF) logs: Look for SQL keywords in request parameters (SELECT, UNION, INSERT, DROP, --, /*, ', OR 1=1)
- IDS/IPS signatures: Snort/Suricata rules that detect common SQLi patterns
- Web server access logs: Look for encoded SQL characters in URLs (%27 for single quote, %20 for space, %3D for equals sign)
- Database logs: Monitor for unusual queries, errors, or query patterns
- Application error logs: Database error messages may indicate injection attempts
- Anomaly detection: Unusual spikes in database query errors or response times
8. Prevention and Mitigation
- Parameterized Queries (Prepared Statements): The most effective defense. User input is treated as data, never as executable code. The query structure is defined separately from the data values.
- Stored Procedures: When implemented correctly with parameterized inputs (not dynamic SQL within the stored procedure)
- Input Validation: Whitelist validation of user input (accept only expected characters, lengths, and formats)
- Output Encoding: Encoding data before incorporating it into queries
- Least Privilege: Database accounts used by web applications should have minimal permissions. Never use sa, root, or DBA accounts.
- Web Application Firewalls (WAFs): Can block known SQLi patterns but should not be the sole defense (can be bypassed)
- Error Handling: Never expose detailed database error messages to users. Use generic error pages.
- ORM Frameworks: Object-Relational Mapping tools generally use parameterized queries by default
- Regular Security Testing: Penetration testing and code review to identify SQLi vulnerabilities
Exam Tips: Answering Questions on SQL Injection Attacks
1. Recognize SQL Injection Syntax in Exam Scenarios
Be able to identify common SQLi payloads such as:
- ' OR 1=1 -- (authentication bypass)
- ' UNION SELECT NULL, NULL -- (UNION-based enumeration)
- '; DROP TABLE users -- (destructive injection)
- ' AND SLEEP(5) -- (time-based blind)
- ' AND 1=1 -- vs ' AND 1=2 -- (boolean-based blind)
Know that the single quote (') is the most fundamental character in SQLi testing.
2. Understand the Differences Between SQLi Types
The exam may ask you to distinguish between error-based, UNION-based, blind boolean-based, blind time-based, and out-of-band SQLi. Know the characteristics of each and when each is used.
3. Know sqlmap Fundamentals
Expect questions about sqlmap syntax and capabilities. Remember key flags: -u for URL, --dbs to enumerate databases, --tables and -D [database] to enumerate tables, --dump to extract data, --os-shell to get a command shell, --batch for non-interactive mode.
4. Understand Comment Syntax Across Databases
- -- (double dash followed by a space) works in most databases
- # works in MySQL
- /* */ works in most databases for block comments
These are used to truncate or comment out the remainder of the original query.
5. Know the Primary Defense
If asked about the BEST or MOST EFFECTIVE defense against SQL injection, the answer is almost always parameterized queries (prepared statements). Input validation is important but secondary. WAFs are useful but can be bypassed and are considered defense-in-depth, not primary protection.
6. Identify Vulnerable vs. Secure Code
If shown code samples, look for string concatenation in SQL queries (vulnerable) vs. parameterized queries with placeholders like ? or @parameter (secure). Direct string concatenation of user input into SQL = vulnerable.
7. Remember xp_cmdshell
For MSSQL-related questions, know that xp_cmdshell is a stored procedure that allows execution of operating system commands. This is a critical escalation path from SQLi to full system compromise. It is disabled by default in modern SQL Server versions but can be re-enabled.
8. Pay Attention to HTTP Encoding
In log analysis questions, remember that SQLi payloads in URLs may be URL-encoded: %27 = single quote, %20 = space, %23 = #, %2D%2D = --, %3B = semicolon. Be able to decode these to identify the attack.
9. Understand Second-Order SQLi
If a question describes a scenario where malicious data is stored first and executed later in a different context, this is second-order SQL injection. Standard input validation at the point of entry might not catch the execution point.
10. Stacked Queries
Know that stacked queries (using semicolons to execute multiple statements) work in MSSQL and PostgreSQL but generally do NOT work in MySQL through PHP's mysql_query() function (though mysqli and PDO may support them). Oracle does not easily support stacked queries.
11. UNION SELECT Column Matching
For UNION-based SQLi, the attacker must match the number of columns in the original query. This is typically done using ORDER BY incrementally (ORDER BY 1, ORDER BY 2, etc.) until an error occurs, or using UNION SELECT NULL, NULL, NULL... with increasing NULLs.
12. Read Questions Carefully
Exam questions may provide log entries, code snippets, or network captures. Look for telltale signs of SQLi: single quotes, SQL keywords in user-controlled input, database error messages in responses, and unusual characters in URL parameters. The question might ask you to identify the attack type, the appropriate response, or the best preventive measure.
13. Incident Response Context
As a GCIH candidate, remember that incident response for SQLi includes: identifying the affected systems, determining the scope of data accessed or exfiltrated, preserving evidence (logs, database audit trails), containing the vulnerability, and remediating the root cause (fixing the vulnerable code).
Summary
SQL Injection remains a foundational topic for the GCIH exam. Focus on understanding the mechanics of how SQL queries can be manipulated, the different types of SQLi attacks, common tools like sqlmap, detection methods through log analysis, and the primary defense of parameterized queries. Being comfortable with recognizing SQLi payloads in various forms (raw text, URL-encoded, within log entries) will serve you well on exam day.
