SQL Injection

SQL Injection

This article shows you another popular attack on websites and APIs today with relevant simple examples. Furthermore, you can learn how to identify and how to prevent SQL Injection attacks.

What is SQL Injection

SQL injection is a Web security vulnerability that facilitates an attacker to modify the SQL queries that an application sends to the database. Basically it allows an attacker to modify and view data that he will not be able to access such as passwords, credit card details, or personal user information. Otherhand it can be used for stressing the database sending complex queries or search all queries. Then it causes unavailable the application for the specific time and it compromises the infrastructure as well.

SQL | Injection | Attack | Hacker | SQLi

Types of SQLi attack

There are many types of SQLi as below

  • In-band SQLi
  • Inferential (Blind) SQLi
  • Out-of-band SQLi

Examples of SQLi attacks

Showing Hidden data

Basically, the attacker adds a boolean to the SQL query or comment conditions to view the data that he won’t be able to access. Consider an online shopping site that has a button to display the available discount as below.

https://notsecured-shopping-site.com/discounts?id=1

This is basically retrieve the discount value from the database for the given id as below.

SELECT * FROM discounts WHERE id=1 LIMIT 1;

In this case, your application can be victimized to SQLi attacks since it does not implement any defense against the SQLi. Then the attacker may construct a query as below.

https://notsecured-shopping-site.com/discounts?id=1%20OR%201=1#
SELECT * FROM discounts WHERE id=1 OR 1=1#LIMIT 1; 

This will show all the discount details in the database, likewise, he will be able to see the data that he wants to compromise.

Subverting application logic

In this type of scenario, the attacker will get the login access commenting on the query. Let’s say a user is bhanu92 and the password is 123456 but the attacker doesn’t know the password. The query as below,

SELECT * FROM users where username= 'bhanu92' AND password = '123456';

But he will submit a form with the user name as bhanu92”’#, then the query will be modified as below,



Another example –

SQL | Injection | Attack | Hacker | SQLi

SELECT * FROM users where username= 'bhanu92'''# AND password = '123456';

Then this query bypasses the password condition and it also returns the same user object hence attacker may access the application.

View data from other tables

When the application is returning a response the attacker may append the UNION keyword in SQL to get the data of another table which not belongs to the current query. for example, my application is trying to get discount value from the database as below,

https://notsecured-shopping-site.com/discounts?id=1
SELECT type,value FROM discounts WHERE id =1

Then the attacker will modify it as below,

https://notsecured-shopping-site.com/discounts?id=1%20UNION%20SELECT%20username,password%20from%20users

Please note – decode above URL using this link. Then you can see the injection query, once it comes to application side the query will be shown below,

SELECT type,value FROM discounts WHERE id =1 UNION SELECT username,password from users;

Examining the database

Every attacker’s very first time uses this technique to see the structure of the database since it will help to attack the database. The successful attack will be able to see the list of tables, column names, and databases in the schema. The injection as below,

SELECT * FROM information_schema.tables
SELECT type,value FROM discounts WHERE id =1 UNION SELECT table_schema, table_name FROM information_schema.tables

How to find SQLi vulnerabilities in the application

In my experience, I used some commercial tools like Appspider (for dynamic code analysis) and Checkmarx (for static code analysis) to identify the vulnerabilities in my applications. Please read them on how to create a scheduler to be scanned your applications.

Without the help of the external application, you can do manual testing for every endpoint in your application.

  • Using some boolean conditions like OR 1=1, OR 1=2 likewise, and compare the correct response and injected response.
  • Using some special characters like ‘ (single quote) to determine errors in the response, most of the time error give the information about the table structure.
  • Use SQL syntax and compare the responses.
  • Use some complex queries and trigger the time delays.

Most of the time attackers use WHERE clause to change the SQL queries. As well as other major operations like INSERT, DELETE, UPDATE queries can be attacked by attackers.

Preventing SQL injection

As per my experience, the best option is using parameterized (Prepared Statements) queries we can prevent from SQLi attacks. It provides better performance rather than using a Statement since the pre-compilation of SQL queries on the database server. If you use Prepared Statements it only compiles the first time and caches to the database. Below is the statement example.

String query = "SELECT * FROM discounts WHERE id = '"+ input_value + "'";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);

As you can see the attacker will be able to change the input value, to prevent it, we can use Prepared statement as below.

PreparedStatement statement = connection.prepareStatement("SELECT * FROM discounts WHERE id = ?");
statement.setString(1, input_value);
ResultSet resultSet = statement.executeQuery();

If you are using Prepared statements please use queries as final static it is a best practice.

public static final string QUERY = "SELECT * FROM discounts WHERE id = ?";
PreparedStatement statement = connection.prepareStatement(QUERY);
statement.setString(1, input_value);
ResultSet resultSet = statement.executeQuery();

Please note above everything I have written in my personal experiences and you can learn about more security using these articles XSS and CSRF.