Home
Potential DB Threats
How DataSunrise Security protects from SQL Injections

How DataSunrise Security protects from SQL Injections

In this article, we demonstrate you how DataSunrise prevents SQL injections which is one of the most popular hacker instruments of breaching web applications. To show you what exactly happens when DataSunrise intercepts a malicious query, we perform a penetration testing with a dedicated software.

Sqlmap

Although there are a lot of various applications dedicated to perform automated vulnerability assessment, we use Sqlmap utility in our current experiment. This free, open-source program is written in Python language and easy to use due to its command-line interface.

According to the official description, SQLmap can utilize the following SQL-injection techniques for penetration tests:

Boolean-based blind

Sqlmap appends to the vulnerable parameter in the HTTP query syntactically valid SQL statement, containing a SELECT substatement or any other SQL statement used to retrieve output from the database. Then Sqlmap compares each HTTP response with the original request and gets the output of the injected statement character by character.

Time-based blind

Sqlmap appends to the vulnerable parameter syntactically valid statement, containing query which causes a delay in DBMS back-end for a certain number of seconds. Then the utility performs a comparison between original response time and time spent on the injected query response and then infers the output of the injected statement character by character.

Error based

Sqlmap appends to the vulnerable parameter in the HTTP request syntactically invalid statement string causing the database-specific error message. Then Sqlmap parses the HTTP response trying to find error messages containing injected SQL code and the subquery statement output within. This method of SQL injection works only if the DBMS is configured to show back-end DBMS error messages.

UNION query-based

Sqlmap appends to the vulnerable parameter in the HTTP request syntactically valid SQL statement starting with UNION ALL SELECT. This method is useful when the web application page uses FOR loop or similar to pass the output of the SELECT statement, so each line of the query output is printed on the page content. Sqlmap is able to exploit “partial” UNION query SQL injection vulnerabilities as well. Such vulnerabilities occur when FOR cycle is not used and only the first entry of the query output is displayed.

Stacked queries-based (piggybacking)

If the target web application supports stacked queries, Sqlmap appends to the vulnerable parameter in the HTTP query a semicolon character ( ; ) followed by the SQL statement to be injected. This technique is used to perform injection of SQL statements other than SELECT (like data definition or data manipulation statements). Potentially, this method can be used to get file system read/write access and operating system command execution depending on the DBMS back-end and the session user privileges.

As you can see, Sqlmap is a powerful tool able not only to reveal vulnerabilities but to exploit them with various SQL injection techniques. Here is more about methods of SQL injection detection.

Preparations for testing

To perform our demonstration we need some “test dummy” application to be SQL-injected, so we use a simple web server written in Python. This server is designed to output information from database defined by the parameters in the URL string. We saved the web server code in the webserv_inj.py file:

import re
import urllib.request
from http.server import BaseHTTPRequestHandler, HTTPServer
from urllib.parse import urlparse

import pyodbc

global dbconnection
global dbcursor

class InjectedServer(BaseHTTPRequestHandler):
    def do_GET(self):
        self.send_response(200)
        self.send_header("Content-type", "text/html charset=windows-1251")
        self.end_headers()
        o = urlparse(self.path)
        params = urllib.request.unquote(o.query)
        m = re.search("id=(?P[^;]+);name=(?P[^;]+)", params)
        id = m.group("id")
        name = m.group("name")
        dbconnection = pyodbc.connect(
            "Driver=PostgreSQL ODBC Driver(ANSI);Server=localhost;Port=54321;Database=test_db;Uid=postgres;Pwd=admin;UseServerSidePrepare=1")
        dbcursor = dbconnection.cursor()
        queryStr = "select * from injected_table where id = " + id + " and name = '" + name + "' order by id"
        dbcursor.execute(queryStr)
        result = dbcursor.fetchall()
        for row in result:
            self.wfile.write(bytes(str(row[0]), "utf-8"))
            self.wfile.write(bytes(str(row[1]), "utf-8"))
            self.wfile.write(bytes(str(row[2]), "utf-8"))

myServer = HTTPServer(("127.0.0.1", 6589), InjectedServer)
try:
    myServer.serve_forever()
except KeyboardInterrupt:
    pass
myServer.server_close()

We will try to breach our self-made web server and get some data from a PostgreSQL database. We used the following code to create a new database table (“injected_table”) and fill it with two entries:

