Top tips for preventing SQL injection attacks
In the wake of the Colonial Pipeline attack and other high-profile cases, IT teams may be scrabbling to shore up their endpoint protection. But those in the developer community know security weaknesses don’t begin and end there; write code improperly or with insufficient security, and you’re also coding in future web attacks.
Web vulnerabilities are an issue that affect even the biggest tech companies. They cover a host of different coding issues, but the examples above include a very specific type.
A zero-trust approach
SQL injection is one of the most dangerous and most common vulnerabilities, but fortunately there are several best practices developers can follow to ensure there are minimal chinks in their armor.
The first is to ensure that client-side input validation isn’t the only line of defense. This validation is a great tool for improved user experience, but it doesn’t work as a security mechanism.
It’s easy to remove client-side validation by altering JavaScript code loaded in the browser or do a basic HTTP call to the backend in a client-server architecture with a parameter that causes an SQL injection. Developers should be treating everything a client sends as potentially harmful and should therefore be validating on the server-side, ideally as close to the source as possible.
Developers should also think carefully about database user privileges. All SQL injection attacks are harmful, but some are more harmful than others: accessing user information is one thing but altering or deleting it is another.
To minimize the impact of an SQL injection, developers should be strategic about an application’s privileges on a database. Does a specific application really need the ability to read, write and update all the databases? Is it necessary for it to be able to truncate or drop tables?
In addition to not allowing every application free reign over a database, it is also unwise to have a single database user for an application. Making multiple database users and connecting them to specific application roles works in the same way as fire doors work to contain a fire: it prevents an attacker from quickly taking over an entire database.
Parameters are the best defense
A critical way developers should protect themselves is by using prepared statements and query parameterization. A lot of languages come with built-in features that help prevent SQL injection, and so when writing SQL queries you can use a prepared statement to compile the query.
Prepared statements can be used to perform query parameterization, which limits the SQL statements that can be entered: a developer creates a base query with placeholders and then user-given parameters can be safely attached to these placeholders. When using a prepared statement and parameterized queries, the database will first build the query execution plan based on the query string with placeholders, and then send the (untrusted) parameters to the database.
As the query plan is already created, the parameters do not influence this anymore and this completely blocks injection. Prepared statements with query parameterization are therefore the best defense against SQL injection.
Parameterization is also paramount when working with stored procedures. Many people believe that working with stored procedures is a good way to prevent SQL injection, but this is not always the case. Just like any SQL queries created within an application, a stored procedure can be maliciously injected. Therefore, as with SQL queries, developers should parameterize the queries in their stored procedure, rather than concatenate the parameters, to protect against injection.
However, there are some situations where prepared statements are not available. If a certain language does not support prepared statements, or an older database doesn’t allow developers to supply the user input as parameters, then input validation is an acceptable alternative.
Teams should ensure that input validation relies on allow-listing and not block-listing – using a well-maintained library or creating a rule that describes all allowed patterns with, for instance, a regular expression. Of course, even if prepared statements are available, input validation is a must.
Multi-layered security and stringent checking
In addition to parameterization and input validation, developers should consider using an object-relational mapping (ORM) layer to protect against injection. This transforms the data from a database into objects and vise-versa, reducing explicit SQL queries and therefore the risk of SQL injection attacks. However, it should be noted that vulnerabilities can still be created within ORM libraries if the wrong or outdated versions of Sequelize or Hibernate are used, so developers must be vigilant.
Ultimately, whatever security strategies are deployed, a strict reviewing system must be in place to review code and flag any vulnerabilities. Code review and pair programming do allow for this, but with manual reviewing processes there are always margins of error. For the highest levels of security, developers should look to specifically designed scanning tools to automatically check for SQL injection vulnerabilities and alert them to any weaknesses in their code.
SQL injection attacks are a dangerous online threat, but they can be defended against. With a zero-trust approach, the use of prepared statements and parameters, and a stringent code-checking process, developers can block any injection attempts. As cybercrime grows in tandem with digitalization, it’s more important than ever that developers write security into the heart of their code.