Today, I would like to share some tutorials regarding the SQL Injection toward SQLite database. I believe you guys has been familiar with the SQL Injection because it is a common mistake when user input is not validated.
Lets take a look at below python script that utilize SQLite
def submitmessage(): message = request.form.get("message", '') if len(message) > 140: return "message too long" # insert new message in DB try: query_db("insert into messages values ('%s')" % message) except sqlite3.Error as e: return str(e) return "OK"
We can see from the above code the user input is checked only for its length then the input is directly executed. For sure the code above is vulnerable to SQL Injection. But wait before we do the real injection, what SQL injection technique is suitable to attack. Below is simple explanation of the technique used
Error-based SQLi is an in-band SQL Injection technique that relies on error messages thrown by the database server to obtain information about the structure of the database. In some cases, error-based SQL injection alone is enough for an attacker to enumerate an entire database. While errors are very useful during the development phase of a web application, they should be disabled on a live site, or logged to a file with restricted access instead
Union-based SQLi is an in-band SQL injection technique that leverages the UNION SQL operator to combine the results of two or more SELECT statements into a single result which is then returned as part of the HTTP response.
Inferential SQLi (Blind SQLi)
Inferential SQL Injection, unlike in-band SQLi, may take longer for an attacker to exploit, however, it is just as dangerous as any other form of SQL Injection. In an inferential SQLi attack, no data is actually transferred via the web application and the attacker would not be able to see the result of an attack in-band (which is why such attacks are commonly referred to as “blind SQL Injection attacks”). Instead, an attacker is able to reconstruct the database structure by sending payloads, observing the web application’s response and the resulting behavior of the database server.
Boolean-based (content-based) Blind SQLi
Boolean-based SQL Injection is an inferential SQL Injection technique that relies on sending an SQL query to the database which forces the application to return a different result depending on whether the query returns a TRUE or FALSE
Depending on the result, the content within the HTTP response will change, or remain the same. This allows an attacker to infer if the payload used returned true or false, even though no data from the database is returned. This attack is typically slow (especially on large databases) since an attacker would need to enumerate a database, character by character.
Time-based Blind SQLi
Time-based SQL Injection is an inferential SQL Injection technique that relies on sending an SQL query to the database which forces the database to wait for a specified amount of time (in seconds) before responding. The response time will indicate to the attacker whether the result of the query is TRUE or FALSE
Depending on the result, an HTTP response will be returned with a delay, or returned immediately. This allows an attacker to infer if the payload used returned true or false, even though no data from the database is returned. This attack is typically slow (especially on large databases) since an attacker would need to enumerate a database character by character.
Out-of-band SQL Injection is not very common, mostly because it depends on features being enabled on the database server being used by the web application. Out-of-band SQL Injection occurs when an attacker is unable to use the same channel to launch the attack and gather results.
Out-of-band techniques, offer an attacker an alternative to inferential time-based techniques, especially if the server responses are not very stable (making an inferential time-based attack unreliable).
Base on the above simple explanation the python code above is severe to SQL Injection attack type error based. Why ? because we can see that any error on the execution will be catch by exception and returned to the user. so whenever we input the message with ended by ‘ and catch error will send the information back to the user
Lets begin to create the SQL injection query. Based on the python code shown that the line that vulnerable to the injection is code below
query_db("insert into messages values ('%s')" % message)
Based on the vulnerable code above then we can formulate the payload as below in order to keep the SQL Syntax is free of error
SQL Injection Exploit
query_db(“insert into messages values (‘ ‘ or payload ) — – ‘)” % message)
Sample of payload 1=1. Do forget to encode the payload before sending it to the server
Error Based Attack to Enumerate Database
SQLite support CASE expression that we can use to do enumeration. We can use the below CASE function for our enumeration. The basic idea is whenever we do correct guessing then it will return OK and whenever our guessing is failed then it will return SQLite error
CASE WHEN [condition] THEN [expression_1] ELSE [expression_2] END
‘ and case when (select 1 from users where username=’admin’) then 1 elseload_extension(1) end)– –
with the above statement, we are trying to check if username = admin is exist in the table. If it is exist then it will return OK
if we query username that does not exist it will return as follow “not authorized” becuase our query select 1 from users where username=’NONEXISTUSER’ does not return any result and then it will execute expression_2 = load_extension(1) which is not authorized by privilege
Based on the burpsuite above, we can send them to intruder and do enumeration
add injection position on this below section
add the payload. You can use wordlist or add it by yourself
to make your life easier, You can also add Grep – Match in the option tab. You can set flag whenever the response contain “not authorized” text
Now you can be very easy to know that admin and guest are exist in the users table
Enumerate Tables Name
We can also fine tunes the payload that we inject to find out the tables in the database
' and case when (select 1 from sqlite_master where type='table' and name ='users') then 1 else load_extension(1) end)-- -
We can use the burp intruder to enumerate with the below sample. Place your payload position on the hightlighted
I will use the wordlist from https://github.com/tennc/fuzzdb/blob/master/dict/BURP-PayLoad/GetTABLES/common-tables.txt to become my payload
Set the flagging to enable you become easier to find the enumeration result
The result can be seen below that we have users and massages tables in the database
With the above situation, Basically we can extend our attack in orther to extract username and password from the users table. But I will not use Burp intruder to work with but I will use python code because it will be more effective