CREATE TABLE injected_table
(
    id integer,
    name character varying(200),
    surname character varying(200)
)
INSERT INTO injected_table VALUES (1, Bob, Martin);
INSERT INTO injected_table VALUES (2, Denis, Morgan);

This is how the new table looks like in the PGAdmin

Then we start our server by running the following command via Windows command prompt:

python webserv_inj.py

To be sure that everything works as expected, we send the following query to web server via web browser:

http://127.0.0.1:6589/?id=2;name=Denis

As a result the web server should output “injected_table” entry with ID=2 and Name=Denis

As a result the web server output “injected_table” entry with ID=2 and Name=Denis

Testing the web server with Sqlmap

Now it’s time to check if our web server can be SQL-injected.

First we start Sqlmap testing procedure via the command prompt:

python.exe sqlmap.py -u http://127.0.0.1:6589/?id=2;name=Denis

When the testing process is finished, let’s take a look at the Sqlmap log file (it was created automatically and saved inside the “output” folder).

sqlmap identified the following injection point(s) with a total of 50 HTTP(s) requests:
---
Parameter: id (GET)
Type: boolean-based blind
Title: AND boolean-based blind - WHERE or HAVING clause
Payload: id=2;name=Denis' AND 8150=8150 AND 'lsHq'='lsHq

Type: AND/OR time-based blind
Title: PostgreSQL > 8.1 AND time-based blind
Payload: id=2;name=Denis' AND 9061=(SELECT 9061 FROM PG_SLEEP(5)) AND 'TLxE'='TLxE

Type: UNION query
Title: Generic UNION query (NULL) - 3 columns
Payload: id=2;name=Denis' UNION ALL SELECT NULL,NULL,(CHR(113)||
CHR(112)||CHR(120)||CHR(120)||CHR(113))||(CHR(88)||CHR(109)||CHR(66)||CHR(103)||CHR(114)||
CHR(69)||CHR(83)||CHR(119)||CHR(113)||CHR(98)||CHR(110)||CHR(73)||CHR(90)||CHR(83)||
CHR(90)||CHR(121)||CHR(113)||CHR(102)||CHR(85)||CHR(117)||CHR(107)||CHR(72)||CHR(78)||
CHR(101)||CHR(79)||CHR(90)||CHR(112)||CHR(120)||CHR(74)||CHR(106)||CHR(114)||CHR(105)||
CHR(85)||CHR(84)||CHR(71)||CHR(104)||CHR(71)||CHR(89)||CHR(75)||CHR(111))||(CHR(113)||
CHR(22)||CHR(107)||CHR(122)||CHR(113))-- -
---
back-end DBMS: PostgreSQL

As the log shows, Sqlmap successfully identified DBMS version (PostgreSQL) and determined the “ID” parameter as vulnerable to SQL injection. Also Sqlmap proposed us three variants of SQL statements which can be used to perform SQL injection (“Payload” strings).

DataSunrise Security rule settings (SQL injections section)

And now we will check how effectively DataSunrise can protect the vulnerable web server (and associated PostgreSQL database) against SQL injections.

We created an “SQL injection” Security rule (refer to DataSunrise user guide for details) and deleted the files Sqlmap has created during the first test.

Now we run Sqlmap testing procedure again (as described above) and when the testing ends we check the utility log (note that Sqlmap was not able to create a log file so we are using its command line log).

According to the log, Sqlmap defined that all the tested parameters are not vulnerable to SQL injection, including the “ID” parameter which was marked earlier as injectable.

Conclusion

Our experiment proves that DataSunrise firewall is highly effective at preventing SQL injections. Nethertheless, for the best possible protection against the most complex threats it is necessary to take extended protective measures as the following:

Following industry’s best practices to develop SQL injection-resistant web applications
Performing simulated hacker attacks on your databases and web applications to reveal possible vulnerabilities (so called “penetration tests”)
Continual database auditing for data breach and data leakage detection
Following the developer’s recommendations and guidelines on safe use of databases and associated software.

DataSunrise supports all major databases and data warehouses such as Oracle, Exadata, IBM DB2, IBM Netezza, MySQL, MariaDB, Greenplum, Amazon Aurora, Amazon Redshift, Microsoft SQL Server, Azure SQL, Teradata and more. You are welcome to download a free trial if would like to install on your premises. In case you are a cloud user and run your database on Amazon AWS or Microsoft Azure you can get it from AWS market place or Azure market place.