Get a free application, infrastructure and malware scan report - Scan Your Website Now

Subscribe to our Newsletter
Try AppTrana WAAP (WAF)

How to Prevent SQL Injection Attacks?

Posted DateDecember 7, 2023
Posted Time 13   min Read

Are you aware of the increasing threat of SQL injection vulnerabilities?In Q4 2022, AppTrana stopped 1,111,548 of these attacks. With over a million SQL injection attacks blocked in just three months, it’s clear that web applications are under siege.

How to stay ahead of the game and protect your business now?

Here is a guide to understanding this OWASP top 10 vulnerability and how to prevent SQL injection attacks.

What are Database and SQL?

A database is a set of described tables from which data can be accessed or stored. A database application requires a communication medium between the front end and the database. This is where SQL comes into the picture.

What does SQL Injection stand for?

Developed in the 1970s, Structured Query Language (SQL) is a language for accessing and manipulating data from the database. An application can communicate with the database using SQL statements.With the use of SQL statements, the application can perform some standard SQL commands such as “SELECT,” “UPDATE,” “INSERT,” “DELETE,” “CREATE,” and “DROP.”

Attackers use the input fields in web applications to run arbitrary queries (injection) on the server. Hence, the attack process is called SQL Injection or SQLi attack.

They gain access to information that is not intended to be displayed. These injection attacks are categorized as ‘high impact severity’ by OWASP Top 10.

What causes SQLi?

The root cause of SQLi is usually a failure to properly sanitize user input.

When an application allows user input in SQL statements without proper validation, an attacker can craft SQL queries with additional commands. It results in unauthorized access, modification of data, or other malicious actions.

For example, an attacker could use SQL injection to bypass authentication mechanisms by crafting an SQL query that always returns true. It allows them to log in without a valid username and password.

Or they could use SQL injection to modify the data stored in the database, steal sensitive data, or execute other malicious actions.

How does SQL Injection Work?

Here’s an example to illustrate how SQL Injection works:

How does SQLi attack works

Let’s say we have a website with a SQL database to store user information. The website has a login form where users enter their usernames and password. The website’s code might look something like this:

$username = $_POST[‘username’];
$password = $_POST[‘password’];
$sql = “SELECT * FROM users WHERE username=’$username’ AND password=’$password'”;
$result = mysqli_query($conn, $sql);
if (mysqli_num_rows($result) > 0) {
// User has successfully logged in
} else {
// Invalid username or password
}

 

In this code, the user’s input is directly inserted into the SQL statement. If a user enters a username like admin’–, the SQL statement becomes:

SELECT * FROM users WHERE username=’admin’–‘ AND password=”

The double dash (–) is a comment character in SQL, which means everything after it is ignored. This allows the attacker to bypass the password check and log in as the administrator.

Attackers try different variations of SQLi using common SQL injection commands to see which commands get executed by the database.

Based on this, they keep executing SQLi attacks to access the required information. They may stop after gathering what they need or keep coming back to do their bidding until these vulnerabilities exist.

Virtually patch SQLi Vulnerabilities on AppTrana WAF

What are the Types of SQL Injection Attacks?

Various types of SQL injection attacks exist, each with its distinctive characteristics. Here is a list of common SQL injection attack types:

  1. Error-Based SQL Injection
  2. Union-Based SQL Injection
  3. Blind SQL Injection
    • Time-Based Blind SQL Injection
    • Boolean-Based Blind SQL Injection

Types of SQL Injection attacks

Error-Based SQL Injection

An error-based SQL injection is an attack that a malicious user uses with malicious SQL queries to get some kind of error or confirmation that there was a problem with their input.

It is normally about a rule in the database’s syntax. Then they can use this information to extract information from the database, such as passwords or personal bank accounts.

How to detect Error based SQL injection?

There are so many types of SQL commands that an attacker could execute, and they’re all quite unpredictable.

Here are some examples: single quotes, double quotes, or SQL operators like AND, OR, and NOT

For example:

http://demo.testfire.net/index.php?title=1’

 

And the error message greets the attacker: “You have an error in your SQL syntax; check the manual corresponding to your MySQL server version for the right syntax to use near ‘‘VALUE’’.

The error message gives him vital information like:

  • DB used as MySQL
  • Error in the syntax is a double quote
  • The place of the error caused is at the end of the parameter

Union-based SQL Injection

In this type of SQL Injection, attackers try to exploit the vulnerability with the help of the “UNION” operator.

