SQL Injection

SQL Injection Explained

SQL Injection is a web security vulnerabity.

It is used by hackers to access and manipulate the database.

This is done by entering malicious data in input fields or on URLs.

SQL Injection is also referred to as SQLi.


Example: Input Field

#

SQL Injection is based on the intruder's understanding how web applications are written, specifically, how dynamic SQL queries are built in the code.

Suppose we have an input field where employees are asked to verify their identity with a unique employee number, like so:

Employee Number   

And this is what the intruder enters:

Employee Number   

The query will now be changed to this.

SELECT FirstName, LastName, Salary
  FROM Employee
 WHERE EmployeeNumber = 78234 OR 1=1

The results are rather dramatic.
This query returns all employee records with their salaries.

Explanation

The query is designed to retrieve a single employee record.

Adding OR 1=1 in the input field will change the SQL query.

Since 1=1 is always true, the query returns all employees and their salaries.

Not cool.


Example: URL

#

Suppose that employees receive a link via email that they can click to quickly access their personal data. This is the link:

https://www.company.com/secure/employeenumber=78234

A malicious employee copies the link and appends OR 1=1, like so:

https://www.company.com/secure/employeenumber=78234+OR+1=1

You can probably already guess what happens when this URL is placed in the browser. The same malicious query is created, returning all employees with their salaries. Again, not cool.


SELECT FirstName, LastName, Salary
  FROM Employee
 WHERE EmployeeNumber = 78234 OR 1=1

SQL Injection Review

SQL injection is a web security vulnerability.

This vurnerability allows the intruder to penetrate the database.

SQL injection refers to the act of 'injecting' malicious code into a SQL query.

The injection can be done from an input field or with a URL alteration.

If successful, an intruder may access, modify, or delete data from the database.


SQL Injection: How to

An intruder often begins by probing the site to find vulnerabilities. This is done by submitting incorrect data, or any other unusual payload, forcing the site to create error messages.

By observing the response, the intruder may discover certain technical details about the server or the database. For example, if the site returns database errors to the user, this may reveal insights into the database structure, such as the database vendor, table names, columns names, data types, and more.

Next, we'll review some attack vectors.


Access hidden data

SQL Injection can be used to retrieve data that is normally inaccessible to users.

Suppose we have an ecommerce website.
In this site, when the user selects a category, the browser requests this URL:

https://company.com/products?category=Computer

This link leads to the following SQL query.

SELECT *
  FROM Product
 WHERE Category = 'Computer' AND Discontinued = 0

It returns all computer products that have not been discontinued.

Next, the intruder alters the URL by appending this: '--, like so.

https://company.com/products?category=Computer'--

Assuming the site does not implement any defense against SQL injection the altered link leads to the following SQL query.

SELECT *
  FROM Product
 WHERE Category = 'Computer'--' AND Discontinued = 0

The double-dash sequence -- is a SQL comment which means that the remainder of the query is ignored. This removes the Discontinued = 0 part. The query will return all products whether they are discontinued or not.

After that, the intruder may decide to display products from any category, including those categories that the user normally does not see, using this link:

https://yourstore.com/products?category=Computer'+OR+1=1--

This leads to the following query:

SELECT *
  FROM Product
 WHERE Category = 'Computer' OR 1=1--' AND Discontinued = 0

Since 1=1 is always true, this query will return a complete list of all products from any category, discountinued or not.


Bypassing login logic

Most web applications have a login form that accept an email and password. When users submit their credentials, for example, "jimmy@gmail.com" and "mypassword", they will be validated with this query.

SELECT *
  FROM Customer
 WHERE Email = 'jimmy@gmail.com' AND password = 'mypassword'

If the query returns a user, the login is successful, otherwise it fails.

Using SQL injection, an intruder may be able to login without a password by appending a SQL comment to the email. They will try to submit "jimmy@gmail.com'--'" with an arbitrary password, which leads to this query:

SELECT *
  FROM Customer
 WHERE Username = 'jimmy@gmail.com'--' AND password = 'mysecret123'

The password clause in this query is ignored because of the comment -- that follows the email value. This query returns a record of user "jimmy@gmail.com" and the intruder is successfully logged in. Of course, this process requires that the intruder knows the user's email.


Retrieving data from other tables

This SQL injection technique uses the UNION keyword. The UNION keyword allows an intruder to execute an additional SELECT query and have the results appended to an existing query.

Say, an application perform this query.

SELECT Email, Name
  FROM Customer
 WHERE Department = 'Marketing'

