How to Defend Your Business Against SQL Injections
One of the oldest (but often neglected) security vulnerabilities is SQL injection. One common scenario goes like this: An unsuspecting programmer writes an application that accepts input from the user which serves as a parameter to retrieve or store data from a database (e.g., a web login form). The programmer writes a dynamically populated SQL query inside the app, based on user input like username and password (see Image 1 for reference). An attacker can then pass the SQL command as an input parameter in a form that alters the behavior of the query inside the app. There are various possible unwelcome results from this, such as “bypassing login logic,” “delete data,” or even “drop database.” In this article, we will discuss ways to prevent SQL injection from happening.
SQL Injection Prone Login Pseudocode
How to Prevent SQL Injection
In this section, we’ll explore eight ways to prevent SQL injections.
1. Use Stored Procedure, Not Dynamic SQL
Consider our earlier dynamic SQL example. In the images below, you can see what it looks like after a user executes SQL injection in the login form. Notice that the fourth statement will be ignored since the “–” syntax disables any succeeding commands, while the third always returns “true.” This results in a successful login, even if the username and password are incorrect (see Images 1.1 and 1.2).
Using stored procedure prevents SQL injection from happening since input parameters are always treated as an actual text value, rather than as a command (see Image 1.3). Refer to Image 1.4 for the new login pseudocode version using stored procedure, a much safer alternative compared to dynamic SQL. Stored procedure is defined as a compiled SQL command saved in a database server that can be reused multiple times by an external program.
Login Form with SQL Injected Input
After SQL Injection
Stored Procedures
Revised Login Pseudocode
2. Use Prepared Statements
Prepared Statements (PS) are pre-compiled SQL commands created inside a program that can be used many times over the course of the application’s lifecycle. By default, PS input parameters are binded. Binded parameters are treated as plain text values, which prevents any command alteration during an SQL injection attack. Consider the example in Image 2.1: A PS variation of the dynamic SQL query introduced earlier maintains the intended login behavior despite the SQL injection parameters in Image 2.2. Image 2.3 shows the resulting SQL command after the input parameters and PS were binded. This will not cause any unexpected SQL results or program outcomes.
Login Prepared Statement Pseudocode
Login Form with SQL Injected Input
Prepared Statement after Value Substitution
3. Use Object Relational Mapping (ORM) Framework
ORM is a programming technique where tables in a database are converted into an object that represents the structure of the database. Programmers can safely use the object to communicate with the tables in the database to run commands like insert, update, search, and delete.
A good example of an ORM framework in Java is Hibernate. Image 3.1 shows how ORM in Hibernate can prevent SQL injection. In line 2, both “username” and “password” are binded parameters that are set on the 3rd and 4th lines. Values that pass the 3rd and 4th lines will be treated as a string and not as part of the SQL command. Running the command on line 5 is even safer since we are explicitly informing Hibernate to expect a single query result for a user credential. Multiple user identities are suspicious; thus the program needs to “throw an error Exception” for proper handling (e.g., deny or disable the user from logging in).
A Safe Hibernate Pseudocode of the Dynamic SQL Query
4. Least Privilege
A program requires user credentials to run SQL commands, such as insert, update, search, delete, and drop, to communicate with a database. To minimize the impact during an SQL injection attack, the program user should only have the required database privileges. Additional privileges should be granted on an as-needed basis. With minimal database privileges, the potential damage of an SQL injection attack is reduced. Table 4.1 is a good example of user credentials with minimal privileges. If an SQL injection attack aiming to drop the entire database table is invoked (like in Image 4.1), such an attack will fail since the user does not have drop capability. Do note that least privilege does not prevent SQL injection, but it does help reduce compromise damage.
SQL Injection Attack Drop Table
5. Input Validation
Validating input plays a significant role in preventing SQL injection. Suspicious inputs are filtered prior to submission or processing by the server when validated. An example of input validation is an email validator. There are two types of validation: server side and client side.
Consider an example of client-side validation (Image 5.1). Passing an SQL injection query would not work, since the input field only accepts a valid email address based on a regular expression (line 3). In the case of microservices where values are passed via REST API, validation must take place on the server side. Passing a value that does not conform to email the regular expression will be rejected by the server. Image 5.2 shows the easiest way to accomplish such a validation in a server via “annotation.” The code snippet is an email validator using Java’s Spring Framework. A server will throw an exception if the value passed is not a valid email address. Spring Framework is just one of the many programming frameworks available, and clients should feel free to choose one based on their preferences and technical know-how.
Javascript Code to Validate an Email ID
function ValidateEmail(mail)
{
if (/^\w+([\.-]?\w+)*@\w+([\.-]?\w+)*(\.\w{2,3})+$/.test(myForm.emailAddr.value))
{
return (true)
}
alert("You have entered an invalid email address!")
return (false)
}
Server-Side Email Validation Using Java’s Spring Framework
6. Character Escaping
Character escaping is an effective way of preventing SQL injection. Special characters like “/ — ;” are interpreted by the SQL server as a syntax and can be treated as an SQL injection attack when added as part of the input. Pre-cleansing input data by “escaping” characters tells SQL values that they should be treated as a string, rather than as a command. Escaping special characters doesn’t guarantee SQL injection prevention, but it reduces the risk significantly.
Here is an example of how character escaping works. An attacker enters a username with an SQL injection command (Image 6.1) aiming to bypass login. Performing character escaping in the input transforms the value to a plain text (Image 6.2). The SQL command in Image 6.2 will not find any valid user in the database, which, in turn, prevents the attacker from bypassing the login form.
Login Form with SQL Injected Input
SQL Command After Character Escaping
7. Vulnerability Scanners
There are times when we need to uncover SQL injection to an already deployed program written with numerous lines of code. Scanning the code manually in search of SQL injections entails a lot of work, and chances of missing some are high. A good option is using sqlmap, an open-source penetration testing tool that detects SQL injection. This works by running sqlmap in the command line and passing the URL of the target application. Sqlmap then dumps the results into a log file where possible areas of attack can be analyzed by the development team in order to perform necessary actions (e.g., code patching or refactoring). In Image 7.1 below, sqlmap detected that the parameter “id” is injectable, probably because it uses an auto-incremented value where the attacker can do some wild guessing.
SQLMap Sample SQL Injection Result. Source
There are other options for enterprise-grade penetration testing tools, such as Tenable, but they come at a cost. In the end, it’s up to your organization to choose the tool that best suits your needs.
8. Use Web Application Firewall
Launching a website on the Internet exposes you to threats, as it is vulnerable to attack. To protect websites, a web application firewall (WAF) is needed. According to OWASP.org, a WAF is “an application firewall for HTTP applications. It applies a set of rules to an HTTP conversation. Generally, these rules cover common attacks such as cross-site scripting (XSS) and SQL injection.”
A WAF is a layer 7 protection that works by filtering common SQL injection patterns in HTTP requests. Here are the two most well known open-source WAFs that can be used against SQL injection:
- ModSecurity: This is a popular open-source WAF owned by TrustWave. It does not have a UI, but does a good job protecting against SQL injection.
- NGINX WAF: This is based on the open-source ModSecurity tool, and was modified for enterprise use. It has an audit logging UI that allows system administrators to monitor websites remotely.
Summary
If we really consider the reasons for SQL injection, it all boils down to configuration and brevity of code. That being said, the best form of SQL injection protection rests with the developer. Make sure to write code at the very start of a project that will avoid common mistakes that lead to SQL injection. Using a modern programming language and framework will also greatly help, as they have built-in libraries that can be used to run SQL commands that deter SQL injections.
Deploying applications to a leading cloud provider can be useful too. These companies come with WAF offerings so that you won’t have to deal with maintaining and installing them on your own. And at the end of the day, it’s always a best practice to monitor your applications in the event an attack does take place. A dedicated security team that monitors your applications and infrastructure can be a major factor in preventing web attacks—and arming them with the right monitoring and security analytics tools will make their lives much easier. In short, being security-paranoid (trusting no one) may sound like overkill, but it’s the key to avoiding SQL injections and ensuring a secured application.