The UNION operator is used for combining 2 tables or performing 2 select queries simultaneously. In union operators, they remove duplicate rows or columns, which we try to execute simultaneously.

Union-based SQL Injection Example

Let’s assume we have a web application that takes a user’s input and builds an SQL query like this:

SELECT name, email, phone FROM users WHERE name = ‘[user_input]’

 

The user input is not properly sanitized, so an attacker could inject their own SQL code. For example, they could enter the following value as their name:

‘ UNION SELECT password, NULL, NULL FROM users —

 

This would result in the following SQL query being executed:

SELECT name, email, phone FROM users WHERE name = ” UNION SELECT password, NULL, NULL FROM users –‘

 

The — at the end of the injected string is a comment symbol, which comments out the rest of the original query. So, the resulting query would be equivalent to:

SELECT name, email, phone FROM users WHERE name = ”
UNION SELECT password, NULL, NULL FROM users

 

This query would return a table with the user’s name, email, and phone number and a table with all the passwords in the user table. The attacker could then use this information to further compromise the system.

Blind SQL Injection

Blind SQLi is a type of SQL injection attack where an attacker exploits a vulnerability in a web application to extract sensitive information from a database without being able to directly see the data.

This type of attack is known as “blind” because the attacker cannot see the actual database content. But instead relies on the application’s response to infer the information.

There are two main types of blind SQL injection attacks:

1. Boolean-based SQLi
2. Time-based SQLi

Boolean-based SQLi

In this type of SQL Injection attack, the attacker sends a series of SQL queries that evaluate either true or false, depending on whether the injected code was executed successfully.

The attacker can then use the application’s response to infer information about the database by constructing complex queries that probe for specific information.

Boolean-based SQLi Example: Deleting a user database using Boolean-based SQLi

How it works: A common online shop’s SQL database query could be like this:

SELECT ItemName, ItemDescription FROM Item WHERE ItemNumber = ItemNumber

 

So, a product URL on the online store could be

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

The SQL query could be

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

 

One is always equal to one. It’s just a basic mathematical fact that holds true no matter where you are. SQL queries return all product names and descriptions in the database, even those you lack permission to access.

Time-based SQLi

This attack involves injecting a query that is designed to cause a delay in the application’s response. By measuring the time, it takes for the application to respond to the query, the attacker can determine whether the query was successful.

This technique is helpful when the attacker has no answer (error/output) from the application because the input validation has been sanitized.

Time-based SQL injection example

For example, let’s say there is a login form on a web application that uses a SQL query to check whether a user’s credentials are valid. The query might look something like this:

SELECT * FROM users WHERE username = ‘admin’ AND password = ‘password123’

 

To perform a blind SQLi attack, an attacker could inject a query like this:

SELECT CASE WHEN (1=1) THEN pg_sleep(10) ELSE pg_sleep(0) END;

 

This query will cause the application to sleep for 10 seconds if the condition “1=1” is true. The attacker can determine whether the condition was true or false by measuring the time it takes for the application to respond to this query.

If the response takes 10 seconds, the attacker knows that the condition was true and that the application is vulnerable to blind SQLi. If the response is immediate, the attacker knows the condition was false.

Once the attacker has confirmed that blind SQLi is possible, they can start injecting more complex queries to extract sensitive information from the database.

How are SQL Injections Bot-Driven?

SQL injection attacks can be bot-driven in a few different ways. Bots can be programmed to automatically scan websites for vulnerabilities and then attempt to exploit them using SQL injection techniques. Here are some examples:

Automated scanning: Bots can be programmed to scan many websites automatically, looking for vulnerabilities that could be exploited with SQL injection attacks. Once a vulnerable website is identified, the bot can then attempt to exploit the vulnerability using a pre-defined set of SQL injection payloads.

Brute-force attacks: Bots can also be used to conduct brute-force attacks on web forms, such as login pages or search boxes, to try to inject SQL code into the application. The bot can use different techniques, such as a dictionary attack or a fuzzing attack, to generate different payloads and submit them to the application to find the vulnerable entry point.

Automated exploitation: Once a vulnerable entry point is identified, bots can be used to automate injecting malicious SQL code and extracting data from the database.

The bot can use different techniques, such as blind SQL injection or error-based SQL injection, to extract sensitive data from the database.

