SQL Injections

Lecture Notes

This lecture will present an overview over SQL Injections with the multiple variants

Download here

Practical tasks

The objective of this lab is to explore Injection Attacks, in particular SQL Injection. This guide will be split into two parts. The first will make use of BWAPP, while the second part will focus on exploiting an actual system that suffers from these vulnerabilities.

If you require assistance remembering some of the SQL syntax, check the SQL Zoo

For many of this exercises it is relevant to use the Zed Attack Proxy or Burp Suite. These tools will interact with your browser, either directly or through a HTTP Proxy, and allow you to capture, craft and send requests as required for each exercise.

bWAPP

bWAPP is a free, open-source and intentionally unreliable web application, or a web buggy program. It helps security enthusiasts, designers and students discover Web bugs and stop them from doing so. bWAPP plans for positive penetration tests and cyber ethics initiatives.

Download it from the bWAPP website, or run a docker container (we advise this approach)

docker run -d -p 80:80 raesene/bwapp

After this step, just point your browser to http://localhost/install.php, install bWAPP and follow the guide.

When the installation is completed, head to the website and create a user. Enter any data you wish.

Solutions for the bWAPP challenges are available at:

The SQL Injection Challenges

In order to be able to select a challenge, there is a registration requirement. However, users have the possibility to register with any data they wish. Afterwards, an SQL injection task can be selected from various ‘bugs’.

GET/SEARCH

For the selected (GET/SEARCH) SQL-Injection, information about available movies is displayed in a table when entering a movie title. Here you can search for one or more movies. As a result of the search, the movie details are displayed. If the input button is clicked without entering a search term, all movies are displayed. The set goal is to get a user password which seems to be possible as we have a table.

When a table is present, there is always the possibility of filling it with user specified data and obtain information through the said table. However first we must find how data is handled and what is vulnerable.

Detect the vulnerability

bWAPP is asking us to search the database for our favorite movie which contains the word ‘iron’ and the search field gives the result ‘iron man’. In this case there is a high probability that this query contains some columns.

The select-command is something like SELECT column1, column2, column3, column4 from table where movie LIKE '%". $userinput."%'

Task

  • Find if the field is vulnerable and what kind of SQL Injection is available

Find the number of columns

If data is to be obtain, the number of columns must be known to the attacker. Only after this step he can put the information in a format compatible by the existing code. We know that the table has 5 columns, but we do not know how many others are fetch from the database. This is a trial an error task, which can be done in several ways. One that works is using the ORDER BY column_number clause.

Tasks

  • Define a strategy to find the number of columns. How many columns we have?
  • How many columns we have?
  • What kind of SQL Injection was used?

Uniting queries

Until now we know the number of columns and we suspect what the query looks like:

SELECT column1, column2, column3, column4 from table where movie LIKE '%". $userinput."%'

We also know that we have a table filled with information from a database. What we want is to use it to exfiltrate information from another table. But first it is important to consider the UNION keyword. When used after a SELECT statement, the UNION statement allows to concatenate the results from another query into the first one.

That is, the first query is executed and it is united with another query. This second query will be controlled by the attacker. An important issue is that the number of columns must match. While we could do ... UNION SELECT 1,2,3. The second SELECT clause must have the first number of columns. Because we already know it, we can move forward.

Tasks

  • Add a second query with the adequate number of columns

Exfiltrate Data

Now that it is possible to concatenate information, it is trivial to extract data from other tables. Instead of issuing a SELECT 1,2,3 you can actually address existing tables.

Tasks

  • Extract information from users
  • Obtain all user information, including passwords
  • Obtain the list of databases
  • Obtain the list of tables
  • Load a file from the server

Other SQL Challenges

The remaining SQL Challenges are also very relevant to this lab, and the mostly explore the same attack, but present different interfaces, or require different SQL Injection types.

Tasks

  • Solve the SQL Injection - Blind - Boolean Based
  • Solve the SQL Injection - Blind - Time-Based
  • Solve the SQL Injection (GET/Select)
  • Solve the SQL Injection (POST/Search)
  • Solve the SQL Injection - Stored (User-Agent)

