💉SQL injection
What is it?
SQL Injection is a web vulnerability that arises when unsanitized input provided by users is directly inserted inside a direct SQL query inside a webpage. There are 3 main categorizations of it and those can be differentiated by the exploitation channel, the sqli type or the order . By channel we can differentiate those as:
In-band SQLi: here we use the same comunication channel of the webpage, using the direct communication with the database
Inferential SQLi: like in blind sqli, we try to infere what's the data we're extracting by the response of the query (error, time, conditionals etc)
Out-of-band SQLi: this is by far the most used, it relies on side technologies to exploit this vulnerability, like DNS or HTTP requests.
By type we can instead classify sqli as:
Standard Sqli: those are classic sqli, where the response of the manipulated/exploited query is directly reflected inside the response (and potentially the webpage)
Blind Sqli: while with standard sqli we can extract information directly, here we need different mechanism to infere the data, like boolean conditions and time based responses, like putting the query to sleep if the first letter of the string we want to extract is 'b' (you can imagine that this way is harder to extract data)
By order we see that there are:
First Order SQLi: in this classification we have all the sqli that incorporate and processes the provided input directly in in the unsafe query
Second Order SQLi: here we have the sqli that happens when the user input is actually stored for future use (like putting it into a db itself or else).
How to test for it?
The idea is to cause identifiable Errors in the application via the input and try understanding what's the context and potentially get a hunch of the query.
A common way to test is using a single ' inside a user controlled input to cause a string escape, exiting the statement and triggering a potential error. Other tests include using clauses (both true or false) or specific payloads for specific DBMS (if known).
If there are some parts of the application that are talking to a database to retrieve or store data, it's possible to try interact with those and find a way to exploit it.
How to exploit it?
<?php
$query = "SELECT * FROM object o WHERE o.id = '" $_GET['id'] + "';";
$result = pg_query($connection, $query);
?>Whit the above example we could see that if we feed a number like 1 we would get the object with id 1 or nothing if it doesn't exists.
Using the previous testing technique, if we put a ' inside the input field, we will escape the string leaving the following '; trailing the query all by itself triggering an error. If that happens, it means that the application is potentially vulnerable to SQLi, and we could try some other payloads, like 1' OR 1=1;-- and get all the items in the table.
1'
This is used to have a correct input and escape the string
OR 1=1
This is used to give an always true statement to bypass the first condition
;--
This ends the query and comments the rest of the original string (note that the commenting could vary from DBMS to DBMS)
This is a very basic example to understand how a SQLi works, there are many more techniques to use, like Union attacks, blind injections ecc.
Why this works?
Let's use another code example:
<?php
$query = "SELECT * FROM users u WHERE u.name = '" $_GET['username'] + "' AND u.password = '" + $_GET['password'] + "';";
$result = pg_query($connection, $query);
?>in this example we're feeding via the URL the parameters usernameand password like http://target.com/login?username='<something>'&password='<something>' that means that we can feed variables directly there and try to fiddle with the underlying query construction. If we try to use admin as user and test as password there, the query would look like this:
SELECT * FROM users u WHERE u.name = 'admin' AND u.password = 'test';so if we try to fiddle with the 'username' field and try to put a single ' on it, we would get wit this query:
SELECT * FROM users u WHERE u.name = ''' AND u.password = 'test';What's the impact?
The main impact is compromising sensitive data, from data breaches to data disruption. In some cases is possible to reach higher criticality by using it to achieve code execution and persistent access to systems compromising entire organizations.
How to mitigate?
Most common way to mitigate and prevent SQLi is using prepared statements
<?php
$query = "SELECT * FROM object o WHERE o.id = '" $_GET['id'] + "';";
$result = pg_query($connection, $query);
?>A safer way of the previous statement could be
<?php
$id = $_GET['id'];
$query = "SELECT * FROM object o WHERE o.id = ':id'";
$stmt = $pdo -> prepare($query);
$stmt -> bindParam(':id', $id);
$stmt -> execute();
$result = $stmt -> fetchAll(PDO::FETCH_ASSOC);
?>Yes, it surely is more complex and verbose, but it helps prevent some types of SQLi.
Prepared statement combined with input validation and whitelisting permitted values could prevent lots of SQLi but not everyone.
References
Last updated