Introduction

Web security is a very crucial aspect of a software development cycle. It is the lock and key of the online world, and it is so unfortunate that many developers do not prioritize it. This is common among entry-level developers. The eagerness to build, deploy and show their work to the world prevents them from taking their time to implement security solutions in their software.

There have been numerous cases of cyber theft, data-breaches, etc. on websites. These cybercriminals look for vulnerable websites to attack. Some of these attacks include:

• SQL injection (SQLi) attack.

• Ransomware attack.

• Phishing.

• Botnet operations.

These attacks do not only affect the owners of the website, but it also extends to the end-users of the website. Hence, the need to tackle this scourge and build more secured websites.

One of the oldest and common attacks to websites is SQL injection (SQLi). Therefore, this article answers the following questions on SQLi:

• What is SQLi?

• How does SQLi work (with practical examples)?

• What are the types of SQLi?

• What are the dangers of SQLi?

• How to tackle SQLi?

Pre-requisites

• Structured Query Language (SQL): To follow along with this article, you must have a basic knowledge of SQL, common SQL commands and SQL implementation.

• HTML: You must understand HTML and how the HTML form works.

• Database server: You must understand databases, database servers and how they operate.

What is SQL injection?

SQLi is a type of web attack that injects malicious SQL code into a website's database server via a get/post request or any other communication channel, in order to take control of, and manipulate a database storage or operating system. This kind of attack is common in relational databases like MySQL, Oracle, etc.

How does SQL injection operate on websites?

The actors behind these attacks intentionally submit malicious SQL statements via a POST or GET request to the database server of an app, thereby triggering its execution as a normal SQL query. This query can delete, edit or bypass an authentication process and allow unauthorized access to user's credentials.

Examine the case studies below to better understand SQLi attacks:

Example 1

An ecommerce web app has a login form where users enter their email and password. A user is expected to submit his/her correct credentials. These credentials are sent to the backend as a post request and added to the SQL query to be executed by the database server as seen below:

email = request.POST.get("email")
password = request.POST.get("password")

database.execute(f"SELECT * FROM users WHERE email={email} AND password={password}")

NOTE: While the code above is written in python, it can also be implemented in other programming languages by making minor changes. The SQL query remains the same and the user inputs are directly concatenated with it.

This kind of website is prone to SQLi attacks. A hacker can enter malicious SQL statements as inputs to be executed in the database server.

An example is shown below:

password = "'password' OR 1=1"
email = 'myemail@gmail.com'

Here, the user entered an email and a malicious SQL query as password. The database server executes it as an SQL query like this:

database.execute("SELECT * FROM users WHERE email='myemail@gmail.com' AND password='password' OR 1=1")

This way the attacker has bypassed authorization by creating two conditional statements.

The conditional statements include:

1. The first and original statement which authorizes users with correct email and password.

2. The second and malicious statement inserted by the attacker, that is 1=1, which will always return True.

Regardless of the validity of the email or password, since the injected SQL statement is True, the query is executed even if the first condition fails.

Therefore, the SQL query reveals all database records. The attacker now has access to customers' private details and administrator privileges.

Example 2

The SQLi below is launched on an online e-bookstore. The attacker is a user who also has products on the website. Instead of submitting his ID to access his profile, he injected a malicious SQL statement that reveals the website's digital products (both paid and free).

ID = "rilwan OR 1=1"
database.execute(f"SELECT name, user, file FROM products WHERE ID={ID}")

This is executed as:

"SELECT name, user, file FROM products WHERE ID='rilwan' OR 1=1

SQLi attack live example

There are various demo web apps that allow developers to test SQLi and how it works live. A very simple one is that of Acunetix. It is easy to use and clearly depicts how SQLi runs on less secure apps. You can see how to use it here.

Forms of SQLi attacks

A. in-band SQLi

This is a type of SQLi where the attacker employs the same communication channel which the application uses to communicate with its database server, to launch an SQLi attack. This includes input fields or URLs that connect with the web server. This is the most common and easiest type of SQLi.

It is of two types: Error-based SQLi and Union-Based SQLi.

1. Error-based SQLi

In this type of SQLi, the attacker intentionally sends a get or post request that triggers an error in the backend. If the display of error messages is not disabled, or the website is in debug mode, the attacker can use the information in the error message to launch an attack. The information could include: the type of database used, table names, column names, etc.

2. Union-based SQLi

This type of SQLi combines two SQL queries through the UNION statement. The queries include the original query and the malicious query injected by the attacker. It works just the way a UNION statement works in SQL. Instead of sending a valid input, the attacker enters an SQL query that contains a UNION statement. For example:

product_name = 'UNION SELECT username, password FROM buyers'
database.execute(f"SELECT p_name, p_description FROM products WHERE p_name LIKE %{product_name}")

