Guarding the Gates: How to Defend Against SQL Injection Attacks

SQL injection is a critical security vulnerability that allows attackers to manipulate database queries by injecting malicious SQL code. This allows the attacker to access data that they are not supposed to.

Here's an overview of SQL injection based on information from OWASP:

What is SQL Injection?

SQL injection is a code injection technique that exploits vulnerabilities in the way applications interact with databases. It occurs when user-supplied data is inserted directly into SQL queries without proper sanitization or validation. A successful SQL injection attack can allow attackers to:

• Read sensitive data from the database
• Modify database data
• Execute administration operations on the database
• Issue commands to the operating system
• Spoof identity
• Tamper with existing data
• Cause repudiation issues like voiding transactions
• Destroy or make data unavailable

What can be impacted

The severity of SQL injection attacks can be very high, they have been the tactic in many high-profile data compromises over the years. An event like that not only results in regulatory fines and reputational damage but in some scenarios, the attackers have obtained a persistent backdoor into the organization's systems leading to long-term compromise with devastating implications. A successful SQL injection attack can result in access to:

• Usernames and passwords
• Credit card and banking details
• Personal Identifiable Information (address, SSN, DOB...)
• Read, modify or delete sensitive data

How does SQL Injection Works

SQL injection typically happens when an application uses user input to construct SQL queries dynamically. These are in place to interact with the backend database to retrieve information therefore the common targets are database-driven websites. The flaw is easy to discover and easy to exploit.

Most SQL injection vulnerabilities occur within the WHERE clause of a SELECT query. However these can occur at any location within the query, and within different query types. Some common SQL injection examples include:

• Retrieving hidden data, where you can modify a SQL query to return additional results.
• Subverting application logic, where you can change a query to interfere with the application's logic.
• UNION attacks, where you can retrieve data from different database tables.
• Blind SQL injection, where the the injection attack asks the database true or false queries.

SELECT * FROM users WHERE username = 'administrator'--' AND password = ' '        

This query returns the user whose username is administrator and successfully logs the attacker in as that user.

The UNION keyword to retrieve data from other tables within the database. This is commonly known as a SQL injection UNION attack. In this instance the attacker needs to know what are the names of the tables and what columns they contain. For example to access stored usernames and passwords in different tables, the following Union query could be used:

SELECT id, username FROM users UNION SELECT id, password FROM creds        

For example, suppose there is a table called "users" with the columns "username" and "password", and a user called "admin". You can determine the password for this user by sending a series of inputs to test the password one character at a time.

SUBSTRING((SELECT password FROM users WHERE username = 'admin'), 1, 1) > 'j        

If this returns an OK response, this indicates that the first character is greater than "j", and we will try with the next letter in the sequence. When the query does not return OK response, this indicates that the previous letter is the first character of the password.

This can be continued to determine the full password for the "admin" user.

SUBSTRING((SELECT password FROM users WHERE username = 'admin'), 1, 1) > 'pd        

Preventing SQL Injection

According to the OWASP SQL Injection Prevention Cheat Sheet, there are three primary methods to mitigate SQL injection vulnerabilities:

1. Use of Prepared Statements (with Parameterized Queries): Prepared statements force developers to define all SQL code first and pass in each parameter to the query later. This approach ensures that the database always distinguishes between code and data, regardless of user input.
2. Use of Properly Constructed Stored Procedures: Stored procedures can be as effective as prepared statements in preventing SQL injection if implemented safely. The SQL code for a stored procedure is defined and stored in the database itself, then called from the application.
3. Allow-list Input Validation: This method is particularly useful for parts of SQL queries that can't use bind variables, such as table or column names. It involves validating user input against a set of allowed values before including it in a query.

SQL injection remains one of the most critical web application vulnerabilities. By understanding how these attacks work and implementing proper defenses, developers can significantly reduce the risk of SQL injection in their applications. Prepared statements and parameterized queries are the most effective defenses and should be used wherever possible.

https://owasp.org/www-community/attacks/SQL_Injection

https://portswigger.net/web-security/sql-injection/cheat-sheet