What is SQL Injection Attack? Examples & Prevention

One feature that almost all web applications have in common is the connection to one or more databases. Whether for retrieving E-mails, shopping on the Internet or to read the news: one or even several databases are always behind it. No matter what programming language the web application was written, communication with the database always takes place after the same principle: the server-side script sends the SQL queries to the database, receives the results, processes as needed and finally returns them to the user.

Within just a few hours, attackers are able to compromise an entire Webserver infrastructure. Often, the attackers' gateway is a classic error in a web application: the vulnerability to SQL injection. Not only is sensitive data at risk, the entire server infrastructure can be taken over by attackers. The gap, which has been known for over the years, is today still the most popular target for hackers.

What is SQL injection attack?

SQL injection (SQLi) is a web security vulnerability that allows an attacker to manipulate an application’s backend by modifying the queries sent to the database. It can be used to:

• Access sensitive data (e.g., user credentials, personal information).
• Modify or delete database records.
• Execute administrative operations on the database.
• Exploit vulnerabilities to escalate privileges.
• Take complete control of the system.

Common types of SQL injection attacks

Following are the four main types of SQL injection attacks that demand different ways of detection and prevention:

In-band SQL Injection

This is the type of SQL injection attack where the attacker injects malicious SQL code to manipulate queries.

For example, say if the attackers wants to access all of the item names and descriptions in the database, the attackers will enter a URL similar to this in the browser:

http://www.examplesite.com/items/items.asp?itemid=999 or 1=1.

The corresponding SQL query will be the following:

SELECT ItemName, ItemDescription
FROM Items
WHERE ItemNumber = 999 OR 1=1

Since 1=1 is always true, this statement could return every data field for all items in the ‘Items‘ table. This is the simplest and most common form of SQLi.

Another way SQL queries can be manipulated with a UNION SELECT to output additional data into a single result.

For example, the input http://www.examplesite.com/items/items.asp?itemid=999 UNION SELECT username, password FROM USERS produces the following SQL query:

SELECT ItemName, ItemDescription
FROM Items
WHERE ItemID = '999' UNION SELECT Username, Password FROM Users;

This query combines the request for item 999’s name and description with another that pulls user credentials for every user in the ‘Users’ table.

Blind SQL Injection

This is the type of SQL injection where the attacker does not directly see the database output (e.g., error messages or returned data). Instead, the attacker infers information based on how the application behaves, such as differences in response time or content.

The delay in response indicates to the attacker that the query has executed, hence the application might be vulnerable to SQL Injection.

For example, the attacker might alter the URL like so:

http://www.examplesite.com/items/items.asp?itemid=999 AND IF(SUBSTRING((SELECT Password FROM Users WHERE id = 1), 1, 1) = 'a', sleep(10), 'false')

This results in the SQL query:

SELECT ItemName, ItemDescription
FROM Items
WHERE ItemID = '999' AND IF(SUBSTRING((SELECT Password FROM Users WHERE id = 1), 1, 1) = 'a', sleep(10), 'false')

If the response by 10 seconds is delayed, the first letter of the password is 'a'. If not, it will respond immediately.The attacker would need to repeat this for each character and each position in the password.

Second-order SQL Injection

Second-order SQL injection happens when malicious input is stored in the database and executed later when another query runs. Unlike classic SQL injection, where the payload is executed immediately, second-order SQL injection waits until the stored data is used in a new query.

To illustrate the vulnerability, let us consider a website that has User login, signup, and password change functionality.

1. Firstly, the users are required to either signup or sign in. New user sign-up with username ‘test’ and password ‘123’.

2. To Perform Second Order SQL Injection an attacker will register with the following username

‘test’ —’ and password ‘abc’.

3. Now the attacker login with the ‘test’ —’ account and go to change password functionality and then changes the password from ‘abc’ to ‘hacked’.

4. The query processing in the backend to update the password:

UPDATE Users  
SET password='hacked'
WHERE username='test' —' and password='abc'

Because after — the query is discarded as comments, The query ends up like:

UPDATE Users  
SET password='hacked'
WHERE username='test'

The Query results in updating the password for the user ‘test’, instead of ‘test’ —’.

Out-of-band SQL Injection

Out-of-band SQL Injection is a type of SQL injection attack where an attacker exfiltrates data using external communication channels such as DNS requests, HTTP requests, or emails.

Out-of-band SQL Injection is not very common, mostly because it depends on features being enabled on the database server being used by the web application.

How to prevent SQL injection attacks

Now, let us discuss how can we prevent SQL injection attacks.

Detect SQL Injection Vulnerabilities

One approach to detecting SQL injection is seeing common keywords that are used in the attack, such as:

1. SELECT, FROM
2. '
3. --
4. #
5. OR 1=1

Use Prepared Statements (Parameterized Queries)

Probably one of the top defenses against SQL injection attacks is by using prepared statements and parameterized queries.

String query = "SELECT * FROM Items WHERE category = '"+ input + "'";
Statement statement = connection.createStatement();
ResultSet resultSet = statement.executeQuery(query);

Never directly concatenate user inputs into SQL queries. You can use parameterized queries and rewrite this code in a way that prevents the user input from interfering with the query structure

PreparedStatement statement = connection.prepareStatement("SELECT * FROM Items WHERE category = ?");
statement.setString(1, input);
ResultSet resultSet = statement.executeQuery();

Use Web Application Firewall or WAF

A Web Application Firewall is a device that cleans and monitors HTTP requests; it blocks malicious traffic, such as SQL injection attempts, from ever reaching your application.

Input Validation & Whitelisting

Input validation will be forcefully implemented for all input fields, including login forms and URLs. This includes data types, and format verification; special character escaping that gives rise to any potential harm.

Make sure that input validation relies on allow-listing and not blocklisting, as described earlier. Create a rule that describes all allowed patterns with, for instance, a regular expression.

Implement Least Privilege Principle

The Least Privilege Principle means giving users and applications only the minimum level of access required to perform their tasks. This reduces the risk of SQL injection, privilege escalation, and data breaches.

For example, by granting users access only to the tables they need, you can reduce the potential impact of misuse by a malicious user.

Conclusion

SQL injection is a critical security risk, but it can be effectively mitigated by using prepared statements, input validation, least privilege access, and other best practices. Implementing these measures will significantly reduce the risk of attacks and keep your application secure.