SQL injection

Overview

An SQL injection vulnerability allows an attacker to insert unwanted code into a SQL query. In many cases, an attacker can modify, add and delete data to the database. For example, he could get all username and passwords of a given website.

In some cases, even the underlying server can be compromized, allowing the attacker to gain information, not only limited to the database.

Example

An unsecure site might contain an API endpoint to get all products of the category gifts. This endpoint looks like this:

https://insecure-website.com/products?category=Gifts

This results in the database being queried:

SELECT * FROM products WHERE category = 'Gifts' AND released = 1

This query ensures that only released products will be shown to the user. However there is a flaw with this approach. An attacker could just enter the ' character to escape this:

https://insecure-website.com/products?category=Gifts'--

This will result in the following query:

SELECT * FROM products WHERE category = 'Gifts' --' AND released = 1

This query will return all products, no matter if released or not, thus skipping this security check.

Furthermore, an attacker can query the database to display all products, not only from the Gifts category. This also includes categories, that an attacker might not even know about:

https://insecure-website.com/products?category=Gifts'+OR+1=1--

This will result in the following query:

SELECT * FROM products WHERE category = 'Gifts' OR 1=1--' AND released = 1

Since 1=1 will always be true, the query will return all items in the products table.

Furthermore

After discovering an SQL injection, it is generally helpful to gain more information about the database itself, or even the system its running on. This information can often pave the way for further exploitation on the system.

In almost every database, you can query the version of the database. The query might vary for other databases.

Query the database version on oracle:

SELECT * from v$version

Query a list of all the tables in a database

SELECT * FROM information_schema.tables

Preventing SQL injections

Most SQL injection's can be prevented using parameterised queries instead of just accepting the string. This is done with a prepareStatement() function, that creates the query before running it. Some placeholder values are stored which allows the query to saveloy input the data into the database.

PreparedStatement statement =
connection.prepareStatement("SELECT * FROM products WHERE category = ?");

statement.setString(1, input);

ResultSet resultSet =
statement.executeQuery();