Home » SQL Injection 101: What is SQLi and How to Prevent Attacks

SQL Injection 101: What is SQLi and How to Prevent Attacks

August 06, 2022 • security

What is SQL injection

SQL injection (SQLi) is a type of attack that allows criminals to execute malicious SQL statements against vulnerable web applications. Attackers can access sensitive data, such as customer information, personal data, trade secrets, and more by bypassing application security measures. Here we will discuss SQL injection, how it works, and how you can prevent attacks.

SQL injection attacks can happen on any website that uses a SQL database. This type of attack allows attackers to get access to your essential data. They can see consumer information, personal data, trade secrets, and intellectual property. This is one of the most severe types of attacks on web applications, according to OWASP.

Types of SQL Injection

Types of SQL Injection

In-band SQLi

In-band SQL injection is an attack where the attacker uses the same channel to send and receive queries. In-band means that the response is obtained using the same communications medium. The attacker’s goal is to get the response in a web browser immediately, if possible when carrying out the attack manually with a web browser.

Example of in-band SQL injection

The most common way for an attacker to do an in-band SQL injection is to change the request so they can see the personal information of the current user. This can be done by changing the value sent as part of the request. For example, if the statement was supposed to display the user’s name, the attacker could change it so that their name is displayed instead.

SELECT * FROM users WHERE user_id LIKE 'current_user'

Error-based SQLi and Union-based SQLi are the two most frequent forms of in-band SQL injection.

Error-based SQLi

An error-based SQLi technique is an in-band SQL injection approach that takes advantage of database server error messages to discover the database’s architecture. Error-based SQL injection is the most common type of in-band SQL injection.

Example of error-based SQLi:

If an attacker tries to log in with the following credentials:

username: ' OR 'a'='apassword: anything

The database will return an error because the statement is syntactically incorrect. The error message will reveal information about the database, which the attacker can use to their advantage.

Union-based SQLi:

In-band SQL injection is a way of getting information from a website using the UNION operator to combine the outputs of two or more SELECT statements.

Blind SQL injection

Blind SQL injection is an attack where the attacker tries to get answers from the database by asking questions that will result in a true or false response. The attacker uses error messages to see if the application responds differently when a specific code is used.

When a hacker uses SQL injection, the web application could display critical database warning messages stating that the SQL query syntax is incorrect. Blind SQL injection works the same way as traditional SQL injection, except for how data is obtained from the database. If a database does not have enough information for an attacker to exploit, an attacker must ask a series of questions to obtain data.

Blind SQL injection is divided into blind-boolean-based SQLi and Blind-time-based SQLi.

Boolean-based Blind SQLi

Boolean-based Blind SQL injection is an attack where the attacker tries to get answers from the database by asking questions that will result in a true or false response. The attacker uses error messages to see if the application responds differently when a specific code is used.

Example of boolean-based Blind SQLi:

If an attacker wants to find out the database type, they will use the following statement:

SELECT * FROM users WHERE user_id LIKE 'current_user' and database() like '%type%'

If the database is MySQL, the output would be something like this:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘and database() like ‘%type%” at line

Time-based Blind SQL injection

A time-based Blind attack is when an SQL command is sent to the server with code that causes queries to execute more slowly.

Time-based Blind attacks allow attackers to extract data based on access time. Such an attack is known as a blind or inferential injection assault. This is a type of assault in which no data flows between the attacker and the database, but because there is no response, it’s also known as a blind injection attack.

The response time indicates whether the answer is correct or incorrect. If the response is negative, the intruder will make another request. This attack technique is slow because the hacker must go through each character individually, especially when attacking massive databases.

Example of blind SQLi

In this example, the attacker tries to determine if the user with id=999 exists in the database. To do this, they use the following statement:

IF(SUBSTRING((SELECT password FROM users WHERE user_id=999),0, LEN('secret'))='secret', SLEEP(30), 'false')

If the user with id 999 exists in the database and their password is secret, then the application will sleep for 30 seconds. The application will return false if the user does not exist in the database.

Out-of-band SQLi

Someone who wants to steal data may send SQL code to a database server in a way that is not part of the usual communication between the server and other computers. This can be done by sending information to the server through DNS or HTTP requests.

The app’s response will not be affected by whether or not any data is returned, whether or not there is a problem with the database, or how long it takes to execute the query. Out-of-band can be used in network interactions to fire events at will. Depending on an injected condition, these may be activated conditionally to gain knowledge one bit at a time.

Data can also leak via several networking protocols from network interactions. The visual represents the request sent from the web application to the app’s database.

Example of out-of-band SQLi

In this example, the attacker tries to determine if a specific user exists in the database. To do this, they use the following statement:

SELECT user_id FROM users WHERE username='$username' AND password='$password' LIMIT 0,0 UNION SELECT NULL,'<?php echo system($_GET['cmd']); ? >' INTO OUTFILE '/var/opt/databases/$filename.php'; --

The application will return their user ID if the user exists in the database. If the user does not exist in the database, then the application will create a file that contains PHP code that can be used to execute system commands. The attacker can then use this file to run commands on the server.

