SQL injection and parameters

In short: SQL injection is an attack where user input pasted into a query runs as SQL, and a parameterized query (prepared statement) stops it.

Problem

The search box in the admin panel

The admin panel has a “Customer email” box. The program takes what was typed and puts it into the query:

SELECT name, email, city FROM customers WHERE email = '<what was typed>'

With maria.ivanova@example.com you get Maria. But what is typed can be any text, including a quote. Then it stops being a value and becomes part of the query. This is called SQL injection: the visitor writes SQL in the program's place.

Try it

Show all the customers

Try it yourself. The goal is to make the page show all the customers without knowing a single email. Watch the line “What reaches the database”: that exact text is what the database runs.

The page looks up a customer by email.database: shop

Customer email

Glued into the text As a parameter

▶ SearchFresh database

What reaches the database

More precisely, if you are curious

A hint: a quote closes the text, and after it you need a condition that is true for every row. For example x' OR '1'='1. You leave out the last quote, because the program adds it on its own.

See it

Two more tricks

The input does not have to be a clever condition. The comment -- removes everything after it, including a check the program has put after the email:

SQLdatabase: shop

SELECT name, email FROM customers WHERE email = 'maria.ivanova@example.com' --' AND city = 'Varna';

SELECT name, email FROM customers WHERE email = 'maria.ivanova@example.com' --' AND city = 'Varna';

▶ RunRestore the queryFresh database

The program looks for a customer with this email from Varna, and out comes Maria from Sofia: the check on the city ended up in the comment.

If the driver runs several statements from one text, the input x'; DELETE FROM customers; -- adds a whole new statement. Try it in the box above (then “Fresh database”). In the real world, input like this deletes tables, changes passwords or reads data it should never see.

The idea

The same in code: four drivers, four behaviours

Here is the search with glued text in all four languages. The program searches for three things: a real email, x' OR '1'='1 and x'; DELETE FROM customers; --, and at the end counts the customers:

Java

static void find(Connection conn, String email) throws SQLException {
    String sql = "SELECT name FROM customers WHERE email = '" + email + "'";
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery(sql)) {
        List<String> names = new ArrayList<>();
        while (rs.next()) {
            names.add(rs.getString("name"));
        }
        System.out.println(email + " → " + names);
    }
}

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
    setup(conn);
    find(conn, "maria.ivanova@example.com");
    find(conn, "x' OR '1'='1");
    try {
        find(conn, "x'; DELETE FROM customers; --");
    } catch (SQLException e) {
        System.out.println("error: " + e.getMessage());
    }
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM customers")) {
        rs.next();
        System.out.println("customers in the database: " + rs.getInt(1));
    }
}

C#

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
Setup(conn);
Find(conn, "maria.ivanova@example.com");
Find(conn, "x' OR '1'='1");
try
{
    Find(conn, "x'; DELETE FROM customers; --");
}
catch (SqliteException e)
{
    Console.WriteLine($"error: {e.Message}");
}
var count = conn.CreateCommand();
count.CommandText = "SELECT COUNT(*) FROM customers";
Console.WriteLine($"customers in the database: {count.ExecuteScalar()}");

static void Find(SqliteConnection conn, string email)
{
    var cmd = conn.CreateCommand();
    cmd.CommandText = $"SELECT name FROM customers WHERE email = '{email}'";
    using var reader = cmd.ExecuteReader();
    var names = new List<string>();
    while (reader.Read())
    {
        names.Add(reader.GetString(0));
    }
    Console.WriteLine($"{email} → [{string.Join(", ", names)}]");
}

Python

def find(conn, email):
    sql = f"SELECT name FROM customers WHERE email = '{email}'"
    names = [name for (name,) in conn.execute(sql)]
    print(f"{email} → [{', '.join(names)}]")


conn = sqlite3.connect(":memory:")
setup(conn)
find(conn, "maria.ivanova@example.com")
find(conn, "x' OR '1'='1")
try:
    find(conn, "x'; DELETE FROM customers; --")
except sqlite3.Error as e:
    print("error:", e)
print("customers in the database:", conn.execute("SELECT COUNT(*) FROM customers").fetchone()[0])
conn.close()

JavaScript

function find(db, email) {
  const sql = `SELECT name FROM customers WHERE email = '${email}'`;
  const names = db.prepare(sql).all().map((row) => row.name);
  console.log(`${email} → [${names.join(", ")}]`);
}

const db = new DatabaseSync(":memory:");
setup(db);
find(db, "maria.ivanova@example.com");
find(db, "x' OR '1'='1");
try {
  find(db, "x'; DELETE FROM customers; --");
} catch (e) {
  console.log("error:", e.message);
}
console.log("customers in the database:", db.prepare("SELECT COUNT(*) AS n FROM customers").get().n);
db.close();

