SQL injection (SQLi) is one of the best-known vulnerabilities in web applications. Yet it remains a relevant threat in modern environments. Although frameworks, ORMs and data access libraries have significantly reduced the number of manually constructed SQL queries, they have not eliminated the risk altogether.
It is precisely because this threat remains underestimated that it is worth revisiting it in detail. In this article, we explore the fundamental principles of SQL injection as well as exploitation techniques. We also detail the different types of SQL injection, provide concrete examples of exploitation and outline the prevention strategies to be implemented.
Comprehensive Guide to SQL Injection (SQLi)
What is SQL injection?
How does SQL injection work?
SQL, which stands for Structured Query Language, is the language used to interact with many relational database management systems. MySQL, MariaDB, PostgreSQL, Microsoft SQL Server, Oracle Database and SQLite all implement SQL, with differences in syntax, functions and architecture.
Applications rely on these databases to store user accounts, business information, content, orders, access rights, billing data, configurations and even the secrets required for them to function.
When a user views a resource, performs a search, generates a report or logs in, the backend frequently needs to read or modify this data.
An example of a legitimate query might be:
SELECT id, username, role
FROM users
WHERE username = 'john';The structure of the query is defined by the developer. The value ‘john’ represents a piece of data.
An SQL injection occurs when this separation breaks down and user-controlled input can alter the syntax sent to the SQL engine.
The key point, therefore, is not so much whether the input contains special characters, but rather whether the application enforces a robust boundary between the SQL code and the data. An apostrophe is perfectly legitimate in a piece of data such as ‘O’Connor’. It only becomes dangerous when it is introduced into a query via concatenation, which allows it to alter the syntactic context.
How a query becomes vulnerable to an SQL injection
Let’s consider a simplified PHP implementation:
$username = $_GET['username'];
$query = "SELECT id, username, role
FROM users
WHERE username = '" . $username . "'";With a normal value, such as ‘john’, the resulting query matches the developer’s intention:
SELECT id, username, role
FROM users
WHERE username = 'john';However, the DBMS is unaware of the origin of the various characters in this string. It does not know that some come from the code and others from an HTTP request.
If the user is able to close the string and enter a new expression, the engine will parse the whole thing as a single SQL statement.
For example, an input such as:
' OR '1'='1can then lead to:
SELECT id, username, role
FROM users
WHERE username = '' OR '1'='1';The added condition is true for all rows. The behaviour of the query has therefore been altered by a piece of data that should have remained a simple value.
The same vulnerability may exist without an apostrophe. In a numerical context:
$query = "SELECT * FROM products WHERE id = " . $_GET['id'];the input:
42 OR 1=1can directly produce:
SELECT * FROM products WHERE id = 42 OR 1=1;These two examples illustrate why an SQLi payload is never one-size-fits-all. The auditor must first understand the exact context of the input.
What are the potential consequences of an SQLi attack?
The impact of an SQL injection varies considerably. In a relatively limited scenario, the attacker may alter a filter to display additional data. In other situations, they may bypass authentication logic, read data belonging to other accounts, or access information that was never intended to be exposed by the application.
Where the vulnerable query permits write operations, SQLi may enable data to be modified or deleted. Excessive privileges can further widen the impact: access to administrative tables, DBMS management functions, reading or writing files, invoking privileged procedures, or even interacting with the operating system.
It is, however, important to reason carefully. The existence of an SQLi does not automatically mean that all these actions are feasible. An application account strictly limited to read-only access on a few views does not present the same risks as an account with administrative privileges. A DBMS hosted in a managed service does not expose the same primitives as an older instance installed with a permissive configuration on the same server as the web application.
The role of a penetration test is precisely to establish this distinction between theoretical impact and demonstrable impact.
SQL injection and NoSQL injection: what are the differences?
SQL injections target relational systems and their query languages. Non-relational databases, such as MongoDB, may be vulnerable to other forms of injection when user input alters the structure of a filter, a query document or an expression interpreted by the backend.
A NoSQL injection therefore does not necessarily involve single quotes, UNION operators or SQL comments. The syntax depends on the technology and API used. Nevertheless, both types share a fundamental cause: untrusted data is allowed to influence the logic of an operation intended for the data engine.
This conceptual similarity should not lead to them being confused. The detection methods, exploitation techniques, syntax and specific preventative measures differ.
Where to Look for SQL Injections?
An SQLi vulnerability can occur wherever user-controlled data influences an SQL operation. Limiting your focus to login forms or ‘id’ parameters leaves a significant portion of the attack surface unaddressed.
URL parameters and forms
Query string parameters are easy to change and provide obvious test cases:
GET /product?id=42 HTTP/1.1
Host: application.exampleBut data from a POST form, a path such as /users/42, a multipart form or hidden parameters is just as interesting. The name of the parameter may provide a functional clue, but never proves that it reaches a database.
The auditor therefore observes the behaviour.
- Does changing a value alter the number of results?
- Does a non-existent value produce a different response?
- Does the field appear to control a filter, a search, a sort or an object selection?
These clues help to prioritise the tests.
Searches, filters, sorting and dashboards
Search engines often generate complex dynamic queries. A page may combine a keyword, a category, a price range, a status, a sort order and pagination. Each option may be implemented differently.
An example query might be:
SELECT id, name, price
FROM products
WHERE name LIKE '%$search%'
AND category = '$category'
ORDER BY $sort
LIMIT $limit;‘search’ and ‘category’ are values. ‘sort’ is a structural element. ‘limit’ may be a numerical expression, the configurability of which depends on the driver and the chosen structure. A single feature may therefore have several distinct injection contexts.
Reporting interfaces, CSV or PDF export functions and administrative dashboards warrant particular attention. They frequently combine multiple filters, aggregations and sorts, sometimes implemented using raw SQL for reasons of performance or flexibility.
APIs and JSON or XML bodies
An API that receives:
{
"category": "laptop",
"minPrice": 500,
"sort": "price"
}is not protected simply by using JSON. If the backend concatenates ‘category’, ‘minPrice’ or ‘sort’ into a query, the same risk applies.
APIs can even increase the attack surface because they expose rich objects: nested filters, arrays of identifiers, multiple sort parameters, advanced searches, optional fields and batch operations. The transport format is just one layer. What matters is the transformation carried out between the structured input and the query sent to the DBMS.
The same reasoning applies to XML. Checks for SQL injection must be applied at the time the query is constructed, not just when the document is parsed.
GraphQL and resolvers
GraphQL is not a form of SQL, and a GraphQL query is not automatically converted into an SQL query. The risk arises when the resolver uses client-controlled arguments to construct an operation on a relational database.
Let’s consider, for example:
query {
products(category: "laptop") {
id
name
price
}
}The resolver receives category. If it calls an ORM correctly, the value will generally be parameterised. If it constructs a raw query to handle a complex filter, it may reintroduce an SQLi vulnerability.
The chain to be analysed is therefore as follows: the GraphQL value enters a resolver, may pass through several services or helpers, and then reaches an SQL sink. It is at this sink, and in the way the query is constructed, that the vulnerability lies.
HTTP headers and cookies
Some applications log or process the User-Agent, Referer, X-Forwarded-For, business headers or tracking identifiers in a database. A backend system may also use a cookie to retrieve a session, a preference or a campaign.
These values can be manipulated by the client. An attacker can send their own headers and modify their cookies, even when the browser would normally generate them automatically. They must therefore be treated as untrusted input.
Header-based SQLi attacks are particularly common in logging or analytics components that have been developed quickly and are mistakenly regarded as internal. They can be difficult to detect because the HTTP entry point and the SQL processing are far apart in the architecture.
Stored data and second-order scenarios
Data may be entered securely into the database but then reused in a dangerous way. For example, the registration process uses:
INSERT INTO companies(name) VALUES (?);The value is set correctly. Later, a reporting tool retrieves ‘name’ and constructs:
$sql = "SELECT * FROM invoices
WHERE company_name = '" . $storedName . "'";The database is treated here as a trusted source, even though it contains data that originally came from the user. The vulnerability does not exist at the time of storage; it arises when the data is reused.
This scenario illustrates why data security is not an absolute property. Data that is secure in one context can become dangerous in another if interpreted differently.
How to Detect an SQL Injection?
Testing for SQLi effectively does not involve sending a long list of random payloads. The aim is to gradually build up a hypothesis about the query, obtain a reproducible signal, and then choose the most appropriate technique.
Mapping controllable inputs
The first step is to identify the data likely to reach the backend: URL parameters, path segments, form bodies, JSON, XML, cookies, headers, stored fields, filters, sorting criteria and pagination parameters.
In a white-box context, this mapping can be supplemented by an analysis of SQL sources and sinks within the code.
The auditor does not necessarily test all inputs with the same level of rigour. Parameters that drive a search, object selection, export or reporting query are often more likely to interact with a data layer than values that are purely displayed on the client side.
Establish a baseline response
Before modifying a request, it is useful to assess its normal behaviour: HTTP status code, approximate size, characteristic content, number of results, response time, redirects and key JSON elements.
Let’s consider:
GET /products?id=42 HTTP/1.1
Host: application.exampleThe baseline response then allows us to compare very similar variations. Without a baseline, a difference observed following a payload may be mistakenly attributed to an SQL injection, when in fact it stems from a cache, missing data or normal business behaviour.
Search for a syntax error
In a supposed textual context, a character such as ' may cause an error. In a numerical context, an unexpected operator may have a different effect. The aim of this step is not to draw immediate conclusions, but to determine whether the syntax of the input appears to be recognised by an SQL interpreter.
A change in the HTTP status code, an exception, a different number of results or a blank page may all be clues. A stack trace mentioning an SQL driver is particularly informative, but a properly configured application should not expose this level of detail.
The hypothesis must then be confirmed with tests that produce predictable results.
Compare true and false conditions
In a digital context, a simple pair can be:
42 AND 1=1then:
42 AND 1=2If the first response consistently replicates the normal behaviour and the second produces a different result, this strongly suggests an injection. The same principle can be adapted to a string context whilst adhering to the syntax of the presumed query.
This method is particularly useful because it does not necessarily depend on a visible SQL error. It demonstrates that a condition introduced by the user directly influences the result of the query.
Search for a time series channel
If no difference in content is apparent, the auditor may look for a controlled delay. MySQL and MariaDB, for example, have SLEEP(), PostgreSQL has pg_sleep(), and SQL Server has WAITFOR DELAY.
A slow query does not constitute proof. Response time depends on the network, server load, third-party services and many other factors. The test must therefore compare several control queries with several queries that are expected to cause a delay significantly greater than normal background noise.
The next step is to make this delay conditional. If the server only waits when the SQL expression is true, the time becomes an oracle that can be used to extract information.
Understanding errors correctly
Errors can be useful in a number of ways. A syntax error suggests a context. An ORDER BY error can help determine the number of columns. A conversion error may reveal that a column expects a numeric type.
A distinction must be made between these errors and genuine error-based extraction. In the latter case, the auditor forces the DBMS to generate an error whose message contains data from a subquery. The error then becomes a channel for data exfiltration rather than merely an indication of the structure.
The availability of this technique depends heavily on the engine, its version and the way in which the application propagates or masks exceptions.
Confirm the context and the DBMS
Once the signal has been confirmed, the auditor seeks to identify the underlying engine. Version functions, error messages, comment syntax, timing functions and behaviour in response to certain constructs all provide clues.
Fingerprinting must avoid jumping to conclusions. For example, @@version exists in several engines. A single positive test may therefore not be sufficient to distinguish MySQL from SQL Server. Ideally, several primitives should be cross-referenced, or an identifiable version string should be retrieved where the output channel permits.
Example of an SQLi Analysis
Let’s consider a catalogue application that exposes:
GET /products?category=laptops HTTP/1.1
Host: shop.example
Cookie: session=...The page returns a list of products belonging to the ‘laptops’ category. The auditor knows neither the SQL query, nor the number of columns, nor the DBMS. They only have the HTTP behaviour to go on.
Identification of the context and confirmation of the SQL injection vulnerability
The first step is to establish the reference response: status 200, response length, number of products displayed and a few static strings.
The auditor then replaces the category with a non-existent value to verify the expected behaviour when the query returns no rows.
Finally, they test a single apostrophe:
laptops'Let us assume that, on this occasion, the application returns a generic error with a 500 status code. This indication is interesting but insufficient.
The auditor therefore seeks to construct two queries that differ only in terms of a logical condition. In the hypothetical context of a string, they adapt the values so that the syntax remains valid and compare a true condition with a false condition.
The true condition returns the normal list, whilst the false condition returns an empty list. After several repetitions, the behaviour remains consistent. The vulnerability is now much more credible: a user-injected SQL expression alters the result of the query.
This step is essential. It prevents a one-off error from being misinterpreted as an SQLi and provides a method that may still prove useful if direct techniques fail.
Choosing the exploitation channel
The auditor then seeks to determine whether the result of the query is displayed directly. As the page displays products, a UNION attack is a natural candidate.
He progressively tests ORDER BY 1, ORDER BY 2 and subsequent positions. The first three positions are accepted, whilst the fourth triggers an error response. The original query therefore appears to return three columns.
He confirms this hypothesis with a query containing three NULL values. The query is accepted. By then replacing each NULL with a test string, he observes that the second and third positions accept text, but that only the second appears in the HTML.
At this stage, several facts have been established: the point is injectable, the injection is likely to be within a string, the query returns three columns, and the second column constitutes a usable display channel.
The next step is to identify the DBMS. A test using a specific function returns a PostgreSQL version string. The auditor can then use PostgreSQL syntax for the subsequent steps rather than blindly trying MySQL, SQL Server or Oracle functions.
They retrieve the name of the current database and a few table names visible via the metadata. A business table containing user accounts appears. The auditor does not need to extract the full contents: rather, they are seeking to determine which columns exist and whether sensitive information is actually accessible to the application account.
Suppose they identify id, email, password_hash and role. The presence of password_hash already indicates that the SQL account used by the catalogue can access data that likely exceeds the functional requirements of a public product page. A test record or a value belonging to a demonstration account may be sufficient to prove unauthorised access.
The test therefore reveals two distinct issues: the SQLi itself and insufficient separation of privileges, since the account used by the catalogue component can read an authentication table.
If the UNION query had failed because the results were not displayed, the auditor could have reverted to the previously confirmed Boolean method. If the content had been exactly the same, a timing channel could have been investigated.
The methodology therefore involves prioritising the most direct and least resource-intensive channel, then switching to blind techniques only when necessary.
SQLi impact assessment
Once the ability to read sensitive data has been demonstrated, it would be technically possible to continue the enumeration. This does not mean that it is appropriate to do so. A penetration test must produce sufficient evidence to enable the risk to be assessed and remedied, not to maximise the amount of data exfiltrated.
Instead, the auditor examines the account’s privileges. Can it write data? Can it create objects? Does it have privileged functions?
In our scenario, let us assume that the role is restricted to SELECT operations across several schemas. The impact remains high because sensitive data is accessible, but the likelihood of database modification or direct RCE is significantly reduced.
This conclusion is more useful than a generic statement such as ‘an SQLi can lead to RCE’. The report may explain that the vulnerability allows arbitrary extraction of data readable by the account, that this account has excessive scope relative to the public function being tested, but that no possibility of writing or system execution has been identified within the authorised context.
The scenario also provides a more precise remedy. The category value must be configured in the query, but the account used by the catalogue must also be reviewed to ensure it can only access the tables or views that are strictly necessary. Correcting the code removes the root cause; the principle of least privilege reduces the impact of any potential future vulnerability.
During the retest, the auditor will replay both the true and false conditions, verify that strings containing SQL characters are treated as plain values, and then confirm that the account or data layer no longer unnecessarily exposes authentication objects.
This practical example summarises the reasoning to be applied throughout this guide: verify before exploiting, identify the context before selecting payloads, use the simplest channel, limit access to data to what is strictly necessary, and assess the impact based on the capabilities actually demonstrated.
Types and Techniques for Exploiting SQL Injections
The categories of SQLi primarily describe the channel used to obtain information or maximise the impact. A single vulnerability can sometimes be exploited in several ways.
When results are displayed directly, an in-band technique is generally more effective than a blind injection. When the application returns nothing useful, a Boolean, temporal or out-of-band technique becomes necessary.
In-band SQL injection
An in-band SQL injection occurs when the same application channel is used both to send the payload and to retrieve useful data or clues.
The two most common types are UNION-based and error-based injections.
UNION-based SQL injection
The UNION operator allows you to combine the results of several SELECT queries. A valid query such as:
SELECT name, price
FROM products;can be combined with:
SELECT username, email
FROM users;provided that both results have the same number of columns and that the corresponding data types are compatible.
In an SQLi attack, the aim is to use this mechanism to make the result of a manipulated query be processed as if it were part of the original result.
Suppose that a category feature constructs:
SELECT name, description, price
FROM products
WHERE category = '$category';The auditor cannot begin effectively with an arbitrary UNION SELECT statement. They must first determine the number of columns, which positions are suitable for text, and which are actually displayed in the response.
Determining the number of columns using ORDER BY
In many DBMSs, ORDER BY 1, ORDER BY 2 or ORDER BY 3 are used to specify the columns in the result set by their position. The auditor can gradually increase this value:
' ORDER BY 1 --
' ORDER BY 2 --
' ORDER BY 3 --
' ORDER BY 4 --When the position exceeds the number of columns, the engine may generate an error. The application does not necessarily display the SQL message; a different generic response, a 500 status code or the absence of a result may be sufficient to identify the threshold.
If `ORDER BY 3` remains valid but `ORDER BY 4` consistently results in different behaviour, the original query likely returns three columns.
This technique must be adapted to the context. The comment used after the payload depends, in particular, on the DBMS and the expected syntax. In MySQL, the sequence -- must be followed by a valid space character; the # character is also supported as an end-of-line comment.
Finding out the number of columns using UNION SELECT NULL
Another method is to increase the number of NULL values until a compatible query is obtained:
UNION SELECT NULLthen:
UNION SELECT NULL,NULLand so on.
NULL is useful because it can be converted to many SQL data types. It therefore maximises the chances of the query succeeding once the correct number of columns has been reached, without yet knowing the type of each column.
An error with two values followed by a successful result with three suggests that the original result contains three columns.
Identifying columns that support text
The number of columns is not sufficient. If the auditor wishes to retrieve a username, a database version or a metadata string, they must find a column that accepts text data.
For a three-column result, they can try the following in turn:
UNION SELECT 'test',NULL,NULL
UNION SELECT NULL,'test',NULL
UNION SELECT NULL,NULL,'test'A conversion error may indicate that the position being tested corresponds to an integer, a date or another incompatible type. If the query is successful, the column is likely compatible with a string.
This step must be distinguished from a second question: is the column actually visible? An application may retrieve four columns but then use only two of them in its HTML template or JSON response. A column may therefore be technically compatible without constituting an exploitable exfiltration channel.
Identifying the columns that are actually displayed
A simple technique involves injecting a recognisable string into each compatible column and checking where it reappears in the response. If HACKAGORA_TEST_47 appears in a product title, a JSON field or an HTML attribute, that position provides an output channel.
Sometimes, several legitimate results may obscure the row added by the UNION, or the application may only process the first record. In this case, the auditor may seek to alter the original part of the query so that only the results from the second selection are retained. This adjustment depends on the context and must remain non-destructive.
Fingerprinting using UNION
Once a visible text column has been identified, the channel can be used to identify the DBMS. Depending on the engine, expressions such as the following are relevant:
SELECT @@version;for MySQL or SQL Server,SELECT version();for PostgreSQL,- or querying
v$versionfor Oracle.
In a UNION context, these expressions must be inserted into the correct number of columns and in a compatible position. Obtaining a version string then allows functions, comments, concatenation and enumeration techniques to be adapted accordingly.
Listing metadata using UNION
Many databases expose metadata via information_schema. Where permissions allow, a query such as:
SELECT table_name
FROM information_schema.tables;can reveal tables. The auditor can then examine the columns of an object of interest:
SELECT column_name
FROM information_schema.columns
WHERE table_name = 'users';The aim is not necessarily to extract all the data. In penetration testing, a few representative data points are often sufficient to demonstrate that an SQL account can access a sensitive table that should never have been exposed via the vulnerable feature.
Concatenate several values into a single column
An interface may display only a single text column. The auditor can then concatenate several fields to retrieve them together. MySQL offers the following options:
CONCAT(username, ':', email)PostgreSQL and Oracle commonly use:
username || ':' || emailSQL Server can use the + operator or other concatenation functions, depending on the data types and version.
This difference demonstrates once again why the fingerprinting stage is not merely a cosmetic exercise. It enables the selection of methods that are compatible with the actual engine.
Error-based SQL Injection
The term ‘error-based’ is often used to refer to any exploit that takes advantage of an SQL error. It is useful to distinguish between errors that help to understand the query and those that are actually used to extract data.
An error caused by:
ORDER BY 10may reveal that the query contains fewer than ten columns. An impossible conversion may indicate the expected data type. A syntax error message may reveal the database engine. This information facilitates analysis, but the data being sought is not yet contained within the error message.
In an error-based SQLi in the strictest sense, the attacker forces the DBMS to perform an invalid operation, the error message for which incorporates a value from a subquery. The conceptual mechanism is as follows: the database calculates a piece of information; this information is used in a context that deliberately triggers an exception; and the error message is then returned to the application.
An error of the type:
Cannot convert value 'production_database' ...can therefore reveal `production_database`, even though the feature never displays the SQL result directly.
The specific techniques vary greatly between databases and versions. They may also be rendered ineffective by error handling that replaces SQL exceptions with a generic message. This measure significantly reduces the amount of information available, but obviously does not fix the SQLi itself.
Blind SQL Injection
A blind injection occurs when the query is manipulable but the application returns neither the SQL result nor, in most cases, a directly exploitable error message. The attacker must therefore deduce the information by observing a stable side effect.
The two most common channels are differences in content or behaviour, and response time.
Boolean-based blind SQL injection
Let us assume that an endpoint returns ‘Product available’ when its query finds a record, and ‘Product not found’ otherwise.
An input:
42 AND 1=1retains the normal result, whereas:
42 AND 1=2makes the product disappear. The application has just provided a clue: it indirectly answers an SQL query with ‘true’ or ‘false’.
The next step is to replace 1=1 with a condition based on actual data. In MySQL, for example:
LENGTH(DATABASE()) = 10allows you to check whether the name of the current database contains ten characters. Once the length has been determined, an expression such as:
SUBSTRING(DATABASE(),1,1) = 'a'allows you to test the first position.
A naive approach would try all possible characters in turn. This quickly becomes computationally expensive. An efficient blind extraction relies instead on binary search. Rather than checking whether the character is a, then b, then c, the auditor compares its numerical value against a threshold. Each check eliminates approximately half the possibilities.
With a space of 128 values, seven queries are theoretically sufficient to isolate a value, since 2^7 = 128. Automated tools use this type of optimisation to significantly reduce the number of queries required.
In a real-world application, true and false are not always represented by explicit messages. The signal may be a JSON field, a difference in the number of results, a redirect, a change in length of a few bytes, or the presence of an HTML element. The auditor must verify that this signal is reproducible and that it does not depend on volatile data, a cache or any other business mechanism.
Time-based blind SQL injection
When the content of the response remains the same, the auditor can use time as a indicator. The primitives are engine-specific:
SLEEP(5)in MySQL or MariaDB,pg_sleep(5)in PostgreSQL,- and
WAITFOR DELAY '0:0:5';in SQL Server.
Inducing a fixed delay demonstrates, above all, that a timing function is achievable. To extract data, this delay must be made conditional. The logic becomes: if an expression is true, wait; otherwise, respond normally.
The auditor can thus test the length of a string, and then each of its characters. Binary search remains relevant: the delay is simply the medium used to represent the true or false bit.
The main challenge is noise. A server may respond in 200 ms and then 900 ms without any SQLi being involved. Tests must therefore use a delay significantly greater than the usual variance and be repeated. The auditor compares several test queries with several queries intended to trigger the delay.
A time-based exploit can also generate a significant load, particularly if each query imposes a delay of several seconds. In a production environment, confirming that the oracle allows internal data to be tested is often sufficient. Extracting long strings simply because the technique allows it may be unnecessarily intrusive.
Out-of-Band SQL injection
Some applications execute the query asynchronously or neutralise any exploitable differences in content and timing. It may nevertheless be possible to trick the DBMS into initiating a network interaction with an infrastructure controlled by the auditor.
An out-of-band SQLi attack then uses another channel – typically DNS or HTTP – to confirm the exploit or transmit data. Conceptually, the database may be tricked into performing a name resolution where the name contains an extracted value:
production-db.audit.exampleThe test DNS server receives the request and allows the data to be observed without it passing through the application’s HTTP response.
This technique depends on several prerequisites. The DBMS must have a network primitive that can be used in the context of the injection, the SQL account must have the necessary permissions, and the environment must allow outbound traffic. Strict segmentation and egress filtering can therefore block this channel even if the SQLi vulnerability itself exists.
This distinction is important for impact analysis: a primitive documented in an engine does not mean that it is accessible in the audited environment.
Stacked Queries
A UNION injection adds a second SELECT statement to the result of the original query. Stacked queries, also known as batched or piggy-backed queries, aim to complete the existing statement and then execute a new, independent statement.
Take, for example:
SELECT * FROM products WHERE id = 42;
UPDATE audit_marker SET checked = 1 WHERE id = 99999;The second statement is no longer limited to the form of a SELECT. Depending on the privileges, it could be an UPDATE, a stored procedure call or another statement supported by the engine.
Support does not depend solely on the DBMS. The driver or application API may prohibit multiple statements within a single call, or require an explicit option. Two applications using the same underlying technology may therefore offer different capabilities.
In penetration testing, verification should prioritise an operation with no lasting business impact. A controlled delay or a harmless query is preferable to modifying data when the mere support for stacked queries is sufficient to demonstrate the expansion of the exploitation scope.
Second-Order SQL injection
Second-order injection occurs when malicious data is stored without causing any immediate effect, and is then reused later in a vulnerable query.
The point of entry and the point of execution may be very far apart. A company name is stored today via a parameterised query. An internal export retrieves it the next day and concatenates it into an SQL filter. The first component is not vulnerable; the second is.
This scenario is particularly difficult to detect using a conventional dynamic scanner. The response to the registration request is perfectly normal and no errors are displayed. It is necessary to understand the data’s lifecycle, identify the features that re-read it, and test the subsequent sinks.
Administration interfaces, reports, exports, scheduled tasks, batch processing and synchronisations are common locations for this type of data reuse. Developers sometimes mistakenly assume that database data is implicitly reliable in these contexts.
The security rule must be phrased differently: data must be used securely in every context in which it is interpreted, regardless of its immediate source.
Advanced Techniques for Exploiting SQL Injection Vulnerabilities
Beyond the scenario described above, certain techniques make it possible to extend access well beyond the initial point of entry. It all depends, then, on the privileges of the database and the underlying infrastructure.
Reading and writing files
Some DBMSs provide functions for interacting with the file system. If the privileges granted to the database are too high, an attacker could exploit this to read sensitive server files or write arbitrary content to the disk.
MySQL, for example, provides the LOAD_FILE() function:
SELECT LOAD_FILE('/etc/passwd');It is also possible to write files using:
SELECT 'malicious content' INTO OUTFILE '/var/www/html/shell.php';SQL Server, for its part, exposes stored procedures that are equally dangerous for interacting with the operating system. This ability to interact with the file system significantly increases the severity of an SQL injection, as it can grant access to configuration files, application secrets, SSH keys, source code, backups or server scripts.
In the most vulnerable environments, the ability to write to files can lead to the server being completely compromised.
Remote code execution (RCE) via SQL injection
In some cases, an SQL injection can go beyond simply compromising the database and lead to remote code execution (RCE) on the underlying operating system. This generally occurs when:
- the database is running with excessive privileges;
- dangerous stored procedures are enabled;
- file writing is permitted;
- functions for executing external commands are accessible.
SQL Server, for example, exposes the stored procedure xp_cmdshell:
EXEC xp_cmdshell 'whoami';Using an SQL injection, an attacker can thus plant a web shell, write malicious scripts to an accessible directory, trigger system functions, or execute external binaries.
A successful RCE can then be used to completely compromise the server, establish persistence, move laterally across the internal network, deploy ransomware, or exfiltrate data on a large scale. Whilst current hardening practices generally disable these dangerous functions by default, poorly secured configurations and legacy environments continue to expose this type of high-risk attack vector.
Bypassing WAFs
Many organisations deploy WAFs (Web Application Firewalls) to detect and block malicious SQL injection payloads. However, attackers regularly attempt to bypass them using obfuscation techniques: payload encoding, case manipulation, inline comments, whitespace, fragmentation of SQL syntax, or the use of alternative operators and functions.
Example of a reformatted payload designed to evade signature-based detection:
UN/**/ION SEL/**/ECTURL encoding, or even double encoding, is also used to conceal malicious input before it reaches the backend. As many WAFs rely heavily on pattern matching and signature-based detection, an incorrectly configured rule set may fail to detect a heavily obfuscated payload. A WAF remains a valuable defence mechanism, but should never be regarded as a substitute for secure request handling via parameterised queries.
Privilege escalation
The severity of an SQL injection depends largely on the privileges assigned to the compromised database account. Excessive permissions significantly widen the attack surface and allow for deeper compromise. An attacker typically seeks to escalate their privileges to access restricted tables, execute administrative functions, interact with the file system, compromise other services, or move laterally within the infrastructure.
An application running under a database account with excessive privileges may thus inadvertently expose administrative stored procedures, database management functions, capabilities to interact with the operating system, or sensitive internal schemas. In some environments, credentials that have been extracted can even be used to compromise other systems connected to the same infrastructure.
Inadequate separation of privileges remains one of the most significant factors determining the severity of an SQL injection exploit. Even a relatively simple vulnerability can become critical if the underlying database account has excessive permissions.
How to Prevent SQL Injection Attacks?
Preventing an SQL injection attack relies on a combination of best development practices, secure database configuration, restricted privileges and regular security testing. As the vulnerability stems from a lack of separation between user data and executable SQL statements, an effective mitigation strategy must cover every stage of interaction with the database.
Modern frameworks and libraries offer more secure mechanisms, but poorly secured implementations, legacy code and hand-crafted queries continue to leave applications vulnerable.
Use prepared statements
Prepared statements remain the most effective defence against SQL injection. Unlike dynamically constructed queries, they separate the query structure from the data provided by the user: the application sends the query template and the parameters separately to the database engine, which then consistently treats the input as data and never as a command.
Example of an insecure construction:
$query = "SELECT * FROM users WHERE id = " . $_GET['id'];Secure version with parameter binding:
$stmt = $pdo->prepare("SELECT * FROM users WHERE id = ?");
$stmt->execute([$id]);As the engine strictly separates the SQL logic from the supplied value, an injected payload can no longer alter the structure of the query. Prepared statements are widely supported by modern database languages, frameworks and drivers. This is a basic defence against SQL injection.
Implement input validation
Input validation reduces the attack surface by filtering out unexpected or malformed data before it reaches the application logic or the database. An application should validate: the data type, the expected format, the length, the permitted characters and the accepted values.
A numeric parameter should only accept numeric values; an email field should enforce a valid email format. A whitelist approach (defining what is valid) is generally more secure than a blacklist approach (attempting to block what is dangerous).
Be careful, however: input validation must never be regarded as sufficient protection on its own. An attacker can often bypass weak filtering or blacklist-based protection. It must therefore complement, rather than replace, prepared queries and secure query handling.
Avoid insecure dynamic query construction
Unsecure dynamic construction remains one of the main causes of SQL injection: it occurs whenever a developer generates an SQL query by concatenating strings, using direct interpolation, or relying on untrusted variables. A typical example:
$query = "SELECT * FROM products WHERE category = '" . $category . "'";Here, a malicious input can directly alter the structure and logic of the generated query. Things to avoid: direct string concatenation, raw SQL interpolation, dynamically assembled SQL fragments, insecure query builders, or the execution of user-supplied SQL.
If dynamic generation is still necessary, prioritise parameterised queries, strict validation checks and secure query-building mechanisms.
Secure stored procedures and database-side logic
When implemented correctly, stored procedures can reduce the risk of injection by encapsulating business logic within predefined routines, thereby limiting direct interaction between user input and dynamically generated SQL.
However, they are not inherently secure: a procedure that constructs dynamic SQL itself remains vulnerable. This is particularly the case with statements such as EXEC(@query) or sp_executesql, if the attacker’s input is incorporated without parameterisation.
A secure stored procedure should: use parameter binding, avoid executing unsecured dynamic SQL, restrict unnecessary privileges, and limit the administrative functionality exposed. Database-side logic must follow the same security principles as application code.
Apply the principle of least privilege
The impact of an SQL injection depends largely on the privileges of the compromised database account. An application should never connect using an administrator account unless absolutely necessary: the account used must have only the permissions that are strictly necessary.
An application that requires only read access should not have file system privileges, administrative permissions, schema modification rights, or the ability to execute commands.
Restricting privileges significantly reduces the potential impact of a successful exploit: even if the query is compromised, limited permissions can prevent privilege escalation, access to the file system, database modification, remote code execution, or lateral movement within the infrastructure. This is one of the most effective ways to contain an exploit.
Disable dangerous database features
Many DBMSs provide advanced features capable of interacting with the operating system or the file system. If these remain enabled unnecessarily, an attacker may exploit them during an attack: command execution procedures, file read/write functions, external network communication, and extended administrative procedures.
SQL Server, for example, exposes xp_cmdshell, whilst MySQL exposes LOAD_FILE(). If the application does not require these functions, they must be disabled or strictly restricted. Reducing the attack surface at the database level limits post-exploitation opportunities and significantly mitigates the severity of an SQL injection.
Implement secure error handling
Error messages that are too detailed provide the attacker with valuable information during an exploit: database type and version, table names, query structure, server file paths, and internal application logic.
An application should never return a raw database error to the end user. Instead, display a generic message on the front end, and keep detailed logs reserved for internal monitoring. An error such as ‘Unknown column “username” in “where clause”’ should never be displayed on the client side.
Poor error handling directly facilitates error-based attacks by exposing backend information that an attacker can exploit to refine their payloads and map the database. Secure logging and monitoring remain essential for detecting suspicious behaviour without exposing sensitive information.
Carry out continuous security testing
Preventing SQL injection cannot rely solely on best practices implemented at the start of a project. Regular security testing is essential to detect new vulnerabilities, risky code changes and changes to the attack surface.
The most effective approaches combine: manual security audits, penetration testing, secure code reviews and automated vulnerability scans.
These tests must also cover APIs, mobile backends, administration interfaces, third-party integrations and legacy components. Integrating these tests into CI/CD pipelines enables vulnerabilities to be detected earlier in the development cycle and reduces the risk of an exploitable flaw reaching production.
As SQL injection remains one of the most dangerous and persistent web vulnerabilities, continuous security validation remains a cornerstone of any modern application security programme.
Conclusion
SQL injection (SQLi) remains one of the most critical and widespread web vulnerabilities. It still features in the OWASP Top 10 today and is listed as CWE-89, proof of its persistence.
It always stems from the same flaw: the lack of separation between the structure of a query and the data provided by the user. Whether the exploit involves a visible error, Boolean behaviour, a response delay or an external network channel, the underlying mechanism remains the same. This is why prepared queries, combined with a strict principle of least privilege, remain the most reliable defence, whether ORM is used or not.
It is important to bear in mind, however, that an application may appear to be protected by a modern framework whilst remaining vulnerable as soon as a developer introduces a fragment of unparameterised dynamic SQL. Consequently, prevention cannot rely solely on the choice of tools: it requires constant vigilance, regular testing and, above all, a clear understanding—such as that developed in this guide—of the actual mechanism behind each exploitation technique.