Overall, bots can be used to automate the process of identifying and exploiting SQL injection vulnerabilities on a large scale. It makes it easier for attackers to compromise many websites and extract sensitive data. That’s why it’s important to implement proper security measures to prevent SQL injection attacks.

What are the Impacts of Injection Attacks?

The impact of SQL injection attacks can be far-reaching and may vary depending on the target. Data integrity and confidentiality breach are the most common consequences. When the database executes the malicious code, it potentially allows the attacker to:

  • Steal sensitive data: SQL injection allows attackers to extract confidential information from a database, including usernames, passwords, credit card numbers, and personal details.
  • Modify or delete data: Exploiting SQL injection enables unauthorized manipulation or deletion of data within a database, leading to potential data loss or damage.
  • Take control of a system: Attackers can gain administrative access, granting them control over a system. This access facilitates further malicious activities such as additional attacks, malware installation, or other unauthorized actions.

The potential cost of an SQL injection attack is even higher.  Financial loss can come in direct losses, such as the cost of restoring systems and data after an attack.  Indirect losses include lost revenue due to a disruption of business operations.

In addition, businesses may also face financial penalties or legal action due to data breaches caused by injection attacks. This can particularly damage businesses that handle sensitive information, such as financial institutions or healthcare providers.

Furthermore, reputation damage can be long-term and difficult to recover from, negatively affecting the company’s future growth and profitability.

Common SQL injection attacks result in loss of knowledge or denial of access. However, over the years, hackers coupled these attacks with insufficient authentication, DNS hijacking, XSS, and DDoS attacks to cause heavy financial damage and absolute host takeover.

Downtime can lead to lost revenue and customer frustration, further damaging the business’s reputation.

Most Notorious SQLi Attacks in History

The following are some of the most famous SQL attacks in recent years that every company must be aware of:

Kotak Life Insurance Data Breach 2023: The fastest-growing insurance company encountered a targeted data breach that exploited a SQL injection zero-day vulnerability within the MOVEit application. The BBC has reported that multiple organizations in the UK, including the BBC itself, have confirmed instances of data breaches resulting from a MOVEit transfer SQL injection vulnerability.

WooCommerce unauthenticated SQL Injection: In July 2021, WooCommerce disclosed that several of its feature plug-ins and software versions were vulnerable to SQL injections; they noticed several security attacks occurring during that time.

Kaseye ransomware attack: In July 2021, a notorious group called REvil affected over 1500 businesses managed by Kaseya. Hacker remotely exploited the SQL vulnerability of the Kaseya VSA servers.

Drupal SQL Injection: In October 2014, Drupal declared its high vulnerability against the attack. Lack of user input sanitization resulted in SQL injection vulnerability. Drupal core versions ranging from 7.0 to 7.31 were vulnerable.

The Target Data Breach: In 2013, the Target Corporation was the victim of a massive data breach that affected 40 million customers. Experts claim that the server fell to SQL injection attacks.

Yahoo Hack: In July 2012, 453,000 email addresses and passwords of Yahoo Voices users were leaked. The credentials were stored in an unencrypted way. Hackers have stolen the data by executing a SQL Injection attack.

The Sony PlayStation Network Hack: In 2011, the Sony PlayStation Network (PSN) was hacked, resulting in the loss of personal information for 77 million users. The hack was reportedly the result of a SQL injection attack. The attackers were able to gain access to sensitive information such as users’ names, addresses, and credit card numbers.

Heartland Payment Systems: In March 2010, Albert Gonzalez was sentenced to 20 years. He installed his code into the credit card server of Heartland Payment Systems and stole 130 million credit card numbers. The attack cost was around $12 million for the company.

How to Prevent SQL Injection Attacks?

To prevent SQL injection attacks, secure all fields from invalid inputs and unauthorized application execution.

While client-side validation effectively prevents invalid inputs from reaching the system logic and enhances user-friendliness, it is not a foolproof method for thwarting users with malicious intentions.

Therefore, validating inputs on the server side becomes essential, ideally as close to the source as possible—particularly at the stage where SQL queries are constructed.

Find SQLi Vulnerbailities on your apps using DAST Scanner

While input validation stands as the best defense against injection attacks, it is essential to establish multiple layers of defense. Implementing measures such as restricting database user privileges and employing parameterized queries is a robust practice to reduce the risk of SQL injection in your application significantly.