An intruder may submit the necessary input that effectively adds this query:

  UNION SELECT Email, Password FROM [User]--

This is the resulting query.

SELECT Email, Name
  FROM Customer
 WHERE Department = 'Marketing'
UNION 
SELECT Email, Password 
  FROM [User]--

Executing this query will return a list of customers together with a list of login credentials of all users. Quite devastating.


Obtaining database info

An intruder may try to get information about the database structure from the database.
The following query can be appended to an input value with a UNION keyword.

SELECT * FROM INFORMATION_SCHEMA.TABLES

This would return a list of all tables in the database. Since this is a UNION the intruder will need to ensure the columns match of the first and second query.


Preventing SQL injection

SQL Injection is made possible by the way SQL queries are constructed in the code. If raw user input is used to simply string together a SQL query then this opens the site to SQL Injection attacks. So, how can we avoid this?

Most instances of SQL injection can be prevented by using parameterized queries (also known as prepared statements).  Parameterized queries are SQL queries which accept user input in a more controlled manner by using SQL parameters. A SQL parameter is a placeholder in a query that accepts a user-entered data value.

Here is a SELECT statement with SQL parameters:

var id = GetRequestString("ProductId");
var sql = "SELECT * FROM Product WHERE ProductId = @0";

db.Execute(sql, id);

Parameters are specified in the SQL statement by a @. The SQL engine checks each parameter value and ensures that it is a valid literal and does not contain executable SQL code.

Depending on the language and platform, writing prepared statements will be a bit different. Here is another prepared statement using Entity Framework Core.

var customer = db.Customers.FromSqlRaw(
      "SELECT * FROM Customer WHERE Id = {0}", id).FirstOrDefault();

SQL parameters can be used in any sql statement that contains user input.
Here is an INSERT statement.

context.Database.ExecuteSqlCommand(
    "INSERT INTO Customer (FirstName, LastName) VALUES(@firstName, @lastName)",
    new SqlParameter("@firstName", firstName),
    new SqlParameter("@lastName", lastName)
);

Applications that accept user input but cannot use SQL parameters need to take a different approach. White-listing permitted input values is one possible way.

Another way to prevent SQL Injection is by studying the possible vulnerabilities first and then remove these one by one with custom code. Clearly, this requires great security skills and experience from the development team.


SQL Injection Risk Sheet

This risk sheet lists SQL techniques that are commonly used during a SQL Injection attack. The code examples are specific to SQL Server. For other databases the syntax will different but the fundamentals are the same.


Comments

#

Comments can be used to remove a portion of the original query. SQL Server supports two types of comments.

--

and

/* comment */

String concatenation

#

Multiple strings can be combined to create a single string using concatenation. Intruders use this to their advantage by concatenating strings in unexpected ways.

'value1'+'value2'

Substring

#

The SUBSTRING function extracts part of a string with a specified length. SUBSTRING can hide system functions and be part of SELECT, INSERT, or UPDATE statements. This example will reveal the database and its version.

SELECT SUBSTRING(@@version,1,90)

This will return something like this: 'Microsoft SQL Server 2016 (SP2-GDR) (KB4583460) - 13.0.5103.6 (X64) Nov 1 2020 00:13:28'. Armed with this information the intruder can more precisely attack this database.


Database metadata

#

INFORMATION_SCHEMA is an ANSI standard that returns metadata about a database. This may include information about tables, columns, constraints, indexes, and more. For example, an intruder can obtain table information with this query.

SELECT * FROM INFORMATION_SCHEMA.TABLES

With our sample database this lists all table names:

TABLE_CATALOG TABLE_SCHEMA TABLE_NAME TABLE_TYPE
MySandbox dbo Customer BASE_TABLE
MySandbox dbo Order BASE_TABLE
MySandbox dbo OrderItem BASE_TABLE
MySandbox dbo Product BASE_TABLE
MySandbox dbo Supplier BASE_TABLE

Conditional errors

#

The intruder may use conditional errors to test certain database conditions. If the condition is true, a divide-by-zero error is triggered, validating the test. Here is the SQL.

SELECT CASE WHEN (CONDITION-HERE) THEN 1/0 ELSE NULL END

Batched queries

#

Batched queries is the execution of multiple queries in a single operation (they are batched together). In SQL Server, multiple queries can be batched by placing a ; (semicolon) between the individual queries, like so.

QUERY1; QUERY2; QUERY3

You may also like



Guides