Do not forget to document the type of SQL Injection used, the number of columns, and the queries used to obtain the information.

This tool also exploits a common Pitfall: Developers frequently forget about different interaction patterns, or consider that data may be mangled. One good example is the GET/Select exercise: the select field contains a limited number of values, but an attacker may introduce more and conduct an attack this way.

Solutions for these challenges can be found here. Use them to guide you through the challenges.

Juice Shop

To install Juice Shop, follow the instructions at: https://owasp.org/www-project-juice-shop/ The installation is very simple, and the application can be run as a Docker container:

   docker pull bkimminich/juice-shop
   docker run -d -p 3000:3000 -e "NODE_ENV=unsafe" bkimminich/juice-shop

Notice the use of NODE_ENV=unsafe. This environmental variable will enable potentially unsafe challenges. This will not be an issue for you as you are using a Virtual Machine, and the instance will not be public. Do not use this settings in a public instance of Juice Shop.

After starting the Docker container, Juice Shop should be accessible at http://localhost:3000. Verify that the application is running by visiting this URL in a browser. You should see the Juice Shop home page.

The Juice Shop application contains a challenge named ‘Login Admin’, which can be solved through SQL Injection. The goal is to login as the admin user, without knowing the password. The login page is available at http://localhost:3000/#/login. The admin user is the first user in the database, and has the email admin@juice-sh.op.

Let us inject SQL into the login field to bypass the login and login as the first user in the database. First, create an error by giving ' as input to the email field, and any string to the password field.

You can use the ZAP Proxy or Burp Suite to intercept the request, or simply use the browser developer tools to inspect the request being sent.

Check the Response in the browser Network tab. You can see the SQL query used in the login. In Burp Suite or ZAP Proxy, you can see the response in the intercepted request.

"SELECT * FROM Users WHERE email = ''' AND password = '698d51a19d8a121ce581499d7b701668' AND deletedAt IS NULL"

Here we used ' in the email input field to cause an SQL error.

Now we know the SQL query related to logging in. We can send ' OR TRUE -- as email input and any string as a password.

"SELECT * FROM Users WHERE email = '' OR TRUE -- AND password = '698d51a19d8a121ce581499d7b701668' AND deletedAt IS NULL"
  • ': character close the email string.
  • OR: is a SQL query
  • TRUE: is a boolean value
  • --: will comment out the SQL query after the TRUE

So, now the SQL will check for email = '' or true which is always a TRUE statement. This SQL injection will log us as the first user in the database.

Order the Christmas special offer

We will use SQL Injections in to order a product that is not supposed to be available any more. This works because products have states, and deleted products are only marked as deleted, but not removed from the database. This is a common approach to avoid losing data.

We need to discover the id of the product, and then add it to our shopping cart. Finally we will checkout and complete the order. The product we want to order is the Christmas special offer of 2014. It is not available anymore, but it is still in the database, marked as deleted.

The search button allows filtering products by name. This is a good starting point to find the id of the product. However, the search button is not vulnerable to SQL Injection. We need to find another way to inject SQL.

If you look at the requests being made, notice the use of /rest/products/search?q=. This is a GET request, and the q parameter is directly used in the SQL query. Check the output of the request when using q=, q=; or q='. You should be able to find a SQL error message. This means that the q parameter is vulnerable to SQL Injection.

Use of the jq tool is recommended to parse the JSON output of the requests. You can install it with sudo apt install jq.

Task:

  • Find out the id Christmas Super-Surprise-Box (2014 Edition)
  • Add a product to your shopping cart and analyze the request
  • Add the Christmas Super-Surprise-Box (2014 Edition) to your shopping cart
  • Checkout and complete the order

Database Exfiltration

You try UNION attacks to find the number of columns, or dump the database. However, the output is in JSON format, which makes it difficult to read. Use ')) UNION SELECT 1,2,3... -- to find the number of columns. You should find that there are 9 columns. From this, you can get up to 9 pieces of information from any single table. There is a interesting table name sqlite_master which contains the database schema. You can use it to find the names of all tables in the database (name column). You can also use it to find the names of all columns in a table (the sql column).