Java

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
    setup(conn);
    find(conn, "maria.ivanova@example.com");
    find(conn, "x' OR '1'='1");
    try {
        find(conn, "x'; DELETE FROM customers; --");
    } catch (SQLException e) {
        System.out.println("error: " + e.getMessage());
    }
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM customers")) {
        rs.next();
        System.out.println("customers in the database: " + rs.getInt(1));
    }
}

Output

maria.ivanova@example.com → [Maria Ivanova]
x' OR '1'='1 → [Maria Ivanova, Georgi Petrov, Ivana Koleva, Dimitar Georgiev, Radost Hristova]
x'; DELETE FROM customers; -- → []
customers in the database: 5

C#

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
Setup(conn);
Find(conn, "maria.ivanova@example.com");
Find(conn, "x' OR '1'='1");
try
{
    Find(conn, "x'; DELETE FROM customers; --");
}
catch (SqliteException e)
{
    Console.WriteLine($"error: {e.Message}");
}
var count = conn.CreateCommand();
count.CommandText = "SELECT COUNT(*) FROM customers";
Console.WriteLine($"customers in the database: {count.ExecuteScalar()}");

Output

maria.ivanova@example.com → [Maria Ivanova]
x' OR '1'='1 → [Maria Ivanova, Georgi Petrov, Ivana Koleva, Dimitar Georgiev, Radost Hristova]
x'; DELETE FROM customers; -- → []
customers in the database: 0

Python

conn = sqlite3.connect(":memory:")
setup(conn)
find(conn, "maria.ivanova@example.com")
find(conn, "x' OR '1'='1")
try:
    find(conn, "x'; DELETE FROM customers; --")
except sqlite3.Error as e:
    print("error:", e)
print("customers in the database:", conn.execute("SELECT COUNT(*) FROM customers").fetchone()[0])
conn.close()

Output

maria.ivanova@example.com → [Maria Ivanova]
x' OR '1'='1 → [Maria Ivanova, Georgi Petrov, Ivana Koleva, Dimitar Georgiev, Radost Hristova]
error: You can only execute one statement at a time.
customers in the database: 5

JavaScript

const db = new DatabaseSync(":memory:");
setup(db);
find(db, "maria.ivanova@example.com");
find(db, "x' OR '1'='1");
try {
  find(db, "x'; DELETE FROM customers; --");
} catch (e) {
  console.log("error:", e.message);
}
console.log("customers in the database:", db.prepare("SELECT COUNT(*) AS n FROM customers").get().n);
db.close();

Output

maria.ivanova@example.com → [Maria Ivanova]
x' OR '1'='1 → [Maria Ivanova, Georgi Petrov, Ivana Koleva, Dimitar Georgiev, Radost Hristova]
x'; DELETE FROM customers; -- → []
customers in the database: 5

With OR '1'='1' all four show every customer. A second statement in the same text is a different story. In C# Microsoft.Data.Sqlite runs it, and the customers drop to 0. In Java executeQuery and in JavaScript prepare run only the first statement and say nothing about the rest, while Python stops with an error. The driver does not protect you: it only decides how big the damage is.

The idea

The parameter

