SQL injection (SQLi) is a web security vulnerability that allows an attacker to interfere with the queries that an application makes to its database.
-
Impacts
- Unauthorized access to sensitive data such as passwords, credit card details and personal user information.
- Reputational damage and regulatory fines.
- Possibility of attacker gaining a persistent backdoor into an organization’s systems.
-
What parts of a
SQL query
can be injected?- Common in
WHERE
clause ofSELECT
statement. - In
UPDATE
statements, within the updated values or theWHERE
clause. - In
INSERT
statements, within the inserted values. - In
SELECT
statements, within the table or column name. - In
SELECT
statements, within theORDER BY
clause
- Common in
-
Detection mechanisms
- Submit a single quote character
'
and look for errors and other anomalies. - Boolean conditions such as
OR 1=1
orOR 1=2
- Payloads designed to trigger time delays such as
' OR '1'='1' AND SLEEP(5) --
- Comments with
--
at the end of an injection.
- Submit a single quote character
-
Examples
-
Retrieving hidden data
Imagine a shopping application that displays products in different categories. When the user clicks on the Gifts category, their browser requests the URL:
https://insecure-website.com/products?category=Gifts
and causes the application to make a SQL query to retrieve details of relevant products from the data base as:
SELECT * FROM products WHERE category = 'Gifts' AND released = 1
This is vulnerable to SQL injection as follows:
-- Payload = Gifts'-- shows gifts released or not SELECT * FROM products WHERE category = 'Gifts'--' AND released = 1 --Payload = Gifts' OR 1=1-- shows all items released or not SELECT * FROM products WHERE category = 'Gifts' OR 1=1--' AND released = 1
CAUTION
: When injecting the conditionOR 1=1
it’s common for applications to use data from a single request in multiple different queries. So if the condition reaches anUPDATE
orDELETE
statement, it can result in accidental data loss. -
Subverting application logic
Imagine an application that lets users login with a username and password. The application checks the credentials by performing an SQL query as follows and if the query returns details of the user, login is successful else it is rejected.
SELECT * FROM users WHERE username = 'wiener' AND password = 'bluecheese'
This is vulnerable to injection as follows:
/*payload = administrator'-- allows the attacker to log in as any user without need for password*/ SELECT * FROM users WHERE username = 'administrator'--' AND password = ''
This query returns user whose
username
isadministrator
without the need for a password. -
UNION attacks
Imagine an application executes the following query containing the user input
Gifts
:
SELECT name, description FROM products WHERE category = 'Gifts'
This is vulnerable to injection as follows:
UNION SELECT username, password FROM users-- --The malicious SQL query would then be: SELECT name, description FROM products WHERE category = '' UNION SELECT username, password FROM users--
This causes the application to return all usernames and passwords along with the names and descriptions of the products.
-
Blind
SQL
injectionMost
SQLi
are blind in nature but can still be exploited to gain unauthorized data. Some techniques are:a. Divide by zero:
SELECT * FROM users WHERE username = '' OR 1=1 AND 1/0 --' AND password = '';
b. Triggering time delays with
SLEEP()
-
-
Prevention against SQL Injection using Prepared Statements or Parameterized Queries
- They make SQL injections virtually impossible.
- Prepared statements separate code from data by ensuring that user-supplied input does not alter your program’s logic.
- Prepared statements are compiled by the SQL server before adding user input.
- Anything that is not part of the prepared statements is treated as string data and not as an executable SQL query.
// Create MySQL server and store username and password in variables. $mysqli = new mysqli("mysql_host", "mysql_username", "mysql_password", "database_name"); $username = $_POST["username"]; $password = $_POST["password"]; // Vulnerable SQL statement $vuln = "SELECT Id FROM Users WHERE Username='$username' AND Password='$password'"; $result = $mysqli->query($vuln); // Safe prepared statement $stmt = $mysqli->prepare("SELECT Id FROM Users WHERE Username =? AND Password =?"); $stmt->bind_param("ss", $username, $password); $stmt->execute(); // Unsafe prepared statement $stmt = $mysqli->prepare("SELECT Id FROM Users WHERE Username='$username' AND Password=password'"); $stmt->execute();
Top comments (4)
Hi.
It's also important to sanitize input before using it.
Type hinting can also help.
ps: small typo in
UNION attack
:WHERE cateogry = ''
should beWHERE category = ''
I'm planning on updating the article with more prevention mechanisms in the future. I wouldn't have noticed the typo on my own. Thank you so much!
Useful! Thanks
I'm glad you liked it!