Preventing SQL Injection Attacks

SQL Injection

A SQL injection is a serious vulnerability affecting applications that use SQL as their database language. Through cleverly constructed text inputs that modify the backend SQL query, threat actors can force the application to output private data or respond in ways that provide intel. SQL injections attacks can ultimately be used to steal information and even take complete control of a system.

A login form with "lorenzo_33" as the username and "password'; DROP TABLE Accounts;--" as the password.

Types of SQL Injections

SQL injections can be broken into multiple types depending on how information is retrieved: union-based, error-based, boolean-based, time-based, and out-of-band injections.

Mitigating SQL Injection Attacks: Input Sanitization

One way SQL injections can be mitigated is through input sanitization. Sanitization is the process of removing dangerous characters from user input.

Dangerous characters might include:

’
;
\--

This is important because they allow attackers to extend SQL queries to gain more information from a database.

Careful, this method is not the perfect defense against SQL injections. Removing characters may have no effect in some queries and, if an attacker finds a way to bypass the sanitization process, they can easily inject data into your system.

SELECT username, email FROM users WHERE id = '1' AND '1' = '2';

Union-Based Injections

Uses the UNION SQL keyword to take two separate SELECT queries and combine their results. Consider the user input:

soap' UNION SELECT username,password,NULL FROM user_table;-- -

Being added to the query:

query = "SELECT product_name, product_cost, product_description FROM product_table WHERE product_name = " + USER_INPUT + "'";

Results in:

SELECT product_name, product_cost, product_description FROM
product_table WHERE product_name = 'soap' UNION SELECT username,password,NULL FROM user_table;-- -';

This resulting query would expose all the usernames and passwords of the users!

Error-Based Injections

An attacker writes a malicious SQL query to force the application to return an error message with sensitive data. The inside statement of the following SQL query gets the password for the profile ID 1 but throws an error on the value type that should be returned. This error accidentally gives away the password!

SELECT user_id FROM users WHERE username='asdf' UNION select 1, exp(~(select*from(SELECT Password FROM profiles WHERE ID=1)x)); -- -

Boolean-Based Injections

An attacker takes note of the difference in web responses after sending SQL queries that result in either TRUE or FALSE. Depending on the result, the HTTP response will change or stay the same. Even though no data is returned from the database, the attacker can gain insight into the database (figure out table names) and eventually build up for a Union-based injection.

SELECT username, email FROM users WHERE id = '1' AND '1' = '1';

Time-Based Injections

Makes use of several built-in SQL functions, such as SLEEP() and BENCHMARK(), to cause visible delays in an application’s response time. Like boolean-based injections, this is also used by an attacker to infer information about the database.

Consider the SQL query in the code block. If there’s a 5-second delay before a response from the server, an attacker can confirm the admin user’s password is P@ssw0rd123.

SELECT id FROM users WHERE username = 'a' OR IF((SELECT password FROM users WHERE username='admin')='P@ssw0rd123', SLEEP(5), NULL);-- -';

Out-of-Band SQL Injections

Rare and difficult injections to execute for an attacker because the attacker is unable to use the same channel to send the SQL query and gather results. Generally, these SQL injections will cause the database server to send HTTP or DNS requests containing SQL query results to an attacker-controlled server. From there, the attacker could review the log files to identify the query results.

An image showing that the attacker injections a malicious SQL command, then the web server processes the injected command, then the database server sends an outbound request to the listening server. Finally, the attacker collects the captured information.

Mitigating SQL Injection Attacks: Prepared Statements

One way SQL injections can be mitigated is through prepared statements. With prepared statements, the query we want to execute is provided to the database in advance. Any input is then treated as a parameter and will not be treated as SQL code.

First, a SQL query template is sent to the database. Certain values, called parameters, are left unspecified. For example, user input.
The database processes the query and performs optimizations.
Values are bound to the parameters and the SQL query is executed.

This method is a nearly foolproof and reliable solution to SQL injections.

$username= $_GET['user']; // Set parameter
$stmt = $conn->prepare("SELECT * FROM Users WHERE name = '?'"); // Prepare statement
$stmt->bind_param("s", $username); // Bind parameter to SQL query
$stmt->execute(); // Execute the SQL query