The query has a ? (in C# $email) where the value goes, and the value is sent separately. The database first understands the query, then puts in the value, and the value stays text whatever it contains:

Java

static void find(Connection conn, String email) throws SQLException {
    String sql = "SELECT name FROM customers WHERE email = ?";
    try (PreparedStatement ps = conn.prepareStatement(sql)) {
        ps.setString(1, email);
        try (ResultSet rs = ps.executeQuery()) {
            List<String> names = new ArrayList<>();
            while (rs.next()) {
                names.add(rs.getString("name"));
            }
            System.out.println(email + " → " + names);
        }
    }
}

try (Connection conn = DriverManager.getConnection("jdbc:sqlite::memory:")) {
    setup(conn);
    find(conn, "maria.ivanova@example.com");
    find(conn, "x' OR '1'='1");
    try {
        find(conn, "x'; DELETE FROM customers; --");
    } catch (SQLException e) {
        System.out.println("error: " + e.getMessage());
    }
    try (Statement st = conn.createStatement();
         ResultSet rs = st.executeQuery("SELECT COUNT(*) FROM customers")) {
        rs.next();
        System.out.println("customers in the database: " + rs.getInt(1));
    }
}

Output

maria.ivanova@example.com → [Maria Ivanova]
x' OR '1'='1 → []
x'; DELETE FROM customers; -- → []
customers in the database: 5

C#

using var conn = new SqliteConnection("Data Source=:memory:");
conn.Open();
Setup(conn);
Find(conn, "maria.ivanova@example.com");
Find(conn, "x' OR '1'='1");
try
{
    Find(conn, "x'; DELETE FROM customers; --");
}
catch (SqliteException e)
{
    Console.WriteLine($"error: {e.Message}");
}
var count = conn.CreateCommand();
count.CommandText = "SELECT COUNT(*) FROM customers";
Console.WriteLine($"customers in the database: {count.ExecuteScalar()}");

static void Find(SqliteConnection conn, string email)
{
    var cmd = conn.CreateCommand();
    cmd.CommandText = "SELECT name FROM customers WHERE email = $email";
    cmd.Parameters.AddWithValue("$email", email);
    using var reader = cmd.ExecuteReader();
    var names = new List<string>();
    while (reader.Read())
    {
        names.Add(reader.GetString(0));
    }
    Console.WriteLine($"{email} → [{string.Join(", ", names)}]");
}

Output

maria.ivanova@example.com → [Maria Ivanova]
x' OR '1'='1 → []
x'; DELETE FROM customers; -- → []
customers in the database: 5

Python

def find(conn, email):
    sql = "SELECT name FROM customers WHERE email = ?"
    names = [name for (name,) in conn.execute(sql, (email,))]
    print(f"{email} → [{', '.join(names)}]")


conn = sqlite3.connect(":memory:")
setup(conn)
find(conn, "maria.ivanova@example.com")
find(conn, "x' OR '1'='1")
try:
    find(conn, "x'; DELETE FROM customers; --")
except sqlite3.Error as e:
    print("error:", e)
print("customers in the database:", conn.execute("SELECT COUNT(*) FROM customers").fetchone()[0])
conn.close()

Output

maria.ivanova@example.com → [Maria Ivanova]
x' OR '1'='1 → []
x'; DELETE FROM customers; -- → []
customers in the database: 5

JavaScript

function find(db, email) {
  const sql = "SELECT name FROM customers WHERE email = ?";
  const names = db.prepare(sql).all(email).map((row) => row.name);
  console.log(`${email} → [${names.join(", ")}]`);
}

const db = new DatabaseSync(":memory:");
setup(db);
find(db, "maria.ivanova@example.com");
find(db, "x' OR '1'='1");
try {
  find(db, "x'; DELETE FROM customers; --");
} catch (e) {
  console.log("error:", e.message);
}
console.log("customers in the database:", db.prepare("SELECT COUNT(*) AS n FROM customers").get().n);
db.close();

Output

maria.ivanova@example.com → [Maria Ivanova]
x' OR '1'='1 → []
x'; DELETE FROM customers; -- → []
customers in the database: 5

Now x' OR '1'='1 is just an email that does not exist, and the result is empty. In the box above choose “As a parameter” and run the same inputs: the query that reaches the database no longer changes.

More precisely, if you are curious

Every language and every driver has parameters: PreparedStatement in Java, Parameters in C#, the second argument of execute in Python, the arguments of all / get / run in node:sqlite. PostgreSQL drivers use $1, $2 (pg) or %s (psycopg). A parameter usually speeds up repeated queries too, because the database can reuse a query it has already understood.

A parameter is for values. Table and column names (for example the column in ORDER BY picked from a drop-down) cannot be parameters: there the program compares the input with a list of the allowed ones (price, name) and lets nothing else through.

Exercise

Search by part of the email

The search also accepts part of an email. The program sends the query in the box with the parameter “petrov”. Replace ? with the value to see what the database will return.

SQLdatabase: shop

SELECT name, email FROM customers WHERE email LIKE '%' || ? || '%';

SELECT name, email FROM customers WHERE email LIKE '%' || ? || '%';

Check▶ Run

Test

Check yourself

1. The program builds the query like this: "... WHERE email = '" + email + "'". What makes it safe?

Removing the quotes from the inputA query with ? and the value as a parameterKeeping the input shorter than 50 characters

2. Why does “x' OR '1'='1” return all the customers?

Because OR is an error and the database skips the WHEREBecause the condition becomes email = 'x' OR '1'='1', and the second part is true for every rowBecause the database does not find the email and returns everything

3. Can a parameter replace the column name in ORDER BY (ORDER BY ?)?

No: a parameter is a value, not a name. The column is chosen from a list in the codeYes, like any value

4. In Python, cursor.execute() with two statements in the text raises an error. Does that mean injection is not dangerous in Python?

No: OR '1'='1', UNION and the -- comment work with a single statement tooYes

Check

Start the lesson over

Related lessons

• Builds on: IN, BETWEEN and LIKE · String functions · DELETE: deleting rows · Connecting to the database from code
• Continues in: ORM and the N+1 problem · Connection pools, backups and permissions
• Tasks on this topic: The database in a program