3 ways of stealing data using SQL injection
Understand the attack logic so as to prevent it
Seeing your companies private data all over the internet is not a happy sight to see. Yes, you get free publicity but not in a positive sense. Building an application takes a lot of work but without security, it acts as a door without a lock, anyone can come in as they please.
SQL injection is one of the most common types of attacks used to hack a web application and yet many of us still know very little about it and how it works. It is a web security vulnerability, attackers use by interfering with the queries that an application makes to its database resulting in a data breach.
What Creates SQL Vulnerability?
As we will later see through multiple examples the main cause that creates such a harmful nitch for hackers is due to dynamic SQL query generation. It sounds like something complex but it simply means to create SQL query through concatenation.
var Query = "SELECT * FROM users WHERE username = " ${user} "AND password =" ${password};
If you have written something similar to the above example in your application then it is highly likely that your application is susceptible to an attack.
This SQL statement is passed to a function that sends the string to the connected database where it is parsed, executed and returns a result. But before all that, the query itself is generated within the application.
Now let’s dive in and learn through examples, how dynamically generating queries, as shown above, can impose serious risks to your data.
1. Retrieving Hidden Data
Suppose we have a shopping application selling a verity of items with one category being “watches”. Clicking on it triggers an action to request the URL:
https://shopingspot.com/products?category=watches
This causes the application to make an SQL query to the database:
SELECT * FROM products WHERE category = 'watches' AND released = 1
This query retrieves data as:
- all details (*)
- from the products table
- where the category is
watches
- and released is 1.
The restriction released = 1
is being used to hide products that are not released. For unreleased products, presumably released = 0
.
The application doesn’t implement any defenses against SQL injection attacks, so an attacker can construct an attack like:
https://shopingspot.com/products?category=watches'--
This results in the SQL query:
SELECT * FROM products WHERE category = 'watches'--' AND released = 1
The key thing here is that the double-dash sequence --
indicating a comment in SQL. Anything after it is simply interpreted as a comment.
This effectively removes the remainder of the query, so it no longer includes AND released = 1
. This means that all products are displayed, including unreleased products.
Going further, an attacker can cause the application to display all the products in any category, including categories that they don’t know about:
https://shopingspot.com/products?category=watches'+OR+1=1--
This results in the SQL query:
SELECT * FROM products WHERE category = 'watches' OR 1=1--' AND released = 1
The modified query will return all items where either the category is watches
, or 1 is equal to 1. Since 1=1
is always true, the query will return all items.
2. Subverting application logic
Consider a simple bank application that lets users log in with an email and password. Only by adding a proper email and a valid password for the username can someone access any information related to the bank account.
Our emails are public but our password is not something that we share. An application usually generates a query as shown below for a given username and password which let's assume is weilder@gmail.com
andbluerose
.
SELECT * FROM users WHERE email = 'weilder@gmail.com' AND password = 'bluerose'
Here, an attacker can log in as any user without a password simply by using the SQL comment sequence --
to remove the password check from the WHERE
clause of the query. For example, submitting the email weilder@gmail.com'--
and a blank password results in the following query:
SELECT * FROM users WHERE email = 'weilder@gmail.com'--' AND password = ''
This query returns the user whose email is weilder@gmail.com
and successfully logs the attacker in as that user with all the access to your bank account that you have.
3. Retrieving data from other database tables
An attacker may leverage an SQL injection vulnerability to retrieve data from other tables within the database. This is done using the UNION
keyword, which lets you execute an additional SELECT
query and append the results to the original query.
For example, if an application executes the following query containing the user input watches
:
SELECT name, description FROM products WHERE category = 'watches'
then an attacker can submit the input:
' UNION SELECT username, password FROM users--
This will cause the application to return all usernames and passwords along with the names and descriptions of products.
Some final words
I hope this gave you a better understanding of how SQL injection works and how hackers misuse them to gain data access and privileges in an application that normally should not be allowed. And please don’t go and hack away at random applications online. It might lead you to unwanted troubles.
Demo: https://www.hacksplaining.com/exercises/sql-injection#/hack-complete