SQL Injection

It’s time to look at SQL injection. For a long time, it was the undisputed king of the OWASP Top 10, we’re talking years in a row. Despite being so old (like over 20 years), and while it’s fallen slightly from the top spot on that list, it’s still an incredibly popular and dangerous vulnerability. 

Being a web security vulnerability, SQL injection (SQLi)  is still one of the most common ‘hacking’ techniques used by attackers, as it allows them to manipulate a database and extract crucial information from it. On an even more alarming note, an attacker can make themselves the administrator of the database server and do some really devastating things like destroying databases, manipulating transactions, disclosing data, and making it vulnerable to more issues.

Let’s take a quick look at how it happens

SQL (or Structured Query Language) is the language used to communicate with relational databases; it’s the query language used by developers, database administrators and applications to manage the huge amounts of data being generated every day.

Within an application, two contexts exist: one for data, the other for code. The code context tells computers what to execute and separates it from the data to be processed. SQL injection occurs when an attacker enters data that is mistakenly treated as code by the SQL interpreter, allowing them to gather valuable information from the application. 

Effects of an SQL injection attack

An SQL injection can be extremely harmful to any web application and has been the preferred technique behind so many high-profile breaches because it provides attackers with unauthorized access to critical data. They can see so much information, from things like usernames and passwords, to credit card details and personal identification numbers. 

After gaining access to this data, attackers can take over accounts, reset passwords, go on an extended online shopping spree, or commit other (much worse) types of fraud. 

But perhaps the most alarming thing about SQLi is that an attacker can, if undetected, maintain a backdoor into the system for long periods of time. As you can imagine, that would lead to repeat data breaches for however long that backdoor is kept open. Scary stuff. 

Let’s look at a few examples to better understand how this looks in action.

SQLi Examples

SQLi includes various vulnerability techniques that can tackle different situations. What follows below are just some of the most common SQLi examples:

SQLi Types

Okay, now let’s look at the three different SQLi types. 

In-band SQLi

This is one of the most common, simple, and efficient types of SQL injection. In this type of attack, the same communication channel ise used to attack and retrieve the outcome or results.

Following are the two types of in-band SQLi attacks:

• Union-based SQLi - The union-based attack utilizes the union operator to combine two or more SQL queries, such as SELECT statements, to get the desired information and results in an HTTP GET response.
• ‍Error-based SQLi - The attacker utilizes the database’s error messages to understand the its structure. In this attack, the attacker may send false requests or perform actions to make the server display error messages so they can receive database information. This is why it’s important that developers avoid sending errors or log messages in the live environment; instead, they should be stored with restricted access.

Inferential SQLi

Inferential or blind SQLi attacks are more complicated and can take more time to exploit. On top of that, the attacker doesn’t actually get the attack results right away, which is what makes it a blind attack. 

The attacker sends the payloads via HTTP requests to the database server to restructure the user’s database, then they observe the application’s response and behavior to see whether or not the attack succeeded. 

These are two types of inferential SQLi attack:

• Boolean-based Blind SQLi - In this attack, a query is sent to the database getting the boolean (true or false) result, and the attacker observes the HTTP response to predict the boolean result.
• Time-based Blind SQLi - In this attack, the attacker sends a query to the database to make it wait for a few seconds before sending the response, and the attacker judges the query results from the response time of the HTTP request.

Out-of-band SQLi

This one is a more rare type of SQLi attack that depends on the database server’s enabled features. It happens in cases where the attacker can’t really use the other attack types.

For instance, if they can’t use the same communication channel for the in-band attack, or the HTTP response isn’t clear enough for them to work out the query results.
Moreover, it’s not that common because of its massive reliance on the database server’s ability to make HTTP or DNS requests to send the required data to the attacker.

How to defend against SQLi

Thankfully, the silver lining to SQL injection being so old and so common is that there are ways to prevent it from happening. Using these kinds of prevention techniques is not only a good coding habit, it’ll really bolster an organization’s security against SQLi. 

There are multiple ways to secure database servers from these kinds of attacks, such as input validation, using a web application firewall (WAF), securing databases, employing third-party security teams or systems, and writing fool-proof SQL queries.

Let’s look at an example of preventing SQL injections in Python by employing one of the above mentioned security measures.

Python Example

In this example, the attacker will be using a boolean-based blind SQL injection to grab important information from the system. 

Python: Vulnerable

Assume there’s a table called “sample_data” in the database. This table stores usernames and passwords for the application's users. 

Now allow the user to find a value from this database table by following commands:

import mysql.connector
db = mysql.connector.connect
#Bad Practice. Avoid this! This is just for learning.
(host="localhost", user="newuser", passwd="pass", db="sample")
cur = db.cursor()
name = raw_input('Enter Name: ')
cur.execute("SELECT * FROM sample_data WHERE Name = '%s';" % name) for row in cur.fetchall(): print(row)
db.close()

SQL injection

Here, if the user enters a name in the search, for instance, Alicia, there will be no problem with the output. 

However, if the user enters something like  Alicia’; DROP TABLE sample_data;  it will affect the database significantly.

Python: Remediation

The SQL statement should be changed to the following to prevent the attack from happening:

cur.execute("SELECT * FROM sample_data WHERE Name = %s;", (name,))

Now, the system will treat the user input as a string, even if the user tries to inject any SQL queries into it, and treat the user input as the name’s value only. 

This simple change can prevent malicious activity in future queries and secure the system from user input attacks.

Java Example

For this example, we’ll also use a database table named “sample_data” that stores the application’s user data. 

A basic login page takes a username and password and the java file, which is a servlet (LoginServlet), validates them against the database to allow the login operation. 

Java: Vulnerable Example 

Using the “sample_data” table in the database, the system allows users to perform login operations by taking their credentials as the input.

There is a query in the LoginServlet file to accommodate the login operation, which is:

//Bad Example. Do not use string concatenation.
String query = "select * from sample_data where username='" + username + "' and password = '" + password + "'";
        Connection conn = null;
        Statement stmt = null;
        try {
            conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user", "root", "root");
            stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(query);
            if (rs.next()) {
                // Login Successful if match is found
                success = true;
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                stmt.close();
                conn.close();
            } catch (Exception e) {}
        }
        if (success) {
            response.sendRedirect("home.html");
        } else {
            response.sendRedirect("login.html?error=1");
        }
    ^}

Following is the query for user login:

select * from sample_data where username='username' and password ='password'

SQL injection

The system will work perfectly if the input is valid. For example, we’ll say the username is Alicia again, and the password is secret. 

The system will return the data of the user with these credentials. However, an attacker can manipulate the user request using Postman and cURL for SQL injection. 

For example, the hacker can send a dummy username ( Alicia) and the password ‘or ‘1’=’1’. 

In this case, the username and password won’t match, but the condition ‘1’=’1’ will always be true so the login operation will be successful.

Java: Prevention

For prevention, we need to modify the LoginValidation code and use PreparedStatement instead of Statement for query execution. This change will prevent concatenating the username and password in the query and treat them as setter data to avoid SQL injection. 

Below is the modified code for LoginValidation:

String query = "select * from sample_data where username=? and password = ?";
Connection conn = null;
PreparedStatement stmt = null;
try {
    conn = DriverManager.getConnection("jdbc:mysql://127.0.0.1:3306/user", "root", "root");
    stmt = conn.prepareStatement(query);
    stmt.setString(1, username);
    stmt.setString(2, password);
    ResultSet rs = stmt.executeQuery();
    if (rs.next()) {
       success = true;
       }
      rs.close();
      } catch (Exception e) {
         e.printStackTrace();
         } finally {
             try {
                 stmt.close();
                 conn.close();
            } catch (Exception e) {
            }
         }

In this case, the PreparedStatement, the setters, and the underlying JDBC API will take care of the user input and prevent the SQL injection.

Examples

Now we’ll look at a few more examples in various languages to better understand what this looks like in action.

C# - Insecure

This example is insecure due to its use of `FromRawSql`. This method does not bind the parameters, or attempt to escape them. As such, this method should be avoided at all cost.

var blogs = context.Posts
    .FromRawSql("SELECT * FROM Posts WHERE state = {0} AND author = {1}", state, author)
    .ToList();

C# - Secure

This example is secure due to the `FromSqlInterpolated`, which takes the interpolated values and parameterizes them.

While this is generally secure, it runs the risk of being very similar to `FromRawSql` which is not secure. 

var blogs = context.Posts
    .FromSqlInterpolated($"SELECT * FROM Posts WHERE state = {state} AND author = {author}")
.ToList();

Java - Secure: Hibernate - Named Query + Native Query

Hibernate offers two method for consturcting queries in a safe way through its `Native Query`, and `Named Query`. Both allow for specifying locations for parameters.

@NamedNativeQuery(
        name = "find_post_by_state_and_author",
        query =
        "SELECT * " +
                "FROM Post " +
                "WHERE state = :state" + 
         " AND author = :author",
        resultClass = Post.class)

java
List<Post> posts = session.createNativeQuery(
        "SELECT * " +
        "FROM Post " +
        "WHERE state = :state" +
        " AND author = :author" )
        .addEntity(Post.class)
        .setParameter("state", state)
        .setParameter("author", author)
        .list();

Java - Secure: jplq

By annotating a `Query` attribute on a jplq repository interface, They can take multiple forms, and are parameterized.

@Query("SELECT p FROM Post p WHERE u.state = ?1 and u.author = ?2")
Post findPostByStateAndAuthor(String state, int author);

@Query("SELECT p FROM Post p WHERE u.state = :state and u.author = :author")
User findPostByStateAndAuthor(@Param("state") String state, @Param("author") int author);

Javascript - Secure: pg

When using the `pg` library, the `query` method allows for parameterization by providing parameter values through its second parameter.

const { posts } = await db.query('SELECT * FROM Post WHERE state = $1 AND author = $2', [state, author])

Javascript - Secure: Sequelize

The `sequelize` library provides a way to parameterize a query through its second argument, which takes settings for the query. This includes a list of values to bind to the query as a parameter, either by name or index.

await sequelize.query(
    'SELECT * FROM Post WHERE state = $state AND author = $author',
    {
        bind: { state: state, author: author},
        type: QueryTypes.SELECT
    }
);

await sequelize.query(
    'SELECT * FROM Post WHERE state = $1 AND author = $2',
    {
        bind: [state, author],
        type: QueryTypes.SELECT
    }
);