How to prevent sql injection

How to prevent SQL Injection

Reading Time: 10 minutes

SQL (Structured Query Language) Injection is a well-known, if not one of the most well-known, software vulnerabilities and security holes. Despite its reputation, preventing SQL injection remains one of the biggest security vulnerabilities Attacks continue to rise.

Search SQL injections

Injection vulnerabilities (of which SQL injections are a variant) are top 10, according to OWASP. SQL injections are number six on the CWE top 25. Other examples of the same types of vulnerabilities include:

  • Command Injection (CWE-77)
  • OS Command Injection (CWE-78)
  • Hibernate Injection (CWE-564)
  • Expression Language Injection (CWE-917)

All these vulnerabilities have a common attribute. They are exploited with data from outside the system, user or file input or whatever in potentially dangerous functions.

Get the free whitepaper: the business value of secure software

Fortunately, SQL injections can be detected by tools both statically and dynamically. However, you can never be sure that you have caught them all. Preventing SQL injections is also key to reducing the frequency and impact of these vulnerabilities. A mature DevSecOps process that includes vulnerability detection and prevention can identify these types of vulnerabilities and prevent them from ever making it into a released product.

What is SQL?

SQL is a domain-specific language for managing 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 the Users table:

[Table "1" not found /]

SQL is the language of choice for managing, querying, and editing data in a relationship database. It defines the tables and relationships in database creation. For most everyday applications, developers use SQL for "CRUD" – to create, read, update and delete data.

Why SQL is exploitable?

General programming languages do not support SQL. Database commands are accessed through an API provided by the database vendor. In many cases, SQL commands are sent as strings that are interpreted by the API and applied to the database. Here are some simple SQL queries:

A typical SQL query has the following form:

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

The output would be as follows:

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

Simple web form

The software processes the form and assigns values to variables as follows:

The as "Name" is used to compose the query using this user input:

Using this constructed query:

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

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

Using the form above, someone enters "[email protected]" or "1" ="1" into the email field.

The same code is used to compose the following SQL query string:

adding something innocuous like "or 1 = 1" changes the logic of the query and possibly leads to data leakage by changing all rows in the table named "Users" be returned. In this case you will see messages for each user in the table. A serious privacy issue, and in some jurisdictions or contexts, a potential legal issue as well, such as. B. GDPR, HIPAA, or CCPA.

The above query ends with the following unintended output:

This is how an SQL injection works

The basic idea of an SQL injection (and other types of injection vulnerabilities) is to use unverified data from outside the application, z. B. User input text, in an SQL query string. The description for KWE 89: "Improper neutralization of special elements used in a SQL command (‘SQL Injection’)." defines this in more detail:

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

The same entry in the CWE database (CWE 89) provides another simple example of this attack. Suppose the application runs a query on behalf of the user "wiley and the user creates the input to contain SQL statements, for example:

If this application does not validate this input, it will create a query like this:

If this attack is successful, all data in table elements is deleted, which devastates the database. Any valid SQL command could possibly be executed this way. This is an example of a write/modify attack designed to corrupt the database or insert unwanted information. The previous example ("or 1 = 1") is a read attack where the intent is data loss.

Many database server implementations accept the semicolon as a command separator, which is how such SQL injections can become so dangerous. The following"-" signals that the rest of the text is a comment, forcing the SQL interpreter to ignore the trailing quote, which would otherwise cause a syntax error. There are several ways to trick the assembled query string. Sometimes in ways developers never imagined.

Safeguards against SQL injections

There are several workarounds that developers should implement. First and foremost, the security posture should consider any data that comes from outside the application and cannot be trusted. The following are typical mitigation strategies:

  • Use prepared statements with parameterized queries.
  • Use stored procedures.
  • Whitelist input verification.
  • Escape all provided input.

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

Testing for SQL injections

A typical security approach is to perform various types of security tests as part of regular QA operations when the integrated software is running. Unfortunately, functional tests do not attempt to inject exploits into user input fields, as most testers do not think of themselves as bad actors.

Aside from the fact that they traditionally don’t have the time or direction to do this. It is also difficult to manually test for injection type vulnerabilities because so many different combinations of inputs must be tried. This is where fuzzing or fuzz testing comes into play. Random, unexpected and invalid data are created as inputs to the application under test. Fuzz testing is part of penetration testing because the goal is to expose vulnerabilities through the exposed interfaces.

Penetration testing

Penetration testing (and by extension, fuzz testing) is beneficial because it can detect security issues that have crept through the process and expose significant security problems. However, as with all dynamic testing, it depends on the amount of test, code, and API coverage to fully test all possible permutations and combinations. Penetration testing depends on the thoroughness of functional testing, which is typically done at the user interface level. For this reason, it is important to support your penetration testing efforts with API testing and SAST to ensure you are thorough.

