Today, we will be covering SQL Injection. Our goal for today is

• Learn the methodology behind SQL Injection
• How to carry out SQL Injection Attack?
• Network Perspective
• How to detect a SQL Injection Attack using snort?

SQL injection can be known as SQLi attack.

Methodology:

A SQL injection attack consists of the insertion or “injection” of a SQL query via the input data from the client to the application.

A successful SQL injection can read sensitive data from the database, modify database data (Insert/Update/Delete), execute administration operations on the database (such as shutdown the DBMS), recover the content of a given file present on the DBMS file system and in some cases issue commands to the operating system.

Threat Modeling

• SQL injection attacks allow attackers to spoof identity, tamper with existing data, cause repudiation issues such as voiding transactions or changing balances, allow the complete disclosure of all data on the system, destroy the data or make it otherwise unavailable, and become administrators of the database server.
• SQL Injection is very common with PHP and ASP applications due to the prevalence of older functional interfaces. Due to the nature of programmatic interfaces available, J2EE and ASP.NET applications are less likely to have easily exploited SQL injections.
• The attacker’s skill and imagination limit the severity of SQL Injection attacks and, to a lesser extent, defense in depth countermeasures, such as low privilege connections to the database server.

In general, consider SQL Injection a high impact severity.

How to carry out SQL Injection Attack?

We are given a UserID prompt and submit button.

Objective

There are five users in the database, with IDs from 1 to 5—your mission… to steal their passwords via SQLi.

Let’s look at the source code:

https://github.com/tryhardnguyen/blog_code_storage/blob/main/SQL_Injection/DVWA_SQL_CODE.php

We can see that in this line: $id = $_REQUEST[ 'id' ]; This is where user input will be stored, and the following query will be run.

$query  = “SELECT first_name, last_name FROM users WHERE user_id = ‘$id’;”;

Let’s replace $id with a 1, so we will have the following query

$query  = “SELECT first_name, last_name FROM users WHERE user_id = ‘1’;”;

The query will retrieve only the person with a user_id of 1. But what we can do to run an extra command is do the following:

1

1' or 1 = '1

This is what it would look like in the query.

1

SELECT first_name, last_name FROM users where user_id = '1'' or 1 = '1

What it does is it says return me 1, OR you can return everything.

You get the following:

Side note: you can check for SQL vulnerabilities by inputting a quote. If a database has an sql vulnerability, it will throw an error message, or an error msg will not appear, which leads to a sql injection (blind).

An error msg for MySQL could be something like this: 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 ’’’’ LIMIT 0,1’ at line 1, or it could be like a redirection to an empty page.

Another query we can do is:

’ UNION SELECT user, password FROM users#

This retrieves the user and password from the user’s database and (#) comments the rest of the line.

This is the screenshot of the user and the user’s password hash.

Medium:

We are given the following:

This is the source code we’re provided: https://github.com/tryhardnguyen/blog_code_storage/blob/main/SQL_Injection/DVWA_SQL_CODE_2.php

The most important line is:

$query  = "SELECT first_name, last_name FROM users WHERE user_id = $id;";

We can see that variable id is not covered in quotes anymore. So, we can execute a SQL attack using the following: 1 or 1=1 UNION SELECT user, password from users#

So what we’re going do is inspect the element of 1

Then Click Submit.

As you can see, the injection work.

High

This is what we’re given.

This is our source code:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55

<?php

if( isset( $_SESSION [ 'id' ] ) ) {
    // Get input
    $id = $_SESSION[ 'id' ];

    switch ($_DVWA['SQLI_DB']) {
        case MYSQL:
            // Check database
            $query  = "SELECT first_name, last_name FROM users WHERE user_id = '$id' LIMIT 1;";
            $result = mysqli_query($GLOBALS["___mysqli_ston"], $query ) or 
            die( '<pre>Something went wrong.</pre>' );

            // Get results
            while( $row = mysqli_fetch_assoc( $result ) ) {
                // Get values
                $first = $row["first_name"];
                $last  = $row["last_name"];

                // Feedback for end user
                echo "<pre>ID: {$id}<br />First name: {$first}<br />Surname: {$last}</pre>";
            }

            ((is_null($___mysqli_res = mysqli_close($GLOBALS["___mysqli_ston"]))) 
            ? false : $___mysqli_res);        
            break;
        case SQLITE:
            global $sqlite_db_connection;

            $query  = "SELECT first_name, last_name FROM users WHERE user_id = '$id' LIMIT 1;";
            #print $query;
            try {
                $results = $sqlite_db_connection->query($query);
            } catch (Exception $e) {
                echo 'Caught exception: ' . $e->getMessage();
                exit();
            }

            if ($results) {
                while ($row = $results->fetchArray()) {
                    // Get values
                    $first = $row["first_name"];
                    $last  = $row["last_name"];

                    // Feedback for end user
                    echo "<pre>ID: {$id}<br />First name: {$first}<br />Surname: {$last}</pre>";
                }
            } else {
                echo "Error in fetch ".$sqlite_db->lastErrorMsg();
            }
            break;
    }
}

?> 

As we can see, the query starts using quotes again.

$query  = “SELECT first_name, last_name FROM users WHERE user_id = ‘$id’ LIMIT 1;”;

To exploit this, we will be using the following:

1

1' UNION SELECT user, password from users#

Network Perspective

Let’s capture the SQL injection via pfSense.

Here is a packet capture of the SQL Injection:

This is what it looks like when we first open it.

To clean up a bit, we can filter it using HTTP.

we see two SQL injection attempts.

If we were to expand the Hypertext Transfer Protocol -> Expand Request URI -> Request URI Query -> We can see the SQL injection attempt that the adversary made:id=%27+or+1+%3D+%271

Although it’s in URL encoding, we can decode using cyber chef.

There you go. Now we can read what it says.

Let’s take a look at another example.

Let’s decode it and see what would happen:

There you go. Now we can read what it says.

How to detect a SQL Injection Attack using snort?

One approach to detecting SQL injection is detecting common keywords that are used in the attack, such as:

1. SELECT, FROM
2. '
3. --
5. OR 1=1

Let’s edit our snort rules

1

sudo nano /etc/snort/rules/local.rules

These are the rule that I wrote for SQL injection:

1
2
3
4
5
6
7
8

alert tcp any any -> 172.20.25.16 80 (content: "GET"; http_method; content:"SELECT"; 
nocase; msg: "SQL Injection Detected";  sid: 1000001; rev: 1;)
alert tcp any any -> 172.20.25.16 80 (content: "GET"; http_method; content:"FROM";   
nocase; msg: "SQL Injection Detected";  sid: 1000002; rev: 1;)
alert tcp any any -> 172.20.25.16 80 (content: "GET"; http_method; content:"OR";     
nocase; msg: "SQL Injection Detected";  sid: 1000003; rev: 1;)
alert tcp any any -> 172.20.25.16 80 (content: "GET"; http_method; content:"1|3D|";  
msg: "SQL Injection Detected"; sid: 1000004; rev: 1;)

The last rule is checking for 1 = (which is in hex).

These rules are pretty simple: if any request has SELECT, FROM, OR. Alert me!

Let’s test out our rule

1

sudo snort -c /etc/snort/snort.conf -A console

Let’s try the other SQL injection query

' UNION SELECT user, password from users#

Snort was able to detect the SQL injection!