How to Prevent SQL Injection

The SQL (structured query language) injection is a well-known, if not, one of the best known, software weaknesses and security vulnerabilities. Despite its reputation, how to prevent SQL injection remains one of the leading vulnerabilities, and attacks continue to grow.

Finding SQL Injections

Injection vulnerabilities (of which SQL injections are one flavor) are the number one web application security issue according to the OWASP Top 10. SQL injections are number six on the CWE Top 25. Other examples of the same sorts of security vulnerabilities are:

• Command injection (CWE-77)
• OS command injection (CWE-78)
• Hibernate injection (CWE-564)
• Expression language injection (CWE-917)

All these vulnerabilities share a common attribute. They’re exploited using data from outside the system, user or file input, or whatever, in potentially dangerous functions.

Fortunately, SQL injections are detectable by tools both statically and dynamically. However, you can never be sure you caught them all. Preventing SQL injections is also key to reducing the frequency and impact of these vulnerabilities. A mature DevSecOps process that incorporates detection and prevention of vulnerabilities is likely to catch and prevent these types of vulnerabilities from ever entering a released product.

What Is SQL?

SQL is a domain-specific language designed for the management of relational databases. Relational databases present data as a collection of tables in rows and columns. Each row has a key that provides the relationship to other tables. Here’s an example of table “user”:

SQL is the language of choice for managing, querying, and manipulating data in a relation database. It defines the tables and relationships in database creation. For most day-to-day use, developers use SQL for “CRUD” — to create, read, update, and delete data.

Why Is SQL Exploitable?

General programming languages don’t include support for SQL. Access to database commands is via an API provided by the database vendor. In many cases, SQL commands are sent as strings that the API interprets and applies to the database. Here are some simple SQL queries:

A typical SQL query takes the following form:

Select (something) from (somewhere) (optional condition)

Using the above tables as an example to retrieve the email from the row where the last name is “Smith”, the following SQL statement is used:

Select email from user where lastname = ‘Smith’

The output would be as follows:

Smith1234@mail.com
John.smith@mail.net
Smith1234@mail.com

Getting input from users with a web form (see below), is a common use case in web applications. The data the users enter in the field “Name”, for example, is used to form SQL queries based on input received. Consider the following simple web form:

The software processes the form and assigns the values to variables something like this:

String formName = request.getParameter(Name);

The string entered as “Name” is used to assemble the query using that user input:

String myQuery = “select message from user where email = ‘” + formName +”’;”

Using this constructed query:

Select message from user where email= ‘Smith1234@mail.com’;

The output from this (using the table above as the example) is as follows:

Hello
How are you

Hopefully, it’s easy to see how this can all go wrong. Given the use of user input directly in the string, someone who understands SQL syntax can easily manipulate it to generate the SQL query. Consider the following example:

Using the same form above, someone enters “Smith1234@mail.com’ or ‘1’=’1” in the email field.

The same code will assemble the following SQL query string:

Select message from user where email = ‘Smith1234@mail.com’ or ‘1’=’1’;

Adding something seeming innocuous such as “or 1=1” changes the logic of the query and potentially leaks data by returning all of the rows in the table called “user”. In this case showing you messages for every user in the table. A serious privacy problem and in some jurisdictions or contexts a potential legal problem as well, such as GDPR, HIPAA, or CCPA.

The query above ends up with the following, unintended output:

Hello
Password 1234
How are you
Don’t tell anyone
Wassup

How a SQL Injection Works

The basic gist of a SQL injection (and other types of injection vulnerabilities) is the use of unchecked data from outside the application, such as user input text, in a SQL query string. The description for CWE 89: “Improper Neutralization of Special Elements used in an SQL Command (‘SQL Injection’)” defines this more precisely:

“Without sufficient removal or quoting of SQL syntax in user-controllable inputs, the generated SQL query can cause those inputs to be interpreted as SQL instead of ordinary user data. This can be used to alter query logic to bypass security checks, or to insert additional statements that modify the back-end database, possibly including execution of system commands.”

The same entry in the CWE database (CWE 89) provides another simple example of this attack. Assuming the application makes a query on behalf of the user “wiley” and the user constructs input in such a way that contains SQL instructions, for example:

name'; DELETE FROM items; --

If this application doesn’t do any validity checking on this input, it constructs a query like this:

SELECT * FROM items WHERE owner = 'wiley' AND itemname = 'name';
DELETE FROM items;
--'

If this attack is successful, it deletes all data in table items – causing havoc to the database. Any valid SQL command could potentially be executed this way. This is an example of write/modify attack where the intention is to corrupt the database or insert unwanted information. The previous example (“or 1=1”) is a read attack where the intent is data leakage.

Many implementations of database servers accept the semicolon as a command separator which allows such SQL injections to be so dangerous. The trailing “–” signals that the rest of the text is a comment, forcing the SQL interpreter to ignore the trailing quotation mark, which would otherwise cause a syntax error. There are multiple ways to trick the assembled query string. Sometimes in ways developers never imagined.

Mitigations to Protect Against SQL Injections

There are several mitigations available that developers should implement. Primarily, the security stance should consider all data coming from outside the application that’s untrusted. The following are typical mitigation strategies:

• Use prepared statements with parameterized queries.
• Use stored procedures.
• Whitelist input validation.
• Escape all supplied input.

These are described in more detail in the OWASP cheat sheet for SQL injections.

Testing for SQL Injections

A typical approach to security is to perform various types of security testing as part of regular QA operations when the integrated software is running. Unfortunately, functional testing doesn’t try to insert exploits into user input fields because most testers don’t think like bad actors.

Besides the fact they traditionally don’t have the time or the direction to do so. It’s also difficult to manually test for injection type vulnerabilities since it requires trying so many different combinations of inputs. This is where fuzzing or fuzz testing comes in. It creates random, unexpected, and invalid data as inputs to the application under test. Fuzz testing is part of penetration testing as the goal is to expose security vulnerabilities through the exposed interfaces.

Penetration Testing

Penetration testing (and by extension, fuzz testing) is beneficial because it can discover security issues that have snuck through the process and reveal significant security issues. However, like all dynamic testing, it depends on the amount of test, code, and API coverage to completely test all possible permutations and combinations. Penetration testing depends on the thoroughness of the functional testing, typically done at the UI level. This makes it important to support your penetration testing efforts with API testing and SAST to ensure that you’re being thorough.

API Testing

API testing helps shift left functional and security testing by removing the dependence on brittle and time-consuming UI tests. The API layer is where much of the application functionality resides and testing is more resilient to change at this level and easier to automate and maintain.

Penetration Testing at the API Level

Penetration testing at the API level to expose SQL injections is possible with tools like Parasoft SOAtest where automated fuzz tests are created from existing functional tests exercise the business logic of the application. Parasoft SOAtest integrates with Burp Suite which is a well-known penetration test tool.

When executing functional test scenarios with Parasoft SOAtest, API calls defined in the test are captured along with the request and response traffic. The Burp Suite Analysis Tool on each test will pass the traffic data to a separate running instance of the Burp Suite application, which will perform penetration testing on the API based on the API parameters it observes in the traffic data, using its own heuristics.

The Burp Suite Analysis Tool will then take any errors found by Burp Suite and report them as errors within SOAtest, associated with the test that accessed the API. Parasoft SOAtest results are reported into Parasoft’s reporting and analytics dashboard. For additional reporting capabilities.

To learn more about this integration, see our previous post on penetration testing. For more information from Portswigger on using Burp for SQL injections, check out their post. Below is a representation of how this integration with Burp works:

Integrating this type of penetration testing into your CI/CD process is an important part of the defense against SQL injections and other types of vulnerabilities.

Penetration and fuzzing are certainly an important process and critical in DevSecOps. However, it poses questions.

• What happens when testing detects security vulnerabilities?
• What happens when a software team discovers much of its user input handling is insecure?
• It certainly needs fixing, but at what cost?

Finding serious security issues at this late stage of development causes serious costs and delays. Prevention and detection are key to shifting security operations further to the left to where it’s cheaper and easier to fix.

Shift the Detection and Elimination of SQL Injections Further to the Left

Adopting a DevSecOps approach to software development means integrating security into all aspects of the DevOps pipeline. Just as teams push quality processes like code analysis and unit testing as early as possible in the SDLC, the same is true for security.

SQL injections could be a thing of the past if teams adopt this approach more broadly. The rise in attacks means it isn’t happening yet. Regardless, let’s outline an approach to prevent SQL injections as early as possible.

Finding and fixing potential SQL injections (and other injection vulnerabilities) pays off big time compared to patching (and apologizing for!) a released application. A single, significant incident, can cost companies $200,000 or more. Many incidents happen to small businesses. A single attack can cause serious financial stress, not to mention potential regulatory issues regarding breach disclosures and protection of PII.

The detect and prevent approach outlined below is based on shifting left the mitigation of SQL injections to the earliest stages of development and reinforcing this with detection via static code analysis.

How to Detect SQL Injections

Detecting SQL injections relies on static analysis to find these types of vulnerabilities in the source code. Detection happens at the developer’s desktop and in the build system. It can include existing, legacy, and third-party code.

Detecting security issues on a continuous basis ensures finding any issues that:

• Developers missed in the IDE.
• Exist in code that predates your new detect-and-prevent approach.

The recommended approach is a trust-but-verify model. Security analysis happens at the IDE level where developers make real-time decisions based on the reports they get. Next, verify at the build level. Ideally, the goal at build level isn’t to find vulnerabilities. It’s to verify that the system is clean.

As an example, consider Parasoft’s demo application, Parabank. There is a potential SQL injection in the file StockDataInserter.java in com.parasoft.parabank.dao.jdbc.internal:

…
final String sql = sb.toString();
rows = (nextId - lastId) / JdbcSequenceDao.OFFSET;
totalRows += rows;
getJdbcTemplate().update(sql);
…

The report generated at build time by Parasoft Jtest is the following:

In the detail is the following warning:

Call to a dangerous method
StockDataInserter.java (96): getJdbcTemplate().update(sql); *** Tainted data: SQL

With a traceback to a previous point where the source-tainted data (uncheck, unvalidated input from outside the application) is found:

Tainting point
StockDataInserter.java (47): return getJdbcTemplate().query(SQL, new 
ResultSetExtractor<List<String>>() { *** Tainted data: 
getJdbcTemplate().query(SQL, new ResultSetExtractor<List<Str...return 
symbols; } })

In the ongoing battle against SQL injections, developers need to take these warnings seriously. Any use of unvalidated data in SQL queries is a serious risk. Even if a specific warning might not be an issue in the current form, it’s possible that later refactoring can expose these vulnerabilities. Check all data used in query strings!

In fact, developers should validate any data from outside an application to ensure it conforms to the expected format and content. Moving to a philosophy of “always validate” and a process that relies on secure coding rather than security testing greatly increases your application security. Start hardening the code to prevent SQL injections from happing in the first place.

When & How to Prevent SQL Injection

The ideal time and place to prevent SQL injections are when developers are writing code in their IDE. Teams that are adopting secure coding standards such as SEI CERT C for C and C++ and OWASP Top 10 for Java and .NET or CWE Top 25, all have guidelines that warn about unvalidated input into SQL queries.

Running static analysis on newly created code is fast and simple and easily integrated into the CI/CD process. It’s good practice to investigate any security warnings and unsafe coding practices at this stage to prevent this code from ever making it into the build.

An equally important part of detecting poor coding practices is how useful the reports are. It’s important to be able to understand the root cause of static analysis violations in order to fix them quickly and efficiently. This is where commercial tools such as Parasoft’s C/C++test, dotTEST, and Jtest shine.

Parasoft’s automated testing tools give full traces for warnings, illustrate these within the IDE, and collect build and other information on a continuous basis. This collected data alongside test results and metrics provides a comprehensive view of compliance with the team’s coding standard. It also shows the general quality and security status.

The reports include the risk models that are part of the information provided by OWASP, CERT, and CWE. That way, developers better understand the impact of the potential vulnerabilities reported by the tool and which of these vulnerabilities to prioritize. All the data generated at the IDE level correlates with the downstream activities outlined above.

Summary

The infamous SQL injection vulnerability continues to plague web applications. Despite knowledge of how it works and can be exploited, it remains prevalent. See the IoT Hall of Shame for recent examples.

We propose a prevent and detect approach to compliment active security testing. This approach prevents SQL injections as early as possible in the SDLC — before writing into the code. Preventing SQL injections at the IDE and detecting them in the CI/CD pipeline is key to routing them out of your software. Lastly, find and fix these bugs during testing using penetration testing techniques.

The battle against SQL injections (as well as other tainted data exploits) still wages on. Smart teams can turn the tide with the right process, tools, and automation in their existing workflows.