Introduction

Imagine a website with a search bar. When you enter a query, the website converts your text into a database query to retrieve related information. SQL injection occurs when a malicious user tricks an application into executing unintended SQL code.

This attack can lead to data breaches, unauthorized access, and even complete loss of control of the database. This is a reminder of the special importance of safe coding practices in the world of web development. In the next line of words, we'll dig deeper into SQL injection(SQLI), its mechanism, and how to protect your application against it.

Prerequisites

Before we dive into SQLI, certain criteria should be met. You should have a solid foundation in;

SQL(Structured Query Language).
Python(Django or Flask).
JavaScript(Node.js).
DBMS(Database Management Systems).

Definition of SQLI and Overview

SQL injection (SQLi) is a security breach that allows attackers to interfere with queries an application makes against its database. This often allows attackers to view data that they normally cannot recover. This can include data belonging to other users or any other data that the application itself can access. In many instances, an attacker(Hacker) can modify or delete this data, resulting in persistent changes to the application's content or behavior.

How SQL Injection Attacks Work

There are various ways SQLI works and all will be reviewed but first let's get an overview of SQLI.

Let's consider an E-commerce site that sells gadgets. When the user clicks on the Android category, the URL gets requested:

https://unprotected-site.com/gadgets?category=Android

This prompts the application to deliver an SQL query to retrieve information about the required gadget from the assigned database:

SELECT * FROM gadgets WHERE category = 'Android' AND released = 1

Release restriction = 1 is used to hide unreleased products. The application does not implement any protection against SQL injection attacks, so an attacker could construct an attack like:

https://unprotected-site.com/gadgets?category= Android'--

Result:

SELECT * FROM gadgets WHERE category = 'Android'--' AND released = 1

What to focus on here is the double dash -- which is used to comment on SQL, and that means the rest after it, is regarded as a comment in the query. Due to this, all gadgets are released including unreleased ones.

An attacker can also cause the application to display all gadgets in any of the categories including ones not known of:

https://unprotected-site.com/gadgets?category=Android' OR+1=1--

The Result:

SELECT * FROM gadgets WHERE category = 'Android' OR 1=1--' AND released = 1

The updated query will return all items where either the category is Android, or 1 is equal to 1. Since 1=1 is always true, the query will return all items.

Classification of SQLI

We have two orders of SQLI, which are:

First-order Injection Attack:

First-order injection attacks occur when the attacker immediately receives a desired result, either through direct feedback from the application they are interacting with or through another feedback mechanism, such as email, etc.
Second Order Injection Attack:

A second-order injection attack is when an attacker creates malicious code that is injected into an application but is not immediately activated by the application. Malicious entries are introduced into the system or database by an attacker. This is used to indirectly enable SQLI to be used later. The attacker usually relies on where the input will be used next and designs his attack from there. Quadrant injection leaves the work of detection and prevention complicated. Indeed, the injection point is different from the point where the attack manifests itself.

Types of SQLI

We addressed the orders when it comes to SQLI, however, in those orders, there are various forms in which SQLI is performed. An example depicting one of those forms has already been given earlier but still, We'll be focusing on a selected few so you can get a grasp of how SQLI works.

Error-Based SQLI

Error-based SQLI is an attack that malicious users use with malicious SQL queries to receive some type of error or confirm that there is a problem with their input data.

This is usually a rule in database syntax. They can then use this information to extract information from the database, such as passwords or personal bank accounts.

Detection:

There are many types of SQL commands that an attacker can execute, and all of them are quite unpredictable.

Here are some examples: single quotes(' '), double quotes(" "), or SQL operators like AND, OR, and NOT

Example:

https://www.examples.org/index.php?item=123′

Due to the single quote added to the vulnerable site, it comes back as an error “You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the right syntax to use near ‘‘VALUE’’. This enables the attacker access to various things like:

DB used in MySQL
Error in the syntax is a double quote
The place of the error caused is at the end of the parameter, etc.

Union Based SQLI

Union-based SQL insertion involves using the UNION operator to combine the results of multiple SELECT statements to extract data from multiple tables as a single result set. For the query to work, two requirements are needed.

The vicious queries must return the same number of columns.
The column returned from the main query must be compatible with the injected query.

Union-based Injection Example:

A query that has access to credentials can be hacked using "Union Select", without the user or company knowing, this is how an attacker gets in.

'UNION SELECT username, passcode FROM users--

An attacker could send the request as a URL-encoded request if the application does not have adequate input validation. The attack can also spread via a classic injection attack using a URL:

