SQL injections. Preventive security.

With this article I want to start a series of articles about web applications security. This topic is truly interesting, sometimes challenging and, of course, very important for any web developer. Even if you think that your website is small and is not attractive to hackers, keep in mind that your website, if it is vulnerable, can be used to trick random users or even attack an external system. For example, using a security hole in your system an attacker can spread an exploit.

In this article I will talk about preventive measures against SQL Injections (aka SQLi). Those are preventive measures that may help in case you have legacy code, or someone in your team accidentally (or blindly) writes code vulnerable to SQLi.

SQL Injection is actually quite basic and relatively simple type of attack. It's relatively easy to prevent but attackers still successfuly use it even these days. According to OWASP Top 10 Vulnerabilities in web applications code injections are one of the most common attacks. This type of attack, if your web application is vulnerable, may allow an attacker to run an arbitrary code in your system. It can be SQL commands, PHP/Python/JavaScript/Bash code or even OS commands.

Despite the popularity of modern frameworks where SQL injections are less likely to happen, there are many websites that use "vanilla" PHP or custom made frameworks, sometimes with very bad security issues. You might be surprised but even big name companies may still have these problem. Despite of the company size or company name things like legacy code, under-qualified personnel, project outsourcing, lack of supervision and poor quality control procedures contribute to this problem a lot. There have been reports that two thirds of the U.S. companies have been breached by SQL Injection attacks in the past years. That sounds like a lot, doesn't it?

Where the SQL code can be injected? Wherever a user input is allowed or user can modify input data. It can be a text box in a form on your website or URI parameters in URL. In theory all user input data must be validated and secured before stored in database. However, because of the reasons mentioned above these measures are often missing in web applications or not fully implemented.

What kind of damage can SQL injection do? In fact, it can be very-very dangerous. Using SQL injection an attacker may execute SQL query that may destroy all data in your database and even affect your file system. Furthermore, an attacker may steal your clients' personal information like full name, address, email, etc. Such data leak may destroy your business given the fact that we live in GDPR era. And I am not even mentioning credit card details here because if you deal with this type of data, I assume you don't even need to read this article - I am hoping you already know your stuff. :) 

Below are a just few hints how to help you prevent or reduce the risks related to SQL injections. 

Database user grants

A good start would to review the grants of the database user that is used by your web application. This won't prevent an SQL injection if your application is vulnerable, but at least it may reduce the potential impact of the attack if it happens. 

Normally, your web application should only be able to SELECT, INSERT, UPDATE and sometimes DELETE rows in your database. Commands like DROP, TRUNCATE, EXECUTE should be disabled for the web app database user. 

It might be also a good idea to disable DELETE command for the web app database user and only "soft delete" entries in your database tables. This means instead of deleting a user from your 'users' table with the DELETE command you simply change user's status from 'active' to 'deleted', and update respective 'deleted_at' field with the current timestamp by using the UPDATE command. Then you can have a cron script (which, if implemented done properly, in theory cannot be attacked by SQL injection) that will use a different database user with DELETE grants enabled, and periodically will scan your tables for soft-deleted entries and delete rows from database tables.

Use code reviews

Code review is a great tool to catch code vulnerable to SQL injection. If your team has at least 2 developers, always cross-check your code. An extra pair of eyes can often help to catch things like typos, broken logic and security issues.

It's worth to mention even senior developers should not hesitate to have junior developers review their code. This can help to identify problems and also will help junior developers to learn and grow.

Use WAF

A web application firewall (aka WAF) monitors, filters and, when needed, blocks HTTP traffic to a web application. Depending on configuration WAF can detect and block HTTP requests that contain specific characters or SQL commands. This is a great tool to use! If you use a properly configured WAF, your web application may be protected even if there is a security issue in the code - WAF simply won't allow an attacker to exploit a vulnerably and execute SQL injection attack.

There are a few options that you can consider when choosing WAF solutions. You can use web server modules (i.e. Apache ModSecurity) or use third-party paid services like CloudFlare.  

Use ORM or popular framework

Modern frameworks typically include SQL query builders and ORM libraries that create and validate SQL queries for you. All you need to do is to bind parameters to SQL statements and query builder will create prepared SQL queries that are safe and protected against SQL injections.

Even if you don't use a framework but you are allowed to use third party libraries, consider a ORM library such as Doctrine or RedBeanPHP.

If for whatever reason your cannot use a framework or a ORM and you write your SQL queries directly in your code, then consider using prepared statements. In case with PHP it could be achieved with PDO or MySQLi built-in functions.

All these measures mentioned above can contribute to your web application security. However, keep in mind that security is a complex issue and there multiple aspects that you need to consider when taking care of your website's security.