Study guide
12.2 SQL Injection (SQLi) Foundations & In-Band Techniques
CREST Practitioner Security Analyst CPSA
Track your progress as you read
Finished sections get a check mark.
1 Chapter 1: Soft Skills, Legal Framework & Assessment Management
1.1 The Penetration Testing Engagement Lifecycle & Scoping 1.2 Legal Framework: Computer Misuse Act & Compliance 1.3 Understanding, Explaining & Managing Technical Risk 1.4 Record Keeping, Interim Escalation & Reporting Standards
2 Chapter 2: Core Networking: IP Protocols & Network Architectures
2.1 IPv4 and IPv6 Addressing, Subnetting & Header Architecture 2.2 TCP and UDP Transport Protocols & Handshake Mechanics 2.3 ICMP Protocol, Network Diagnostics & Error Messages 2.4 Network Topologies, Switched Media & VLAN Segmentation 2.5 Network Media, Ethernet Standards & Physical Topologies
3 Chapter 3: Cryptography & Secure Communication Protocols
3.1 Symmetric & Asymmetric Cryptographic Algorithms 3.2 Cryptographic Hash Functions, MACs & Digital Signatures 3.3 Secure Protocols: SSL/TLS, IPsec & SSH
4 Chapter 4: Background Information Gathering & OSINT Reconnaissance
4.1 WHOIS Records, Domain Registrars & Autonomous Systems 4.2 DNS Infrastructure, Record Types & Zone Transfers 4.3 Search Engine Reconnaissance, Google Dorking & Information Leakage 4.4 Newsgroups, Mailing Lists & Public Archive Reconnaissance
5 Chapter 5: Networking Equipment & Infrastructure Protocols
5.1 Router & Switch Management: SNMP, SSH & Authentication 5.2 Layer 2 Protocols: ARP, CDP, STP, VTP & Redundancy 5.3 Core Network Services: DHCP, NTP & TFTP 5.4 Wireless Security (802.11) & VoIP Assessment 5.5 Out-of-Band Management, Console Access & Cisco Reverse Telnet
6 Chapter 6: Network Mapping, Nmap Techniques & Target Identification
6.1 Host Discovery Techniques & Ping Sweep Methodologies 6.2 Port Scanning Mechanics: TCP, UDP & Stealth Scans 6.3 OS Fingerprinting, Service Detection & Timing Templates 6.4 Firewall and IDS/IPS Evasion Techniques 6.5 Passive OS & Service Fingerprinting 6.6 Network Access Control Analysis & Firewall Rule Base Review
7 Chapter 7: Vulnerability Identification & Tool Output Analysis
7.1 Vulnerability Scanning Principles & Methodology 7.2 CVE Referencing, CVSS Scoring & Risk Prioritisation 7.3 Banner Grabbing, Service Verification & Manual Triage 7.4 Host Audit Techniques: Processes, Sockets, Patch Levels & Interesting Files
8 Chapter 8: Microsoft Windows Security Assessment & Active Directory
8.1 Active Directory Architecture & Domain Reconnaissance 8.2 Windows User & Group Enumeration via SMB & RPC 8.3 Windows Authentication: NTLM, Kerberos & Password Security 8.4 Windows Patch Management, Local Security & Common Services 8.5 Windows File System, Share & Registry Permissions 8.6 Desktop Lockdown, Citrix Breakout & Common Windows Applications
9 Chapter 9: Unix & Linux Security Assessment & Legacy Services
9.1 Linux/Unix Security Architecture, Permissions & Filesystem 9.2 Unix User Enumeration & Service Discovery 9.3 Network File System (NFS) & RPC Services Assessment 9.4 Legacy Unix Services: R-Services, X11, FTP & Modern SSH 9.5 Unix Remote Information Services: rusers, rwho, finger & systat 9.6 Unix Vulnerability Exploitation & Post-Exploitation Workflow
10 Chapter 10: Web Architectures, Technologies & Testing Methodologies
10.1 Web Server Architecture & the HTTP/HTTPS Protocol 10.2 Web Application Components, Frameworks & State Management 10.3 Web Reconnaissance, Content Discovery & Threat Modelling 10.4 Testing Authentication, Authorization & Session Management 10.5 Web Service Protocols: SOAP, REST & XML Messaging 10.6 Web APIs, Server Extensions & Client-Side Sub-Components
11 Chapter 11: Web Testing Techniques & OWASP Top Vulnerabilities
11.1 Input Validation Vulnerabilities & Parameter Manipulation 11.2 Cross-Site Scripting (XSS): Types, Impact & Defenses 11.3 Cross-Site Request Forgery (CSRF) & Client-Side Attacks 11.4 Command Injection, File Upload Vulnerabilities & API Flaws 11.5 Information Disclosure: Error Messages & Mark-up Leakage 11.6 Injection Beyond SQL: LDAP, XPath, Code & Template Injection 11.7 Source Code Review & Encoded/Cryptographic Value Analysis
12 Chapter 12: Database Security Assessment & SQL Injection
12.1 Database Architectures, Default Ports & Authentication 12.2 SQL Injection (SQLi) Foundations & In-Band Techniques 12.3 Blind SQL Injection & Database Enumeration 12.4 Database Attack Vectors, Stored Procedures & Hardening
12.2 SQL Injection (SQLi) Foundations & In-Band Techniques
Key Takeaways Explain This Topic AI
SQL Injection (CWE-89) occurs when untrusted user-supplied data is concatenated directly into dynamic SQL queries without parameterized separation, altering the query's syntactic parsing tree.
SQLi vulnerabilities are categorized into three core operational channels: In-Band (Classic), Inferential (Blind), and Out-of-Band (OOB).
Authentication bypass attacks leverage Boolean tautologies (such as ' OR '1'='1 or ' OR 1=1 --) coupled with database-specific comment terminators (-- in ANSI/MSSQL/PostgreSQL, # in MySQL, and / ... / inline).
Error-based injection forces database runtime cast or parsing exceptions (e.g., MSSQL CONVERT(int, ...), Oracle CTXSYS.DRITHSX.SN(), MySQL extractvalue()) to exfiltrate confidential data directly inside verbose error responses.
UNION-based injection requires identifying the exact column count (via ORDER BY n or UNION SELECT NULL) and ensuring type compatibility across corresponding column positions before exfiltrating data.
Last updated: September 2026
12.2 SQL Injection (SQLi) Foundations & In-Band Techniques
SQL Injection (SQLi), catalogued under CWE-89 ("Improper Neutralization of Special Elements used in an SQL Command"), remains one of the most devastating and prevalent web application vulnerabilities. For decades, it has featured prominently in the OWASP Top 10. A successful SQL injection attack enables adversaries to bypass authentication, access and manipulate sensitive database records, execute administrative operations, and in many instances, pivot to full operating system compromise.
Root Cause Analysis: The Mechanics of CWE-89
The root cause of SQL injection is the failure to separate code from untrusted data . When an application constructs SQL queries by dynamically concatenating user-supplied input directly into query strings, user input transitions from passive parameter data into active database instructions. +-----------------------------------------------------------------------------+
- | SQL INJECTION PARSER SUBVERSION |
- +-----------------------------------------------------------------------------+
- | Vulnerable Code: |
- | query = "SELECT * FROM users WHERE user = '" + userInput + "';" |
- | |
- | Legitimate Input: 'alice' |
- | SELECT * FROM users WHERE user = 'alice'; |
- | [Lexer treats 'alice' strictly as a string literal operand] |
- | |
- | Malicious Input: ' OR '1'='1 |
- | SELECT * FROM users WHERE user = '' OR '1'='1'; |
- | [Lexer encounters unmatched quote, creates OR boolean operator node, |
- | and alters the Abstract Syntax Tree (AST) execution logic] |
- +-----------------------------------------------------------------------------+
When the database engine's parser performs lexical analysis, single quotation marks ( ' ) or double quotation marks ( " ) delineate string boundaries. An unescaped quote character in the input prematurely closes the intended literal. Any subsequent characters are parsed as raw SQL operators, keywords ( OR , UNION , SELECT ), or comments, fundamentally rewriting the query's Abstract Syntax Tree (AST) before execution.
Classification of SQL Injection Channels
SQL injection vulnerabilities are classified based on the channel through which the attack payload is delivered and the method by which data is extracted: +-----------------------------------------------------------------------------+
- | TAXONOMY OF SQL INJECTION CHANNELS |
- +-----------------------------------------------------------------------------+
- | Channel Type | Data Extraction Method | Bandwidth / Speed |
- +-----------------------+-------------------------------+---------------------+
- | In-Band (Classic) | Direct HTTP Response (UNION, | High bandwidth, |
- | | Error messages, Direct records)| Immediate results |
- | Inferential (Blind) | True/False Response Changes or| Low bandwidth, |
- | | Execution Time Delays | Bit-by-bit extraction|
- | Out-of-Band (OOB) | External DNS / HTTP Callbacks | Variable bandwidth, |
- | | (e.g., DNS exfiltration) | Asynchronous |
- +-----------------------+-------------------------------+---------------------+
In-Band SQLi (Classic): The attacker uses the same channel of communication (the HTTP request and response cycle) to both launch the attack and harvest results. In-band techniques include UNION-based injection and Error-based injection.
Inferential SQLi (Blind): The application does not display query results or detailed database errors in the HTTP response. The attacker reconstructs data by sending iterative payloads and observing side effects, such as differing HTTP response bodies ( Boolean-based ) or response latency ( Time-based ).
Out-of-Band SQLi (OOB): Used when in-band responses are absent and network or server configurations prevent reliable blind timing attacks. The attacker forces the database server itself to initiate an external network request (such as a DNS lookup or HTTP request) to an attacker-controlled server, carrying data within the request payload.
In-Band Exploitation: Authentication Bypass & Comment Syntaxes
Authentication bypass is the classic entry-level demonstration of SQL injection. Web login scripts frequently validate credentials using queries structured as: SELECT * FROM accounts WHERE username = '$user' AND password = '$password';
Tautology Payloads
An attacker supplies an input designed to evaluate to a mathematical tautology (a condition that is always true): Username: admin' OR '1'='1
Password: [anything]
This renders the resulting database query as: SELECT * FROM accounts WHERE username = 'admin' OR '1'='1' AND password = '...';
Because the OR condition evaluates to TRUE , the database returns records matching the username or the tautology, causing the application to log the attacker in as the first user returned (typically the administrator account).
Database Comment Syntaxes
To prevent trailing SQL syntax (such as AND password = '...' ) from triggering syntax errors, attackers terminate the query prematurely using database-specific comment markers: +-----------------------------------------------------------------------------+
- | DATABASE COMMENT SYNTAXES |
- +-----------------------------------------------------------------------------+
- | Database Engine | Line Comment Syntax | Inline / Block Comment |
- +-----------------------+--------------------------+--------------------------+
- | Standard ANSI / MSSQL | -- (requires space/tab) | /* comment */ |
- | PostgreSQL | -- | /* comment */ |
- | Oracle | -- | /* comment */ |
- | MySQL | # or -- (space required) | /* comment */ |
- | SQLite | -- | /* comment */ |
- +-----------------------------------------------------------------------------+
Important CPSA Syntax Detail: In MySQL , the double-dash comment ( -- ) MUST be followed by at least one space or whitespace character (e.g., -- or --+ where + decodes to a space in URL encoding). A double dash without a trailing space ( --foo ) is treated as a syntax operator, not a comment. MySQL also supports the hash character ( # ) as an immediate line comment delimiter.
- Payloads utilizing comment truncation: admin'--
- admin'#
- admin'/*
- Resulting executed query: SELECT * FROM accounts WHERE username = 'admin'-- ' AND password = '...';
The entire password validation clause is commented out, authenticating the session as admin .
Error-Based SQL Injection Mechanics
When an application fails to display database records directly on the webpage but displays raw database engine error messages (verbose error handling), attackers employ Error-Based SQL Injection . The objective is to deliberately trigger a runtime exception inside a function that reflects input data, coercing the database into outputting the results of a subquery within the text of the error message. Attacker Payload ---> Subquery Evaluates: (SELECT @@version)
- |
- v
- Database forces conversion of text into incompatible type
- |
- v
- Runtime Exception: "Conversion failed when converting nvarchar value
- 'Microsoft SQL Server 2019...' to data type int."
- |
- v
- Verbose error message rendered in HTTP response to attacker
- Engine-Specific Error-Based Functions
- 1. Microsoft SQL Server (MSSQL): Type Conversion Errors
MSSQL enforces strict type conversion rules. Attempting to convert a text string into an integer ( int ) using CONVERT() or CAST() causes MSSQL to output the failed string in the error message: ' AND 1=CONVERT(int, (SELECT @@version))--
' AND 1=CAST((SELECT user_name()) AS int)--
Output Message: Conversion failed when converting the nvarchar value 'Microsoft SQL Server 2019 (RTM) - 15.0.2000.5' to data type int.
2. Oracle RDBMS: XML & Network Resolution Errors
Oracle does not leak data via simple cast errors in the same manner as MSSQL. Instead, specialized PL/SQL packages or XML functions are invoked:
CTXSYS.DRITHSX.SN() : Generates an error containing the result of an arbitrary query: ' AND 1=CTXSYS.DRITHSX.SN(user, (SELECT banner FROM v$version WHERE rownum=1))--
UTL_INADDR.GET_HOST_NAME() : When supplied with a subquery, fails name resolution and returns the resolved string in the error: ' AND 1=UTL_INADDR.GET_HOST_NAME((SELECT user FROM dual))--
3. MySQL / MariaDB: XPath Evaluation Errors
In MySQL (5.1+), the XML parsing functions extractvalue() and updatexml() evaluate XPath queries. If the XPath expression syntax is invalid, MySQL outputs the evaluated expression in the error message. Attackers prepend an invalid character (such as a tilde ~ , hex 0x7e ) to force an error: ' AND extractvalue(1, concat(0x7e, (SELECT version())))--
- ' AND updatexml(1, concat(0x7e, (SELECT user())), 1)--
- Output Message: XPATH syntax error: '~8.0.32'
- UNION-Based SQL Injection: Step-by-Step Methodology
The UNION operator in SQL combines the result sets of two or more SELECT statements into a single composite result set. In a UNION-based attack, the attacker appends their own malicious SELECT query to the original application query, extracting data directly onto the web page. Original Application Query: Attacker Appended Query:
- SELECT id, title, description UNION SELECT 1, username, password
- FROM products WHERE category = 'Books' FROM users;
Composite Result Set Returned to Web Page:
- +----+-------------------+-----------------------------------+
- | id | title | description |
- +----+-------------------+-----------------------------------+
- | 1 | SQL Guide | Comprehensive manual... |
- | 2 | Network Security | Practical assessment... |
- | 1 | administrator | $2y$12$e8YqJ2K8Z7xL9wV0N1mO3... | <-- Injected
- +----+-------------------+-----------------------------------+
- Strict Prerequisites for UNION Injection
For a UNION query to execute without database termination, it must satisfy two fundamental SQL standards:
Identical Column Count: Both the original query and the injected query must return the exact same number of columns .
Compatible Data Types: The data types of corresponding columns in both queries must be compatible (e.g., if column 2 in the original query is an integer, the second column of the injected query cannot be arbitrary text on engines that enforce strict typing).
Step 1: Determining the Column Count
To discover how many columns the original query selects, the attacker employs one of two methods:
Method A: The ORDER BY Technique
The ORDER BY clause sorts results by a specified column index (1-indexed). The attacker increments the column index until the database throws an error: ' ORDER BY 1-- (Success: at least 1 column)
- ' ORDER BY 2-- (Success: at least 2 columns)
- ' ORDER BY 3-- (Success: at least 3 columns)
- ' ORDER BY 4-- (Error: query references a non-existent column!)
If ORDER BY 3 succeeds but ORDER BY 4 triggers an error (e.g., The ORDER BY position number 4 is out of range of the number of items in the select list ), the original query selects exactly 3 columns .
Method B: The UNION SELECT NULL Technique
The attacker injects successive UNION SELECT statements populated with NULL literals. NULL is convertible to any data type, avoiding data type mismatch errors: ' UNION SELECT NULL-- (Error: column mismatch)
- ' UNION SELECT NULL, NULL-- (Error: column mismatch)
- ' UNION SELECT NULL, NULL, NULL-- (Success: column count matched!)
- Step 2: Determining Data Types & Finding Display Columns
Once the column count is established, the attacker determines which columns can display string ( varchar / text ) data and are rendered visibly on the page. The attacker tests each column position individually with a string literal: ' UNION SELECT 'a', NULL, NULL--
- ' UNION SELECT NULL, 'a', NULL--
- ' UNION SELECT NULL, NULL, 'a'--
If the string 'a' appears on the web page when injected into position 2, column 2 is confirmed as an injectable display column.
Step 3: Extracting Data from Metadata and User Tables
With injectable columns identified, the attacker queries system catalogs and operational tables to exfiltrate data. To ensure only the attacker's data is displayed (suppressing legitimate original results), the attacker makes the first query return an empty set by specifying a non-existent ID (e.g., id=-1 ): -1' UNION SELECT 1, table_name, 3 FROM information_schema.tables WHERE table_schema=database()--
- 1' UNION SELECT 1, column_name, 3 FROM information_schema.columns WHERE table_name='users'--
- 1' UNION SELECT 1, concat(username, ':', password), 3 FROM users--