Error-Based SQL Injection: Examples and 5 Tips for Prevention

What Is Error-Based SQL Injection?

Error-based SQL injection is an In-band injection technique that enables threat actors to exploit error output from the database to manipulate its data. It manipulates the database into generating an error that informs the actor of the database’s structure.

In-band injection enables threat actors to utilize one communication channel to launch an attack and retrieve data. It requires using a vulnerability to force data extraction. Typically, the vulnerability allows code to output an SQL error from the server instead of the required data. This error enables the actor to understand the entire database structure.

In this article:

• Error-Based SQL Injection Examples
  • Simple Example: Adding SQL Syntax to User Input
  • Advanced Example: Running Proprietary Query for Database Vendor
• Error Based SQL Injection Prevention
  • Prepared Statements
  • Stored Procedures
  • Principle of Least Privilege
  • Allowlist Input Validation
• Preventing SQL Injection with Bright Security

Error-Based SQL Injection Examples

Simple Example: Adding SQL Syntax to User Input

In error-based SQL injection, the attacker tries to insert a malicious query with the goal of receiving an error message that provides sensitive information about the database.

The attacker might try any type of SQL command in an input field parameter—such as a single quote, double quote, or SQL operators like AND, OR, NOT.

This example shows a URL that accepts a parameter from the user, in this case the required item:

https://example.com/index.php?item=123

The attacker can try adding a single quote at the end of the parameter value:

https://example.com/index.php?name=123’

If the database returns an error like this, the attack succeeded:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘‘VALUE’’.

This error message provides the attacker with:

• Information about the database used—MySQL
• The exact syntax that caused the error—single quote
• Where the syntax error occurred in the query—after the parameter value

For an experienced attacker, this is enough to see that the server is connected to the database insecurely and plan additional SQL injection attacks that can cause damage.

The attacker can also easily automate this using a command like grep extract to try many SQL syntax options in an input parameter and see which ones return errors.

Related content: Read our guide to SQL injection payloads

Advanced Example: Running Proprietary Query for Database Vendor

If the attacker knows the vendor and version of the database engine, they can try more advanced techniques. Using vendor-specific queries, they can extract specific data that can help them plan additional attacks.

Consider the same URL as above, where the attacker knows that the database is Oracle 10g. The attacker can provide a parameter value like this:

https://example.com/index.php?item=123||UTL_INADDR.GET_HOST_NAME( (SELECT user FROM DUAL) )--

The attacker appends a special query to the parameter value. This query combines two elements:

• The UTL_INADDR.GET_HOST_NAME() function, which returns the hostname on an Oracle 10g database.
• The SQL query SELECT user from DUAL. The DUAL table is a reserved table that exists in any Oracle database. 

The combination of these two elements will result in an error, because the host name will not equal any of the user names in the DUAL table. The error will look something like this:

ORA-292257: host DAVID unknown

As soon as the attacker receives this error, they can continue passing different values to the 

Then the tester can manipulate the parameter passed to the GET_HOST_NAME() function, to discover the names of users, data from other tables, or to eventually guess the database hostname.

Another variation on this attack is to try multiple special queries for different databases and see which one “hits home”—this can reveal to the attacker which database engine the website is using.

Related content: Read our guide to SQL injection attacks

5 Tips for Error-Based SQL Injection Prevention

1. Prepared Statements

Using prepared statements with variable bindings is the most secure way to write database queries. It is also easier for programmers to work with than dynamic queries. In a parameterized query, the developer must first define all the SQL code and then pass each parameter to the query. 

This coding style creates a separation between code and data, regardless of user input provided, which prevents almost all SQL injection attacks. Prepared statements prevent an attacker from changing the intent of a query, even if a malicious SQL command is injected into user inputs.

In rare cases, prepared statements can negatively affect performance. If this is a problem, you can use one of the other prevention methods described below, but be aware that they are less secure.

2. Stored Procedures

Stored procedures are another way to prevent SQL injection. When used correctly, stored procedures can completely eliminate SQL injection vulnerabilities.

Stored procedures require developers to write SQL statements which are automatically parameterized. The difference between prepared statements and stored procedures is that the SQL code for the stored procedure is defined and stored in the database itself, then called by the application.

3. Principle of Least Privilege

Applications must allow each process or software component to access and affect only the resources it needs. Apply privileges based on actual needs—for example, an application should only have permission to access the database if it actually needs it, and should not have write or delete permissions if it only needs to read data from the database. This can mitigate many of the risks associated with injection attacks.

It is quite rare for applications to change database structure at runtime. In most cases, operations like creating or dropping tables, or modifying columns in a table, are performed during release windows and not during runtime. 

Therefore, it is best to reduce the application’s privileges at runtime to edit only the specific data it needs, but not change the table structure. For SQL databases, this means that the application’s service account can only execute DML statements, not DDL statements.

4. Allowlist Input Validation

Some parts of a SQL query do not permit the use of bind variables (such as table and column names) or sorting indicators (such as ASC or DESC). Use input validation or query redesign to prevent these types of illegal inputs. Whenever table or column names are used, these values should come from the code and not from user inputs.

Whenever user parameters must be used to target table and column names, map the parameter values to an allowlist of valid, expected names, to prevent unexpected user input.

Learn more in our detailed guide to blind sql injection.

5. Use Dynamic Application Security Testing (DAST)

DAST scanning can help detect SQL injection from the attacker’s perspective, attempting to exploit running applications with malicious inputs, and identifying severe vulnerabilities. 

Bright Security is a next-generation dynamic application security testing (DAST) solution which helps automate the detection and remediation of SQLi early in the development process, across web applications and APIs. 

By shifting DAST scans left, and integrating them into the SDLC, developers and application security professionals can detect vulnerabilities early, and remediate them before they appear in production. Bright Security completes scans in minutes and achieves zero false positives, by automatically validating every vulnerability. This allows developers to adopt the solution and use it throughout the development lifecycle. 

Scan any web app, or REST, SOAP and GraphQL APIs to prevent SQL injection vulnerabilities – try Bright Security free.

Subscribe to Bright newsletter!