API tests

API testing helps move functional and security testing to the left by eliminating reliance on brittle and time-consuming UI testing. The API layer is where much of the application functionality resides, and testing is more resilient to changes at this level and easier to automate and maintain.

API level penetration testing

API-level penetration testing to make SQL injections available is possible with tools such as Parasoft SOAtest When automated fuzz tests are created from existing functional tests, the application’s business logic is applied. Parasoft SOAtest integrates with Burp Suite This is a well-known penetration testing tool.

When running functional test scenarios with Parasoft SOAtest, API calls defined in the test are captured along with request and response traffic. For each test, the Burp Suite analysis tool forwards the traffic data to a separate running instance of the Burp Suite application, which performs penetration tests on the API using its own heuristics based on the API parameters observed in the traffic data.

Burp Suite analysis tool then takes any errors found by Burp Suite and reports them as errors in SOAtest associated with the test that accessed the API. Parasoft SOAtest results are reported in Parasoft’s reporting and analysis dashboard. For additional reporting capabilities.

For more information on this integration, see our previous post on penetration testing. For more information from Portswigger on using Burp for SQL injections, see Check out their post. Below is an illustration of how this integration works in Burp:

Prevent SQL Injections with Burp and Parasoft DTP

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

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

  • What happens when security vulnerabilities are identified during testing?
  • What happens when a software team determines that much of its user input processing is insecure?
  • It certainly needs to be fixed, 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 moving security measures further to the left, where they are cheaper and easier to fix.

Move SQL injection detection and elimination further to the left

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

SQL injections could become a thing of the past if teams adopt this approach more broadly. The increase in attacks means this is not yet happening. 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 compared to patching (and excusing!) From a shared application. A single significant incident can cost organizations $200,000 or more. Many incidents occur at small businesses. A single attack can cause serious financial exposure, not to mention potential regulatory issues related to breach disclosure and personal data protection.

The approach to detection and prevention described below is based on moving mitigation of SQL injections to the left into the earliest stages of development and reinforcing it with detection through static code analysis.

How to detect SQL injections

SQL injection detection is based on static analysis to find these types of vulnerabilities in the source code. Detection occurs on the developer’s desktop and in the build system. It can include existing code, legacy code, and third-party code.

Continuous detection of security issues ensures that all issues are found that:

  • Developers missed in the IDE.
  • Insist on code that is older than your new approach to detecting and preventing them.

The recommended approach is a trust-but-verify model. Security analysis is performed at the IDE level, where developers make real-time decisions based on the reports they receive. Next, check at the build level. Ideally, the goal at the build level is not to find vulnerabilities. The idea is to verify that the system is clean.

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

The report that was created at the time of Parasoft Jtest is the following:

Finding SQL Injections: Parasoft Jtest Report

In detail, the following is a warning:

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

In the ongoing fight against SQL injections, developers need to take these warnings seriously. Using unvalidated data in SQL queries is a serious risk. While a particular warning may not be a problem in its current form, this later refactoring may expose those vulnerabilities. Check all data used in query strings!

Developers should validate all data from outside an application to ensure it matches the expected format and content. Moving to an "always validate" philosophy and a process that relies on secure coding rather than security testing greatly increases application security. Start hardening the code to prevent SQL injections from running in the first place.

When and how to prevent SQL injection?

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

Running a static analysis for newly created code is quick and easy and easy to integrate into the CI / CD process. It is recommended that all security alerts and unsafe coding practices be investigated at this time to prevent this code from ever being included in the build.

Use static code analysis to prevent SQL injections

An equally important part of detecting bad coding practices is the usefulness of the reports. It’s important to understand the root cause of static analysis violations so you can fix them quickly and efficiently. Here you can find commercial tools such as Parasoft’s C / C ++ – Test, dotTESTand Test seem to be.

Parasoft’s automated testing tools provide complete traces for warnings, visualize them in the IDE, and continuously collect build and other information. This collected data, in addition to test results and metrics, provides a comprehensive overview of the team’s coding standard compliance. 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. In this way, developers better understand the impact of the potential vulnerabilities reported by the tool and which of these vulnerabilities need to be prioritized. All data generated at IDE level correlates with the downstream activities described above.


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

We suggest a prevent and detect approach to complement active security testing. This approach prevents SQL injections from being inserted into the SDLC as early as possible – before writing into the code. Preventing SQL injections in the IDE and detecting SQL injections in the CI / CD pipeline is key to redirecting from your software. Last, find and fix these bugs during testing using penetration testing techniques.

The fight against SQL injections (as well as other tainted data exploits) continues. Smart teams can turn the tide with the right processes, tools and automation in their existing workflows.

Like this post? Please share to your friends:
Leave a Reply

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!: