SQL Injection Attacks: Know How to Prevent Them

DZone 's Guide to

SQL Injection Attacks: Know How to Prevent Them

They still happen, so what exactly are they, and how can you stop them from even happening?

· Security Zone ·
Free Resource

With advancement in technology, modern society has accomplished many unthinkable goals. However, as technology develops, so does the risk involved in using it. Same is the case with web applications. Today's applications are fraught with vulnerabilities. Since 2003, SQL injection has remained in the OWASP top ten list of application security risks that companies are wrestling with. In this article, we will explore SQL injection attacks and ways to prevent it. Let's take a look at topics covered in this article:

  1. What is an SQL Injection Attack?
  2. What can an SQL Injection do?
  3. How do SQL Injection attacks work?
  4. What are different types of SQL Injection attacks?
  5. How can SQL Injection Attacks be prevented?

You can go through this cybersecurity video lecture where our training expert is discussing each and every nitty-gritty of the technology.

Application Security | SQL Injection Attack | Edureka

What Is an SQL Injection Attack?

SQL injection (SQLi) is an injection attack where an attacker executes malicious SQL statements to control a web application's database server, thereby accessing, modifying, and deleting unauthorized data.

In the early days of the Internet, building websites was a simple process: no JavaScript, no CSS, and few images. But as the websites gained popularity, the need for more advanced technology and dynamic websites grew. This led to the development of server-side scripting languages like JSP and PHP. Websites started storing user input and content in databases. MySQL became the most popular and standardized language for accessing and manipulating databases. However, hackers found new ways to leverage the loopholes present in SQL technology. SQL injection attacks are one of the most popular ways of targeting databases. SQL injections target the databases using specifically-crafted SQL statements to trick the systems into doing unexpected and undesired things.

What Can SQL Injection Attacks Do?

There are a lot of things an attacker can do when exploiting an SQL injection on a vulnerable website. By leveraging an SQL injection vulnerability, given the right circumstances, an attacker can do the following things:

  • Bypass a web application's authorization mechanisms and extract sensitive information
  • Easily control application behavior that's based on data in the database
  • Inject further malicious code to be executed when users access the application
  • Add, modify, and delete data, corrupting the database, and making the application or unusable
  • Enumerate the authentication details of a user registered on a website and use the data in attacks on other sites

It all depends on the capability of the attacker, but sometimes, an SQL injection attack can lead to a complete takeover of the database and web application. Now, how does an attacker achieve that?

How Do SQL Injection Attacks Work?

A developer usually defines an SQL query to perform some database action necessary for his application to function. This query has one or two arguments so that only desired records are returned when the value for that argument is provided by a user.

An SQL injection attack plays out in two stages:

  1. Research: Attacker gives some random unexpected values for the argument, observes how the application responds, and decides an attack to attempt.
  2. Attack: Here, the attacker provides carefully-crafted value for the argument. The application will interpret the value part of an SQL command rather than merely data. The database then executes the SQL command as modified by the attacker.

Consider the following example in which a website user is able to change the values of $user and  $password, such as in a login form:

$statement = "SELECT * FROM users WHERE username ='$user'  AND  password '$password'";

This particular SQL statement is passed to a function, which, in turn, sends the string to the connected database where it is parsed, executed, and returns a result.

#Define POST variables
uname = request.POST['username']
passwd = request.POST['password']

#SQL query vulnerable to SQLi
sql = "SELECT id FROM users WHERE username='" + uname + "' AND password='" + passwd + "'"

#Execute the SQL statement 

Now, if the input is not properly sanitized but the application, the attacker can easily insert carefully crafted value as input. For example, this will look something like:

$statement = "SELECT * FROM users WHERE username ='Dean' OR '1'='1'-- ' AND password = 'WinchesterS'";

So, what's happening here? The highlighted part is the attacker's input, it contains two special parts:

  • OR '1' = '1' is a condition that will always be true, thereby it is accepted as a valid input by the application
  • -(double hyphen) instructs the SQL parser that the rest of the line is a comment and should not be executed

Once the query executes, the SQL injection effectively removes the password verification, resulting in an authentication bypass. The application will most likely log the attacker in with the first account from the query result - the first account in a database is usually of an administrative user.

Note that this is just one way of exploiting the SQL Queries to get the necessary information in an unofficial way. SQL injection attacks are divided into multiple types.

What Are the Different Types of SQL Injection Attacks?

Attackers can extract data from servers by leveraging an SQL injection vulnerability in various ways. SQL injection can be classified into three major categories:

Let's explore the variants.

In-Band SQL Injection

It is the most common SQL injection attack. Usually, it occurs when an attacker is able to use the same communication channel to both launch the attack and gather results. The two most common types of in-band SQL injection are:

  • Error-based SQL Injection — It is a technique that relies on error messages thrown by the database server to obtain information about the structure of the database. Sometimes, this simple attack is more than enough for an attacker to enumerate an entire database.
  • Union-based SQL Injection — This technique leverages the UNION SQL operator to combine the results of two or more SELECT statements into a single result, which is then returned as part of the HTTP response.

In this type of injection, no data is actually transferred via the web application. So, the attacker will not be able to see the result of an attack. Here, the attacker reconstructs the database structure by sending payloads and observing the web application's response and the resulting behavior of the database server. The two types of inferential SQL injection are:

  • Boolean-based SQL Injection — In this technique application is forced to return a different result depending on whether the query returns a TRUE or FALSE result. Based on the result, the content within the HTTP response will change, or remain the same.
  • Time-based SQL Injection — It is a technique that relies on sending an SQL query to the database, which forces the database to wait for a specified amount of time (in seconds) before responding. The time website takes to respond will indicate to the attacker whether the result of the query is TRUE or FALSE.

Out-of-Band SQL Injection

These types of SQL injection attacks are the least common and generally the most difficult to execute. They usually involve sending the data directly from the database server to a machine that is controlled by the attacker. Out-of-band techniques offer the attacker an alternative to In-band or Blind SQL injection attacks, especially if the server responses are not very stable.

So, server-scripting languages are not able to determine if or not the SQL query string is malformed. All that they can do is send a string to the database server and wait for the interpreted response. But surely, there must be ways to sanitize user input and ensure that an SQL injection is infeasible, right?

How Can SQL Injection Attacks Be Prevented?

There are a lot of easy ways to avoid falling prey for SQL injection attacks and to limit the damage they can cause. Few of them include:

  • Discover SQL injection vulnerabilities by routinely testing applications both using static testing and dynamic testing
  • Avoid and repair injection vulnerabilities by using parameterized queries and Object Relational Mappers (ORMs). These types of queries specify placeholders for parameters so that the database will always treat them as data rather than part of a SQL command.
  • Remediate SQL injection vulnerabilities by using escape characters so that special characters are ignored.
  • Mitigate the impact of SQL injection vulnerabilities by enforcing least privilege on the database, this way each software component of an application can access and affect only the resources it needs.
  • Use a Web Application Firewall (WAF) for web applications that access databases. This can help identify SQL injection attempts and sometimes help prevent SQL injection attempts from reaching the application as well.

SQL injection attacks are popular attack methods for cybercriminals, but by taking the proper precautions, such as ensuring that data is encrypted, performing security tests, and being up to date with patches, you can take meaningful steps toward keeping your data secure.

There are a variety of ways a hacker may infiltrate an application due to web application vulnerabilities. So stay informed!

database security, php, security, sql, sql injection, sql injection attack, video, vulnerabilities, web dev

Published at DZone with permission of Archana Choudhary , DZone MVB. See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}