It is executed as:

database.execute(f"SELECT p_name, p_description FROM products WHERE p_name LIKE '%' UNION SELECT username, password FROM buyers")

B. Blind SQLi

In Blind SQLi, the attacker analyses and compares the responses of the web app to its requests. It then uses this information to plan attacks. This kind of SQLi does not reveal the data contained in the database directly to the attacker. It is time-consuming and the result of the attack is not obvious to the attacker.

It is of two types: Boolean-based blind SQLi and Time-based Blind SQLi.

1. Boolean-based blind SQLi

This technique relies on true or false conditions to infer information. For example, the attacker injects conditional SQL queries such as: WHERE "a"="a", that interprets to either True or False. The response of the application such as redirects, a different error message, etc. helps the attacker to confirm the success of his malicious SQL query.

2. Time-based blind SQLi

This technique uses commands and functions such as: WAITFOR in Microsoft SQL Server, to cause delay in an application's response. Since the attacker does not get to confirm the success of his attack directly, he uses SQL commands to create time lapses. If there is a specific delay in the response, it confirms to the attacker that the injection was successful.

C. Out-of-bound SQLi

This kind of SQLi uses other advanced measures to attack an application. They do not rely on the server's response to launch an attack. It utilizes external channels such as: DNS or HTTPS requests to transfer information.

This type of SQLi transfers the information of a victims' database server to their own controlled server. They do this by triggering actions such as DNS requests, etc. You can read more about this here.

SQL injection risks

The motive behind most SQLi attacks is to gain access to the data stored in a database. Data such as: passwords, credit/debit card details, etc. are their major targets. In some cases, SQLi attacks are done to destroy or manipulate database storage. The following is a list of the common effects and dangers of SQLi attacks:

1. Data Manipulation and Data Loss:

Most SQLi attacks manipulate the data in a database by deleting, editing or modifying them. This often leads to data loss.

1. Reputation Damage/Distrust:

A successfully launched SQLi attack exposes private credentials of every user of the website to the attacker. Credentials like credit/debit card details, can be used for fraud, etc. resulting in loss of customer trust for the victim organization. This can damage their reputation forever.

1. Unauthorized access:

SQLi enables hackers to bypass authentication and gain complete access to sensitive data stored in the database. Sensitive data such as: financial records, user credentials or intellectual property.

1. Violation of Regulatory Compliance:

Successful data breaches caused by SQLi can lead to legal repercussions, fines and penalties on the organization affected by the attack. This happens in cases whereby the attack was caused by negligence in implementing proper security measures.

1. Application Disruption:

Some SQLi attacks can lead to downtime or service unavailability in an application. They disrupt the normal functioning of the application. An example is the Time-based Blind SQLi

How to prevent SQL injection?

In most cases, the effects of SQLi are irreversible and can have a negative impact on a business. It is necessary to employ available means to prevent this attack. The common ways to prevent SQLi includes:

1. Parameterized queries:

Rather than concatenating the user input directly with your SQL query, passing it as a parameter will reduce SQLi attacks. When malicious user inputs are added as parameters, it prevents the database server from running it directly as an SQL query. Instead, it treats it as an ordinary data rather than an executable code. Below is an example of a parameterized query, as opposed to direct concatenation:

username = request.POST.get("username")
database.execute("SELECT * FROM users WHERE username = %s", [username])

If there are more parameters, add them to the list accordingly. These functions are made available by frameworks to prevent SQLi attacks.

1. Escaping user inputs:

You need to escape user inputs by removing extra characters like double quotes, semi-colons, etc. so they don’t get interpreted as SQL queries. Specific functions to achieve this differ from one language or framework to another.

1. Validating user inputs:

It is necessary to implement methods to validate user inputs before they are being executed. This can include checking their data types or verifying them against a list of expected inputs. Depending on your programming language, there are functions and frameworks that can help with this process.

1. Avoid dynamic SQL statements:

Avoid SQL queries that are determined by the user's input. Embrace the use of static, pre-defined queries.

1. Running regular scans:

Other methods to prevent SQL injection involve the use of third-party solutions such as: Acunetix. They run automated scans on your software and fix vulnerabilities on your website. They also scan through potential SQLi attacks that can be caused by external libraries.

Conclusion

SQL injection attack is very dangerous and could have a severe effect on the database server and a business at large. It should be prevented at all costs. Every organization should prioritize web security in the development cycle of their software.

"A stitch in time saves nine". It is better to guide against web attacks including SQLi in the development stage, than to suffer the loss of not doing so at a later time in the future.

References

Exploiting SQL injections by Acunetix

What is SQL injection and how to prevent it by Acunetix