How to prevent an SQL injection

The best way to protect against SQL injection attacks is to use input validation, prepared statements, and parametrized queries. The code should never make direct use of the user’s input. Developers must sanitize all input instead of simply web form inputs such as login forms. Single quotes should be eliminated from any questionable code components. It’s also a good idea to hide database problems on live sites to avoid inadvertently revealing them. SQL injection may provide information about a database system that attackers can use to their advantage.

If you find a problem with your website, you should take it offline immediately and contact your hosting provider. They can help you determine whether or not your site has been compromised and what steps you need to take to fix the problem. In the meantime, ensure that all of your website’s users know about the problem and change their passwords as soon as possible.

Prevention tips for avoiding SQL injections

There are a few ways to avoid SQL injection vulnerabilities in your programming language and database setup. These techniques can be used with most databases, such as XML. You can use these techniques to make your databases more secure.

1) Use of Properly Constructed Stored Procedures

Beginners should start by learning how to create statements with variables. This is easier than creating dynamic queries, and it is easier to understand. Parameterized queries are where the developer creates all of the SQL code and then supplies each parameter at a later date. This method allows the database to identify between source code and information.

Prepared statements help ensure that a query’s goal is not changed, even if someone tries to give SQL instructions.

2) Allow-list Input Validation

SQL queries use bind variables in specific places for data. For example, if you’re using Python, you would use the %s placeholder. You can use a regular expression to validate user input against the allow-list for what characters are allowed in each bind variable.

If you’re using JavaScript, you can use \w to match alphanumeric and underscore characters.

The allow-list should be as specific as possible to avoid false positives.

For example, if you’re looking for a US phone number, you would use the following regular expression:

/^\d{11}$/

This would match a string of 11 digits that could be a phone number. If someone tried to submit something like ‘abcdef‘, it would not match, and the input would be invalid.

This will help make sure your data is safe and sound. If you need to use values from code instead of user parameters, that’s okay too!

However, suppose user parameter values target specific table and column names. In that case, the parameter values should be mapped to the corresponding table and column names to ensure that unvalidated user input does not enter the query.

3) Use whitelists

Don’t filter user input based on blacklists of bad characters. Using allow-lists of good characters that are expected in specific fields is much more effective. This will stop SQL injection attacks before they start.

For example, only allow digits and dashes in the input field if you expect a phone number. If you’re expecting an email address, only allow characters that are valid in an email address.

4) Use the Most Up-To-Date Platforms

PHP does not have SQLi protection in older web development platforms. Use the most up-to-date edition of the programming environment, language, and associated technologies available. Instead of PHP, rather use PDO in this example.

5) Scan Your Web Application Regularly

SQL injections can be very hard to spot. It is important to scan your web application for vulnerabilities regularly.

6) Enforcing Least Privilege

The principle of least privilege is a security concept that restricts users to the minimum amount of access they need to do their job. This includes limiting the number of accounts users have and the privileges those accounts have.

Least Restriction on Functionality (LRF) is the practice and concept of restricting user rights, accounts, and computing processes to only those resources necessary for basic, acceptable tasks. This helps maintain minimal user rights or clearance levels, which is vital for people to do their jobs effectively.

Least privilege is a security principle that requires applications, systems, and gadgets to only have the permissions necessary to accomplish a specific task. This way, the impact will be limited if someone manages to exploit a vulnerability and do damage. This is in contrast to giving users more permissions than they need, which increases the risk of significant harm in an SQL attack.

SQL Injection – Frequently asked questions

 
What is the most common SQL injection?

In-band SQL injection is the most common type of SQL injection attack. It occurs when an attacker can use the same communication channel to deliver the payload and gather results.

What is the best defense of SQL injection?

The best defense against SQL injection is to use parameterized queries. This type of query uses placeholder values for parameters, which are supplied at a later date. This method allows the database to identify between source code and information.

How is SQL injection detected?

SQL injection can be detected in several ways. One method is to use a web application firewall (WAF). A WAF is a piece of hardware or software that sits between a web application and the internet. It inspects traffic for malicious activity and can block SQL injection attacks.

What is second-order SQL injection?

Second-order SQL injection occurs when an attacker can inject a payload that is stored by the web application and then later executed. This type of attack is more difficult to achieve because the attacker must have a way to trigger the execution of the stored payload.

What is blind SQL injection?

Blind SQL injection is an attack where the attacker does not directly see the results of their payload. Instead, they must use true or false statements to infer information from the database. This type of attack is more challenging to execute but can be just as dangerous as other types of SQL injection.

What is a stacked query?

A stacked query is a type of SQL injection where the attacker uses multiple queries to extract information from the database. This type of attack is more challenging to execute but can be very dangerous if successful.

What is an error-based SQL injection?

Error-based SQL injection is an attack where the attacker uses database errors to infer information from the database. This attack is more challenging to execute but can be very dangerous if successful.

woman avatar

security

admin is a senior staff writer for Government Technology. She previously wrote for PYMNTS and The Bay State Banner, and holds a B.A. in creative writing from Carnegie Mellon. She’s based outside Boston.