7 Effective SQL Injection Prevention Techniques

  1. Implement Input Validation and Sanitization
  2. Apply Escaping Techniques
  3. Utilize Parameterized Statements (Prepared Statements)
  4. Incorporate Stored Procedures
  5. Conduct Continuous Scanning and Penetration Testing
  6. Adopt the Least Privilege Principle
  7. Deploy Web Application Firewalls (WAF)

Let’s look at each method to enhance your application’s defense against SQLi:

1. Implement Input Validation and Sanitization

Input validation is a fundamental and indispensable component of a robust security strategy, particularly defending against injection attacks. Whether dealing with SQL injection, XSS, or other injection vulnerabilities, input validation ensures that user inputs adhere to expected formats while sanitization removes potentially harmful elements.

Example (using PHP and filter_var):

$username = filter_var($_POST[‘username’], FILTER_SANITIZE_STRING);

2. Apply Escaping Techniques

The escaping technique involves modifying user inputs to neutralize special characters that could be used for malicious SQL injection.

The database system interprets these characters as literal values rather than executable code by escaping them. Using database-specific escape functions or libraries is crucial to handling special characters properly.

Example (using PHP and MySQLi):

In PHP, the mysqli_real_escape_string function can be utilized to escape user inputs before incorporating them into SQL queries:

$user_input = mysqli_real_escape_string($connection, $_POST[‘user_input’]);
$password_input = mysqli_real_escape_string($connection, $_POST[‘password_input’]);
$query = “SELECT * FROM users WHERE username = ‘$user_input’ AND password = ‘$password_input'”;

 

Key Considerations – Use Database-Specific Escaping Functions

Different database systems may have unique escaping functions. Always use the appropriate escaping functions provided by the specific database being used (e.g., mysqli_real_escape_string for MySQL, pg_escape_string for PostgreSQL).

3. Utilize Parameterized Statements (Prepared Statements)

While the escaping technique can be effective, using parameterized statements is generally considered a more secure and robust approach to prevent SQLi. Parameterized statements separate user inputs from the SQL query, eliminating the need for manual escaping.

This ensures that user inputs are treated as data, preventing the execution of malicious code. The database system recognizes placeholders and binds user inputs securely during execution.

Example (using Python and SQLite):

cursor.execute(“SELECT * FROM users WHERE username = ? AND password = ?”, (user_input, password_input))

 

Instead of directly embedding user inputs into the SQL query, placeholders (?) are used for the values that will be provided later.

The execute method of the database cursor is used to execute the SQL query. The first argument is the SQL query with placeholders, and the second argument is a tuple containing the actual values (user_input and password_input) that will replace the placeholders.

The database system recognizes the placeholders (?) and binds the user inputs securely during execution. This means that the values provided in the tuple are treated as data, not as part of the SQL query, preventing any potential SQL injection.

4. Incorporate Stored Procedures

Stored procedures encapsulate SQL code within the database. The injection risk is minimized by defining parameterized procedures, as these procedures are executed without directly incorporating user inputs.

Example (using SQL Server):

CREATE PROCEDURE GetUser
@username NVARCHAR(50)
AS
BEGIN
SELECT * FROM users WHERE username = @username;
END

 

Despite popular belief, relying on stored procedures doesn’t completely defend a system from SQL injection. Like SQL queries directly integrated into an application, stored procedures remain vulnerable to malicious injection if not managed carefully.

To prevent SQL injection in stored procedures, it is crucial to parameterize the queries within the procedure rather than concatenating parameters directly.

Example (Incorrect Method in MySQL) – Concatenating parameters directly

CREATE PROCEDURE GetUser(IN username VARCHAR(255))
BEGIN
SET @sql_query = CONCAT(‘SELECT * FROM users WHERE username = ”’, username, ””);
PREPARE stmt FROM @sql_query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
END;

 

Preventive Approach (Correct Method with Parameterization):

CREATE PROCEDURE GetUser(IN username VARCHAR(255))
BEGIN
SELECT * FROM users WHERE username = username;
END;

 

By avoiding the direct concatenation of parameters and opting for parameterized queries within stored procedures, developers can substantially mitigate the risk of SQL injection. This approach enhances security by treating user inputs as data and not as executable code, aligning with best practices to safeguard against potential malicious exploitation.

5. Conduct Continuous Scanning and Penetration Testing

Regular security audits and code reviews involve thorough examinations of the application’s codebase. Automated tools and manual inspections help identify and address potential vulnerabilities, ensuring ongoing security.

