32
SQL Injection vs. Your Applications in the Modern Age
Developers dealing with web applications see a lot of things threatening to harm the things they build. Some of these things include attacks targeted at people (for example, social engineering), some of these attacks (DoS or DDoS attacks, Cross-Site Scripting, Cross-Site Request Forgery, Broken Authentication, Sensitive Data Exposure, Broken Access Control, Insecure Deserialization, etc.) target parts of web applications. However, some attacks primarily target your database and data stored there—one of such attacks is SQL injection (SQLi for short). In this blog post, we will look at the impacts such an attack might have.
SQL injection is an attack frequently targeted at web applications. The purpose of such an attack frequently is to exfiltrate sensitive data from the database and use it for the personal gain of the attacker. Such an attack is so prevalent and dangerous precisely because many developers overlook the importance of security when creating public, web-facing solutions. When security gaps are overlooked, malicious parties often find and exploit them. These nefarious actors exploit such vulnerabilities because they can profit from selling data stolen during the breach.
The impacts of such an attack depend on your web application. Suppose your application does not store sensitive information, in which case, the attack would likely not have any far-reaching consequences. However, if that’s not the case, you might have a serious problem on your hands—sensitive data could be stolen and sold off for profit, causing issues for your business and needless problems for your customers due to stress resetting passwords and changing information elsewhere.
The concept of SQL injection is pretty simple: such an attack works because some developers put everything that the user writes in a given input field into a query and pass it on to a database. A basic snippet of vulnerable code would look like this (you can also use $_GET instead of $_POST, the premise is the same):
$Input = $_POST['input'];SELECT * FROM demo_table WHERE column [= | < | > | <= | >= | LIKE ...] '$Input';As you can see, in some of the cases, the vulnerable code is pretty simple (it can also get complex, we will get into different scenarios later) – such vulnerable code is then exploited by attackers frequently using the quote character (') to induce errors. Once it induces errors, attackers know the code is vulnerable, and they can attack our applications.
There are a couple of categories that SQL injection attacks fall under:
Attackers use each of the types outlined above to accomplish different goals – classic SQL injection is useful if the attacker has some implied knowledge about the inner workings of the system, union-based SQL injection can be useful if the attacker combines the results of a couple of
SELECT statements into a single result set, error-based SQL injection is used when the application is "silent" (meaning that it doesn’t return any responses, so the attacker looks for functionality changes when issuing certain kinds of queries)At this point, you should have a pretty good understanding of what SQL injection is and what its types are, but how do you protect your applications from such an attack? Thankfully, there are a couple of well-known ways to lessen the risk of such a vulnerability being exploited now or in the future:
/* Prepare MySQL statement */
if (!($statement = $mysqli->prepare(
"SELECT customerID
FROM orders
WHERE item = ?"
))) {
echo "Failed: (" . $mysqli->errno . ") " . $mysqli->error;
}
/*
Bind variables to statement as parameters using bind_param().
The type (first) parameter can take integers (i), doubles (d),
strings(s), and blobs (b).
*/
$purchasedItem = 'ice cream';
if (!$statement->bind_param("s", $purchasedItem)) {
echo "Failed: (" . $statement->errno . ") " . $statement->error;
}
/* Execute prepared MySQL statement */
if (!$statement->execute()) {
echo "Failed: (" . $statement->errno . ") " . $statement->error;
}Keeping these points in mind should help keep your application humming along nicely.
Using prepared statements, using security-focused plugins provided by MySQL, avoiding granting unnecessary administrative privileges, and taking other aforementioned precautions into account should put you and your database on a good path. However, if you want to dig deeper into MySQL and understand why SQL queries work the way they do and why using a, for example, web application firewall (WAF) might protect you against many kinds of attacks, including SQL injection, consider this:
SHOW PROFILE FOR QUERY [query id here];
If used correctly, in most cases, the output will provide you with what the query did and the duration of the task. You will also be able to observe a bunch of interesting things, including getting answers to questions like:
Some of the answers to these questions might help you understand why SQL injection works the way it does. For example, issue a
SHOW PROFILE FOR QUERY query again, and you will see the following:
The marked row indicates that straight after starting to run the query, MySQL checks for permissions. Remember what we discussed earlier? You should avoid granting unnecessary privileges precisely for this reason – if the MySQL account that an attacker is targeting does not have enough permissions, the query will fail. Since the functionality of the
SHOW PROFILE FOR QUERY queries aren’t in the scope in this blog post, we aren’t going to go into too much detail here, but you can see how and why following the advice above is important.Most of the advice outlined above is also applicable in the database space:
Take the advice above into account, and you should be well on the way towards mitigating SQL injection issues that might strike your database now or in the future.
As you might tell, most of the advice helping you secure your applications and databases from SQL injection is pretty general and straightforward. However, securing your databases from SQL injection is not rocket science either – keep the guidelines that have been outlined above in mind, and you should be good to go!
Lukas is an ethical hacker, a MySQL database administrator, and a frequent conference speaker. Since 2014 Lukas has found and responsibly disclosed security flaws in some of the most visited websites in Lithuania and abroad including advertising, gift-buying, gaming, hosting websites as well as some websites of government institutions. Lukas runs one of the biggest & fastest data breach search engines in the world — BreachDirectory.com and frequently blogs in multiple places educating people about information security and other topics. He also runs his own blog over at lukasvileikis.com
32