How to prevent SQLi? – Web hosting | Cloud Computing | Data center
Injection vulnerabilities have been ranked among the most dangerous vulnerabilities in the OWASP (Open Web Application Security Project®) Top 10 Vulnerability Table 2017. SQLi (SQL Injection) is one of those injection vulnerabilities which can range from disclosure of sensitive data to complete system compromise. This is the reason why it is recommended to follow certain procedures which can help us to prevent SQLi. The main goal is always to clean up user input and not let it pass the application in raw format under any circumstances. You can find more details about 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 which is very necessary to prevent SQLi is the defense which is applied during the backend development of the application specifically at the point where user input interacts with the database to get data.
The root cause of SQLi is that query and data are sent combined. When the data and the query are mixed up, the behavior of the app changes and it starts spitting out bad information. Prepared reports help send this data separately.
Let’s say we want to make a connection using “userid” and “password”. If we don’t use prepared statements, the query format would look like this:
Select * among users where userid =5 AND password =12345
As for the query, it will work fine and return the data on user 5.
But there is a problem and that is that the attacker can easily insert any entry he wants and which will be directly transmitted to the database.
Query and input are two separate things, so they need to be sent to the database separately. To accomplish the same task using prepared instructions, we will perform the following steps.
- Establish a connection with the database and store it in a variable (dbConnection) for later use
- Prepare the required request
$ stmt = $ dbConnection-> prepare (“SELECT * FROM USERS WHERE userid =? AND password =?”);
- Link parameters
$ stmt-> bind_param (“ss”, $ username, $ password);
To note: So far, the details of the query and parameters have been sent to the program that communicates with the database. At this point, we have therefore separated the user input from the request.
- Run the query
$ stmt-> execute ();
To note: As the input data is now sent separately, the chances of SQLi are now very low.
Validation and sanitation of user inputs
Input validation is the process of identifying whether an input part is to be used for processing or not. Validating and sanitizing inputs goes a long way in filtering out bad data from user inputs. This technique can help a lot to prevent SQLi. There are two ways to validate entries.
In this technique, the bad characters are defined in the function that validates the user’s input. If any of these characters are found in the user’s input, it is either discarded or converted to other forms so that the action cannot be performed as intended by the attacker. For example, a normal user does not use ‘or’ when entering their account details, so these characters should not be allowed in input fields.
While this approach is useful, sometimes it doesn’t work, as attackers continually evolve over time and they get the idea of bypassing the filter.
In a whitelist approach, developers identify the type of input needed to accomplish a task. For example, if we are talking about password complexity, only a few special characters should be allowed. It is not necessary to allow all special characters for passwords to be complex. Let’s say we only allow a few characters like @,%, * and ^, then all other special characters will be filtered out automatically and the risk of bypassing the filter will be greatly reduced.
Entry cleanup refers to removing bad characters from the entry that was passed to the application. Different languages treat it differently. For example, in PHP we can use a function ie real_escape_string () to filter out all escape characters that can damage the system.
Technology has evolved a lot. In this modern age, many new technologies are developed to secure user data. Initially, firewalls were used to monitor basic network traffic in order to decide whether to allow or block some traffic. Now, with the advancement in technology, new generation firewalls have been developed that not only perform old tasks but also perform packet level scanning to look for malicious content. These firewalls can also be configured to clean user input from forms. Even if somehow some unintentional input is passed to the backend of the app, it does not allow sensitive information to be leaked to anyone.
There may be other ways to protect a system against SQLi. It is good practice to use a multi-layered protection mechanism to achieve good system security posture. Only front-end validation checks cannot serve this purpose. Likewise, only a firewall may not be able to defend you against new attack techniques. That is why we combine all these protection mechanisms to achieve maximum security for our systems.
Read more : How to Create a Security Operations Center on a Budget