Tasks:

  • Dump all table names
  • Dump all column names for any table
  • Dump the users
  • Dump the products
  • Dump the orders

Find out how the application hides deleted products from its customers. Try to craft an attack string that makes deleted products visible again. You need to get the deleted product into your shopping cart and trigger the Checkout. Neither of the above can be achieved through the application frontend and it might even require (half-)Blind SQL Injection. Exfiltrate the entire DB schema definition via SQL Injection An attacker would try to exploit SQL Injection to find out as much as possible about your database schema. This subsequently allows much more targeted, stealthy and devastating SQL Injections, like Retrieve a list of all user credentials via SQL Injection.

Find out which database system is in use and where it would usually store its schema definitions. Craft a UNION SELECT attack string to join the relevant data from any such identified system table into the original result. You might have to tackle some query syntax issues step-by-step, basically hopping from one error to the next As with Order the Christmas special offer of 2014 this cannot be achieved through the application frontend.

SQL Injection Attack Lab

This exercise, provided by SEELabs (https://seedsecuritylabs.org/Labs_20.04/Web/Web_SQL_Injection/) contains a simple application and a database. You can use docker compose to setup a personal environment and correlate what you observe with the actual code.

In this lab, it was created a web application that is vulnerable to the SQL injection attack. The web application includes the common mistakes made by many web developers. The goal is to find ways to exploit the SQL injection vulnerabilities, demonstrate the damage that can be achieved by the attack, and master the techniques that can help defend against such type of attacks.

To setup the lab, obtain this file, unzip it and start it with docker compose up --build -d.

If you have issues with docker compose, try docker-compose instead. If you need to install docker compose, do sudo apt install docker-compose.

To exploit the application from the webpage, try providing admin' # as the username. This should allow you to detect wether the page is vulnerable or not. You can also follow this approach from the command line, using curl, wget or even a python script using the requests package.

curl http://localhost/unsafe_home.php?username=Admin%27%20%23

You can be more creative and use the vulnerability to modify your own salary. Employees can only update their nicknames, emails, addresses, phone numbers, and passwords; they are not authorized to change their salaries. If you want to increase your own salary, exploiting the SQL injection vulnerability in the Edit-Profile page may allow it. The salaries are stored in a column called salary.

Use the statement in the NickName field: ', Salary=10000 where name = ‘Alice’ # and then update it.

You can also change others salary. Use statement in the profile editor: ', Salary=1 where name = ‘VICTIM’ #

Finally, you can change others passwords. The passwords are stored in SHA1 format. Therefore you can create a password to a user, and exploit the site using ', password=(SHA1 Password) where name = ‘VICTIM’ #.

The countermeasures for this attack are Prepared Statements. The code includes a safe version at safe_home.php. Use this home and check if the attacks are possible. In order to isolate the countermeasures, you can copy lines 70-80 from safe_home.php into lines 70-100 of the unsafe_home.php file. If you do this you will need to rebuild the docker images with docker compose up --build --force -d

Fixing SQL Injections

The best way to fix SQL Injections is to use Prepared Statements. This approach will separate code from data, and avoid the possibility of an attacker injecting SQL code. Prepared Statements are available in most programming languages. An ORM (Object Relational Mapper) will also use Prepared Statements, and is a good approach to avoid SQL Injections.

The following text will create a simple SQLite database with a users table, which will be used in the next examples.

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL,
    password TEXT NOT NULL,
    email TEXT NOT NULL
);

PHP

The following PHP code contains a SQL Injection vulnerability.

<?php
// Create a new SQLite3 connection
$db = new SQLite3('./database.db');
// Execute a query with user input directly in the SQL statement
$results = $db->query("SELECT * FROM users WHERE username = '$username' AND password = '$password'");
?>

Inspect the code and find the SQL Injection vulnerability. In PHP, you can use the PDO library to create prepared statements. Here is an example of a prepared statement in PHP:

