SQL injection and ways to prevent it

June 17, 2020

The Internet as we know it now is a shadow of its former self. Back in the day when the web was not created, we requested a document and received it as it is. But now after the introduction of HTML, Javascript, and SQL the popularity of dynamic websites or web applications as they are called, has exploded.

This has introduced a lot of factors that can be exploited to gain illegal access to websites or databases. These types of exploits (Vulnerabilities) or security mistakes are some of the following:

SQL injections
Cross-site scripting(XSS)
Insecure Direct Object References
Broken Authentication

The above list contains the attacks which were most common in 2019.

I will be talking about SQL injections in this blog. :

SQL Injections:-

An SQL injection is a result of failure to filter untrusted inputs, i.e., USER INPUTS.
In this type of attack, an attacker would simply insert(inject) malicious code within your input.
There are mainly 3 types of SQL injections:

Error-based SQL Injection
UNION-based SQL Injection
Blind SQL Injection

1. Error-based SQL Injection:

This is the most common type of vulnerability in SQL injection. It is quite easy to exploit.

It relies on feeding the server unexpected commands or invalid inputs, which will force the server to send some error.

From this error, the attacker can determine the structure, version, operating system or even return full query results.

In the below example, This webpage returns if the name entered exists in the table or not.

The SQL query used by the application is the following:

"SELECT * FROM customer WHERE first_name='". $_GET['name'] ."'";

The server accepts input from the user and returns if that name exists or not. This means that the attacker can input invalid inputs to modify the backend query.

For example, we can add a single quote at the end of the input like mary’. After accepting this input the backend query will look like the following.

"SELECT * FROM customer WHERE first_name=’ mary’ ’ “;  (note the extra quote here)

This means that the query is invalid so the server will return the following error.

The attacker uses this type of injection because of the fact that injected malicious input will cause the server to output the results into an error message. This message can reveal many details about the underlying DB. For example, the following injected payload

MARY' AND (SELECT 0 FROM (SELECT count(*), CONCAT((SELECT @@version), 0x23, FLOOR(RAND(0)*2)) AS x FROM information_schema.columns GROUP BY x) y) - - '

Will result in the following error.

Duplicate entry '5.7.29-0ubuntu0.16.04.1#1' for key ''

This error is generated because of the fact that GROUP BY requires unique group keys, which we have intentionally not passed unique to return the value of SELECT @@version in the error message.

2. UNION-based SQL Injection:

The union operator appends the results from both queries vertically. This allows the user to run two or more queries if they have the same structure. I have chosen the SELECT query in this example. For this exploit to work, the following conditions must be satisfied.

Each SELECT statement within the UNION has the same number of columns.
The columns must also have similar data types.
The columns in each SELECT statement are in the same order.

SELECT first_name, last_name from customer UNION SELECT username, password from login;

Let’s try to implement a UNION SQL injection.

We have a simple web page that takes a name and if the name is found return the name and its email address.

We will use the following input in the search box.

mary’ UNION SELECT 1;--

Which gives the following output.

It seems that the structure of our query is different than the original query. Let’s try to increase the number of columns

mary’ UNION SELECT 1,2;--

Which gives the following output.

This query was successful which means that we have the number of columns correct now. Expanding on this idea the attacker can get any data from the database as long as he is using 2 columns. For example the following query:

mary’ UNION SELECT 1,@@version;--

gives the following output.

We can get all of the information on the databases present in this server. Using the following query.

mary’ UNION SELECT table_name,column_name FROM information_schema.columns;--

The above query will show all of the database tables and columns but I have shown the ones which we will be using.

Now we know that this table contains these columns. We can exploit it by the following query.

mary’ UNION SELECT customer_id, email FROM customer;--

3. Blind SQL Injection:

This type of SQL injection does not show any kind of error. That’s why it is called a Blind SQL injection. In this type of injection, the attacker relies on the response type from the server or the time it takes to get a response from the server. That is why there are 2 types in Blind SQL Injection.

Boolean-Based Blind SQL injection
Time-Based Blind SQL injection

In the Boolean-Based injection, the server responds with a True or False response. The attacker will modify the query according to the response of the server and will reveal information about the database.

For example, as we saw in Error-Based SQL injection the server will respond with “exists or not exists” response.

From the Error-Based injection, we know that name MARY exists in the database so we will modify our query to MARY’ AND 1=1;--, which will generate the following response.

Alternatively, feeding MARY’ AND 1=2;-- will return not exist as MARY exists in the database but 1=2 is false.

We can use the same method to find out the version of the database using the following query.

MARY' and substring(@@version,1,1)=1;--

If the first character of the version of the database is 1 then this will generate a positive response.for the second character we can use the following query.

MARY' and substring(@@version,2,1)=1;--

We can also know the name of the database. First, we need to know the length of the name of the database. We can do it from the following query.

MARY’ and length(database())=1;--

We can increment the trailing value until we get a positive response from the server.

To find out the characters in the database name we can try the following query.

MARY' and substring(database(),1,1)='a';--

We can replace the last character with all the alphabets until we get a positive response.
We can change the value after the database() to check for that particular character, i.e.

MARY' and substring(database(),2,1)='a';--

In the Time-Based Blind SQL injection, the attacker relies on the time it takes to get a response from the server. For example, consider the following query.

MARY' AND sleep(10);--

The above input will force a 10-second delay in the response. We can expand on this concept by doing the following query.

MARY' and if((select+@@version) like "10%",sleep(2),null);--

If the response comes in two seconds, it means that the version starts with “10.” The “like” string operator we used in the query is designed to make a character-by-character comparison.

Preventing SQL Injection:-

SQL injection has 2 root causes:

Not validating the input before constructing the query.
Including user input in building dynamic queries.

To lessen the problem, we need to enforce input validation and resort to prepared statements in combination with other protection methods.

1. Validating user-supplied input:-

There are 2 possible ways to validate user-supplied inputs.

Blacklist the Input
Whitelist the Input

For both methods, we have to prepare a list of characters that will be blacklisted or whitelisted.
Blacklisting the input means that you don’t accept the characters from the list in the input, i.e. JS validation.
Whitelisting the input means that you escape the characters from the list in the input

From the two Whitelisting is more preferable.
Blacklisting is not a recommended way to protect against SQL Injection because it is highly prone to failure. It works as long as the developer can make sure that the user input fields accept no special characters, other than what’s required.

2. Prepared Statements:-

This means that you execute the queries from the user who has minimum rights to execute it. This will soften the impact of an SQL injection attack. For example, A user who only has the right to read the database will only be able to access the data not alter or delete it in the case the application is compromised.

3. Additional layers of security:-

Solutions like a Web Application Firewall (WAF) can help as an extra measure of protection against SQL Injection attacks. WAFs inspect traffic at the application level and can determine whether it’s bad or not. Maintenance is required as signatures need to be updated, otherwise, attackers can find a way to bypass the WAF.

For Your Business Requirements