How to Prevent SQLi?

Injection vulnerabilities have been categorized as the topmost dangerous vulnerabilities in the OWASP (Open Web Application Security Project®) top 10 2017 vulnerability chart. SQLi (SQL Injection) is one of these injection vulnerabilities that can lead from sensitive data disclosure to complete system compromise. That is the reason it is recommended to follow certain procedures that can help us in preventing SQLi. The main goal is always to sanitize the input of the user and not let it pass the application in raw format in any case. You can find more details on SQLi, its types, and some useful examples here.

Some of the methods to achieve this goal are explained below.

Using prepared statements

The first defense that is very necessary to prevent SQLi is the defense that is applied during the backend development of the application specifically at the point where the user input interacts with the database to get data.

The root cause of SQLi is that query and data is sent combined. When both data and query get mixed up, the behavior of the application changes, and it starts spitting out wrong information. Prepared statements help in sending this data separately.

Demonstration

Let’s say we want to perform login using “userid” and “password”. If we do not use prepared statements, the format of the query would be like this:

Select * from users where userid=5 AND password=12345

As far as the query is concerned, it will work fine, and it will return the data about user 5.

But there is one problem and it’s that the attacker can easily insert any input he wants and that will be directly transmitted to the database.

The query and the input are two separate things, so they should be sent separately to the database. To accomplish the same task using prepared statements, we will perform the following steps.

Make a connection with the database and store it in a variable (dbConnection) for later use
Prepare required requery
$stmt = $dbConnection->prepare(“SELECT * FROM USERS WHERE userid = ? AND password = ?”);
Bind the parameters
$stmt->bind_param(“ss”, $username, $password);
Note: Until this point, query and parameter details have been sent to the program that communicates with the database so at this point we segregated user input from the query.
Execute the query
$stmt->execute();
Note: As input data is sent separately now, the chances of SQLi are very low now.

User-input validation and sanitization

Input validation is the process of identifying whether some piece of input should be used for processing or not. Input validation and sanitization help a lot in filtering out bad data from user input. This technique can help a lot in preventing SQLi. There are two ways how input validation is carried out.

Blacklisting approach

In this technique, bad characters are defined in the function that validates user input. If any of those characters are found in user input, they are either eliminated or they are converted into other forms so that the action cannot be performed as intended by the attacker. For example, a normal user does not use ‘ or ” while entering his account details so these characters should not be allowed in input fields.

Although this approach is useful, it sometimes does not work as threat attackers continuously evolve with time and they come up with some idea of bypassing the filter.

Whitelisting approach

In a whitelisting approach, developers identify the type of input that is needed to accomplish a task. For example, if we talk about password complexity, then only a few special characters should be allowed. There is no need to allow all the special characters so that passwords can be made complex. Let’s say, we only allow few characters like @, %, *, and ^, then all other special characters will be filtered out automatically and the risk of filter bypassing reduces a lot.

Input sanitization

Input sanitization refers to removing bad characters from the input that was passed to the application. Different languages deal with it differently. For example, in PHP we can use a function i.e. real_escape_string() to filter all the escape characters that can harm the system.

Firewall protection

Technology has evolved a lot. In this modern era, many new technologies are being developed to secure user data. Initially, firewalls were used to monitor basic network traffic to decide whether to allow or block some traffic. Now with the advancement of technology, new generation firewalls have been developed that not only perform old tasks but also do analysis on packet level to check for malicious content. These firewalls can also be configured to sanitize user input from forms. Even if somehow an unintended input gets passed to the backend of the application, it does not allow the leakage of sensitive information to anyone.

Conclusion

There can be more ways to protect a system against SQLi. A good practice is to use a multilayer protection mechanism to gain a good security posture of the system. Only front-end validation checks cannot serve the purpose. In the same way, only a firewall may not be able to defend you against new attack techniques. That is why we combine all these protection mechanisms to gain maximum security for our systems.