<?php
// Create a new PDO connection
$pdo = new PDO('sqlite:./database.db');
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
// Prepare the SQL statement with placeholders
$stmt = $pdo->prepare('SELECT * FROM users WHERE username = :username AND password = :password');
// Bind the actual values to the placeholders
$stmt->bindParam(':username', $username);
$stmt->bindParam(':password', $password);
// Execute the statement
$stmt->execute();
// Fetch the results
$results = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>

In this example, the SQL statement is prepared with placeholders for the username and password. The actual values are then bound to the placeholders using the bindParam method. This approach ensures that the values are treated as data, and not as part of the SQL code, thus preventing SQL Injection attacks.

The documentation for the PDO library is available at: https://www.php.net/manual/en/book.pdo.php where you can find information about Prepared Statements. For ORM, you can use Eloquent, which is part of the Laravel framework.

Python

The following Python code contains a SQL Injection vulnerability.

import sqlite3

def get_user(username, password):
    conn = sqlite3.connect('database.db')
    cursor = conn.cursor()
    cursor.execute(f"SELECT * FROM users WHERE username = '{username}' AND password = '{password}'")
    return cursor.fetchall()

Inspect the code and find the SQL Injection vulnerability. Fix it using Prepared Statements and ORM. The documentation for the sqlite3 library is available at: https://docs.python.org/3/library/sqlite3.html where you can find information about Prepared Statements. For ORM, you can use SQLAlchemy: https://www.sqlalchemy.org/

Java

The following Java code contains a SQL Injection vulnerability. Fix it using Prepared Statements.

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.PreparedStatement;

public class SQLInjectionExample {
    public static void main(String[] args) {
        String username = "user"; // User input
        String password = "pass"; // User input

        try {
            Connection conn = DriverManager.getConnection("jdbc:sqlite:database.db");
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery("SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'");
            
            while (rs.next()) {
                System.out.println("User ID: " + rs.getInt("id"));
            }
            rs.close();
            stmt.close();
            conn.close();
        } catch (Exception e) {
            e.printStackTrace();
        }   
    }
}

Inspect the code and find the SQL Injection vulnerability. Fix it using Prepared Statements and ORM. The documentation for JDBC is available at: https://docs.oracle.com/javase/tutorial/jdbc/basics/index.html where you can find information about Prepared Statements. For ORM, you can use Hibernate: https://hibernate.org/orm/documentation/

Ruby

The following Ruby code contains a SQL Injection vulnerability.

require 'sqlite3'

def get_user(username, password)
    db = SQLite3::Database.new('database.db')
    db.execute("SELECT * FROM users WHERE username = ? AND password = ?", [username, password])
end

Inspect the code and find the SQL Injection vulnerability. The documentation for the sqlite3 gem is available at: https://github.com/sparklemotion/sqlite3-ruby where you can find information about Prepared Statements. For ORM, you can use ActiveRecord: https://guides.rubyonrails.org/

Go

The following Go code contains a SQL Injection vulnerability. Fix it using Prepared Statements or an ORM.

package main
import (
    "database/sql"
    "fmt"
    _ "github.com/mattn/go-sqlite3"
)
func getUser(db *sql.DB, username, password string) {
    query := fmt.Sprintf("SELECT * FROM users WHERE username = '%s' AND password = '%s'", username, password)
    rows, err := db.Query(query)
    if err != nil {
        fmt.Println(err)
        return
    }
    defer rows.Close()
    for rows.Next() {
        var id int
        var name string
        var pass string
        rows.Scan(&id, &name, &pass)
        fmt.Println(id, name, pass)
    }
}
func main() {
    db, err := sql.Open("sqlite3", "./database.db")
    if err != nil {
        fmt.Println(err)
        return
    }
    defer db.Close()
    getUser(db, "user", "pass")
}

Inspect the code and find the SQL Injection vulnerability. The documentation for the database/sql package is available at: https://pkg.go.dev/database/sql where you can find information about Prepared Statements. For ORM, you can use GORM: https://gorm.io/docs/

External exercises

Relevant Software:

Resources:

Previous
Next

Last updated on 14 Sep 2025