AppTrana’s embedded DAST scanner ensures continuous inspection for security vulnerabilities like SQL injection in your code. This tool not only automates the vulnerability assessment process but is also equipped with manual pen testing.

Regular security audits, code reviews, and penetration testing collectively contribute to a proactive approach to identifying and addressing vulnerabilities.

6. Adopt the Least Privilege Principle

Limiting permissions to the minimum necessary reduces the impact of a successful SQL injection attack. Granting only specific privileges required for the application decreases the potential damage.

Example (using MySQL):

GRANT SELECT ON database.users TO ‘web_app’@’localhost’;

 

By using this GRANT statement with the SELECT privilege, the example demonstrates a security best practice: providing the ‘web_app’ user with the minimum necessary permissions. This user can only execute SELECT queries on the ‘users’ table within the ‘database’ database.

If an attacker exploits a vulnerability and gains unauthorized access, the impact is minimized because the compromised user has restricted privileges, mitigating potential damage to the overall system.

7. Deploy Web Application Firewalls (WAF)

Web Application Firewall (WAF) monitors and filters incoming HTTP traffic, detecting and blocking SQL injection attempts.

Rules can be configured to identify patterns associated with SQL injection, providing an additional layer of defense.

WAF Rule Example:

SecRule ARGS “(select|union|insert|delete|drop)” “deny,log”

This rule is like a security filter that scans incoming data for certain words often associated with SQL injection attempts. If it finds any, it denies the request and logs the occurrence, providing an additional layer of defense against potential SQL injection attacks.

Many organizations struggle to tackle issues such as outdated code, limited resources for testing and implementing changes, lack of awareness about application security, and the challenges posed by frequent updates in their applications.

Even minor code changes can introduce injection issues as such changes may not be subjected to a fully-fledged security review process in the development cycle.

When a vulnerability is identified, and immediate code fixes are not feasible due to time constraints or other considerations, a WAF can be used for virtual patching the vulnerabilities.

Virtual patching provides organizations with a rapid and effective means of securing their applications against known vulnerabilities, buying time to implement proper code fixes or updates.

Prevent SQL Injection with AppTrana

AppTrana WAF help protects against SQL injection attacks by analyzing the incoming traffic to your web application and identifying malicious SQL code. It can then block the malicious traffic before it reaches your application, preventing the attack from occurring.

AppTrana uses a combination of machine learning, rule-based, and behavioral-based detection techniques to identify and block SQL injection attacks.

Additionally, AppTrana provides real-time visibility and reporting on all incoming traffic, so you can see what is happening on your application and quickly respond to potential threats.

You can start by determining if your website has SQL Injection risks with AppTrana Free Trial.

Stay tuned for more relevant and interesting security articles. Follow Indusface on FacebookTwitter, and LinkedIn.

AppTrana WAAP

Venkatesh Sundar

Venky is an Application Security technologist who built the new age Web application Scanner and Cloud WAF - AppTrana at Indusface as a Founding CTO. Currently, he spends his time on driving Product Roadmap, Customer Success, Growth, and technology adoption for US businesses.

Share Article:

Join 47000+ Security Leaders

Get weekly tips on blocking ransomware, DDoS and bot attacks and Zero-day threats.

We're committed to your privacy. indusface uses the information you provide to us to contact you about our relevant content, products, and services. You may unsubscribe from these communications at any time. For more information, check out our Privacy Policy.

Related Posts

Prevent SQLi Attacks
How to Prevent Bot-Driven SQL Injection Attacks?

SQL Injection attacks have far-reaching business impacts. Delve into the details of SQLi attacks and how to prevent them.

Read More
SQLite Vulnerability and Application Security
SQLite Vulnerability May Be Putting Your Applications at Risk

SQLite vulnerability is a serious security flaw despite being medium impact, and can put your applications at risk.

Read More
Drupal security
7 Quick and Easy Ways to Secure Drupal Website

Efficiently securing Drupal needs understanding of Drupal security best practices for maintaining it securely. Learn more here.

Read More

AppTrana

Fully Managed SaaS-Based Web Application Security Solution

Get free access to Integrated Application Scanner, Web Application Firewall, DDoS & Bot Mitigation, and CDN for 14 days

Get Started for Free Request a Demo

Gartner

Indusface is the only cloud WAAP (WAF) vendor with 100% Customer Recommendation for 3 consecutive years.

A Customers’ Choice for 2022 and 2023 - Gartner® Peer Insights™

The reviews and ratings are in!