http://ichigo.com/vehicles.php?category=Benz ‘+UNION+SELECT+username,passcode+FROM+users–

The — sign at the end of the inserted string is a comment symbol, commenting out the remainder of the original query. This query will return a table with the users' names, email addresses, and phone numbers, as well as a table with all the passwords from the user's table. An attacker can then use this information to further compromise the system.

Just as the name suggests, yes it's a blind attack. The attacker exploits web applications' vulnerability to extract classified information from the database without being able to see the data directly.

There are two types of Blind SQLI:

Boolean Based SQLI
Time-Based SQLI

Boolean Based SQLI:

In this type of SQLI attack, the attacker sends a series of SQL queries that are evaluated as true or false, depending on whether the injected code was executed successfully or not. An attacker can then use the application's response to infer information about the database by constructing complex queries to look for specific information.

Below is an example of blind SQLI using an online store that displays devices for sale. The following link shows details about device ID 8, pulled from the database.

http://www.rukiashop.local/device.php?id = 8

The query used to get the request:

SELECT columnName, columnName2 FROM table_name WHERE id = 8

The Hacker inputs certain blind SQLI format:

http://www.rukiashop.local/device.php?id=8 and 1=2

The SQL query now looks like this:

SELECT columnName2 FROM tableName WHERE ID = 8 and 1=2SELECT name, description, price FROM StoreTable WHERE ID = 8 and 1=2

From this, the result will return false with nothing being displayed. The hacker doesn't give up and then proceeds to modify it by:

http://www.rukiashop.local/item.php?id=8 and 1=1

The query looks like this:

SELECT columnName, columnName2 FROM tableName WHERE ID = 8 and 1=1SELECT

Now, due to this, the database returns TRUE, and all details of ID 8 are displayed. A strong indication of site vulnerability.

Time-Based SQLI:

In this case, the attacker performs a tedious operation on the database.

If the website does not return a response immediately, this indicates a blind SQL vulnerability. The activity that takes the most time is sleeping.

Based on the above example, an attacker would compare the web server's response time with a standard SQL query and then issue the query below:

http://www.rukiashop.local/device.php?id=8 and if(1=1, sleep(12), false)

The site is vulnerable if the response is delayed by 12 seconds.

Best Practices for SQLI Prevention

Preventing or mitigating SQLI attacks includes ensuring that no fields are vulnerable to application execution and invalid input. It is not possible to manually check each page and application on the website. Especially when frequent updates and usability are top priorities.

However, security analysts and developers recommend the following few points that ensure that your database is well protected inside the server.

Input Validation and Sanitization

Validation Input:

One way to reduce the risk of SQLI is to check the type of input you expect from the user, such as numbers, text, dates, or email addresses. You can use functions or libraries built into your programming language or framework to validate input format and length and reject any mismatched input. For example, in the Python code below;

import re

def validate_email(email):
    pattern = r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$'
    return bool(re.match(pattern, email))

def validate_regex(regex, string):
    return bool(re.match(regex, string))

The validate_email() function takes an email address as input and returns True if it is valid and False otherwise. The validate_regex() function takes a regular expression and a string as input and returns True if the string matches the regular expression and False otherwise.

Sanitization Input:

Another way to prevent SQLI is to sanitize input values, that is, remove or escape any characters that could be interpreted as SQL commands or keywords. You can use functions or libraries that encode or escape special characters, such as quotes, semicolons, hyphens, or comments. For example, in the Python code below;

import mysql.connector

cnx = mysql.connector.connect(user='username', password='password',
                              host='127.0.0.1',
                              database='database_name')
cursor = cnx.cursor()

query = ("SELECT * FROM table_name WHERE column_name = %s")

value = ('string_to_escape',)
cursor.execute(query, value)

for row in cursor:
  print(row)

cursor.close()
cnx.close()

In this example, %s is a placeholder that will be replaced with the escaped value of string_to_escape. This way, you don’t have to worry about manually escaping strings and your code is more secure against SQLI attacks.

Test your Code:

This is something that should be done regularly if possible to escape SQLI, Frameworks or tools that access SQLI attacks are highly needed. you can use these; SQLmap, Havij, or SQLninja.

Conclusion

These are a few but not all points you can use when handling SQLI. A detailed study of each attack is being conducted to classify the SQL injection attack. With the help of these categories, industry experts and developers can enhance the security of web application databases.

Safeguarding Your Web Applications Against SQL Injection Attacks

Table of contents