SQL Injection refers to an injection attack wherein an attacker can execute malicious SQL statements (also commonly referred to as a malicious payload) that control a web application’s database server (also commonly referred to as a Relational Database Management System – RDBMS). Since an SQL Injection vulnerability could possibly affect any website or web application that makes use of an SQL-based database, the vulnerability is one of the oldest, most prevalent and most dangerous of web application vulnerabilities.
SQL injection, also known as SQLI.
For example, let’s say functionality in the web application generates a string with the following SQL statement:
$statement = "SELECT * FROM users WHERE username = 'bob' AND password = 'test'";
This SQL statement is passed to a function that sends the string to the connected database where it is parsed, executed and returns a result.
As you might have noticed the statement contains some new, special characters:
- * (asterisk) is an instruction for the SQL database to return all columns for the selected database row
- = (equals) is an instruction for the SQL database to only return values that match the searched string
- ‘ (single quote mark) is used to tell the SQL database where the search string starts or ends
Now consider the following example in which a website user is able to change the values of ‘$user’ and ‘$password’, such as in a login form:
$statement = "SELECT * FROM users WHERE username = '$user' AND password
An attacker can easily insert any special SQL syntax inside the statement, if the input is not sanitized by the application:
$statement = "SELECT * FROM users WHERE username = 'admin'; -- ' AND password = 'anything'";
What is happening here? The green part (admin’; –) is the attacker’s input, which contains two new, special characters:
- ; (semicolon) is used to instruct the SQL parser that the current statement has ended (not necessary in most cases)
- — (double hyphen) instructs the SQL parser that the rest of the line (shown in light grey above) is a comment and should not be executed
This SQL injection effectively removes the password verification, and returns a dataset for an existing user – ‘admin’ in this case. The attacker can now log in with an administrator account, without having to specify a password.
The Different Types of SQL Injection Vulnerability
- Error-Based SQL Injection
- Boolean-Based (Blind) SQL Injection
- Time-Based SQL Injection
- Out-of-Band SQL Injection Vulnerability
Prevention for SQL Injection
There are several effective ways to prevent SQLI attacks from taking place, as well as protecting against them, should they occur.
The first step is input validation (a.k.a. sanitization), which is the practice of writing code that can identify illegitimate user inputs.
While input validation should always be considered best practice, it is rarely a foolproof solution. The reality is that, in most cases, it is simply not feasible to map out all legal and illegal inputs—at least not without causing a large amount of false positives, which interfere with user experience and an application’s functionality.
- Parameterized statements : With most development platforms, parameterized statements that work with parameters can be used (sometimes called placeholders or bind variables) instead of embedding user input in the statement. A placeholder can only store a value of the given type and not an arbitrary SQL fragment. Hence the SQL injection would simply be treated as a strange (and probably invalid) parameter value.
- Escaping: A straightforward, though error-prone way to prevent injections is to escape characters that have a special meaning in SQL. The manual for an SQL DBMS explains which characters have a special meaning, which allows creating a comprehensive blacklist of characters that need translation. For instance, every occurrence of a single quote (
') in a parameter must be replaced by two single quotes (
'') to form a valid SQL string literal.
- Pattern Check : Integer, float or boolean, string parameters can be checked if their value is valid representation for the given type. Strings that must follow some strict pattern (date, UUID, alphanumeric only, etc.) can be checked if they match this pattern.
- Database permissions : Limiting the permissions on the database login used by the web application to only what is needed may help reduce the effectiveness of any SQL injection attacks that exploit any bugs in the web application.