SQL injection (SQLi) is a code injection technique used to attack data-driven applications, in which malicious SQL statements are inserted into an entry field for execution. In this post, we will understand SQL injection attacks and what are best practices to avoid them.
SQL injection attacks allow attackers to spoof identity, tamper with existing data, cause repudiation issues, destroy the data or make it otherwise unavailable. This essentially compromises the integrity of data as well as application.
SQL Injection attacks can be majorly classified into three categories, they are as follows,
In-band SQL Injection occurs when an attacker is able to use the same communication channel to both launch the attack and gather results.
Error-based SQLi and Union-based SQLi are two common types of In-band SQL injections.
Inferential SQL Injection may take longer for an attacker to exploit, however, it is just as dangerous as any other form of SQL Injection. In an inferential SQLi attack, no data is actually transferred via the web application, instead, an attacker might be able to reconstruct the database structure by sending SQLi payloads, observing the application’s response and the resulting behaviour of the database server.
Boolean-based SQLi and Time-based SQLi are two common types of Blind SQL Injections.
Out-of-band SQL Injection occurs when an attacker is unable to use the same channel to launch the attack and gather results, It offers an alternative to time-based techniques, especially if the server responses are not very stable.
Out-of-band SQLi techniques would rely on the database server’s ability to make DNS or HTTP requests to deliver data to an attacker.
Defence Against Above Attacks
Use of Prepared Statements (with Parameterized Queries)
Parameterized queries force the developer to first define all the SQL code, and then pass in each parameter to the query later. This coding style allows the database to distinguish between code and data, regardless of what user input is supplied.
Prepared statements ensure that an attacker is not able to change the intent of a query, even if SQL commands are inserted by an attacker.
Read more in detail about Query Parameterization here.
Allow-list Input Validation
Various parts of SQL queries aren't legal locations for the use of bind variables, such as the names of tables or columns, and the sort order (ASC or DESC).
In such situations, input validation or query redesign is the most appropriate defence.
For the names of tables or columns, ideally those values come from the code, and not from user parameters.
For something simple like a sort order, it would be best if the user supplied input is converted to a boolean, and then that boolean is used to select the safe value to append to the query. Apart from this, Input validation is also recommended as a secondary defence in all scenarios.
Read more in details about input validation techniques on OWASP.
Escaping All User-Supplied Input
Generally this method is used as a last resort when none of the other techniques are feasible.This technique is to escape user input before putting it in a query, usually recommended for legacy code where input validation is cost effective.
Each DBMS supports one or more character escaping schemes specific to certain kinds of queries. If all user supplied input is escaped using the proper escaping scheme for the database, the DBMS will not confuse that input with SQL code written by the developer, thus avoiding any possible SQL injection vulnerabilities.
Object Relational Mappers
Using Object Relational Mapping frameworks enables to create database queries in a safe way, and as they are not constructed using strings, SQL injection vulnerability is less.
In this blog, we saw the possible SQL Injections which can attack your application to tamper with data by exploiting the vulnerability. Also, we listed out possible defence mechanisms against the same. If you want help in eliminating this vulnerability, you can get in touch with PLG Works, that’s us.