Learning About Security: SQL Injection

What is SQL Injection?

SQL Injection is an attack technique that manipulates SQL queries by inserting malicious code, allowing unauthorized operations on the database.

SQL Injection Attack Example

Using Bun's SQLite3 driver and Hono, we’ll set up two servers: one on localhost:3000 (target) and one on localhost:4000 (attacker) to demonstrate an SQL Injection attack.

Target Server

On the target server, we create a /user endpoint to retrieve data from a users table based on an id specified in the URL query parameter. We’ll populate this users table with a few records.

import { Hono } from "hono";
import { Database } from "bun:sqlite";

const app = new Hono();
const db = new Database(":memory:");

db.run(
  "CREATE TABLE IF NOT EXISTS users (id INTEGER PRIMARY KEY, username TEXT, password TEXT, role TEXT)"
);
db.run("INSERT INTO users (username, password, role) VALUES ('admin', 'adminpass', 'admin')");
db.run("INSERT INTO users (username, password, role) VALUES ('john_doe', 'john123', 'user')");
db.run("INSERT INTO users (username, password, role) VALUES ('jane_doe', 'jane123', 'user')");
db.run("INSERT INTO users (username, password, role) VALUES ('alice', 'alice123', 'user')");
db.run("INSERT INTO users (username, password, role) VALUES ('bob', 'bob123', 'user')");
db.run("INSERT INTO users (username, password, role) VALUES ('charlie', 'charlie123', 'user')");

app.get("/user", (c) => {
  const id = c.req.query("id");
  const query = db.query(`SELECT * FROM users WHERE id = ${id}`);
  const user = query.all();
  return c.json(user);
});

export default app;

Attacker Server

On the attacker server, we create an /attack endpoint that sends a request to the /user endpoint on the target server with a SQL Injection attempt.

import { Hono } from "hono";

const app = new Hono();

app.get("/attack", async (c) => {
  const response = await fetch("http://localhost:3000/user?id=1 OR 1=1");
  const data = await response.json();
  return c.json(data);
});

export default {
  port: 4000,
  fetch: app.fetch,
};

Launching the Attack

By accessing http://localhost:4000/attack, the following JSON response is returned:

[
  {"id":1,"username":"admin","password":"adminpass","role":"admin"},
  {"id":2,"username":"john_doe","password":"john123","role":"user"},
  {"id":3,"username":"jane_doe","password":"jane123","role":"user"},
  {"id":4,"username":"alice","password":"alice123","role":"user"},
  {"id":5,"username":"bob","password":"bob123","role":"user"},
  {"id":6,"username":"charlie","password":"charlie123","role":"user"}
]

This occurs because the target server executes SELECT * FROM users WHERE id=1 OR 1=1. The 1=1 condition is always true, so it includes all rows in the query result, revealing all user data.

SQL Injection Mitigation

One way to prevent SQL Injection is to use parameterized queries. With parameterized queries, user input is treated as data, not executable SQL, nullifying injection attempts.

Fixing the Target Code

Here’s how we can modify the target server’s /user endpoint to prevent SQL Injection:

app.get("/user", (c) => {
  const id = c.req.query("id");
  const query = db.query(`SELECT * FROM users WHERE id = ?`);
  const user = query.all(id);
  return c.json(user);
});

With this change, an attack attempt will return an empty JSON.

Cases Where Parameterized Queries Are Ineffective

If table names or column names are dynamically modified based on user input, parameterized queries may not offer protection. This could still result in unauthorized data access.

Example with Dynamic Table Name

Sending a table=sqlite_master -- parameter retrieves database schema information, as sqlite_master holds metadata in SQLite.

Target Server Code

app.get("/select_table", (c) => {
  const table = c.req.query("table");
  const query = db.query(`SELECT * FROM ${table} WHERE role = ?`);
  const results = query.all("user");

  return c.json({
    message: `Data from table ${table}`,
    data: results,
  });
});

Attacker Server Code

app.get("/attack_table", async (c) => {
  const targetUrl = "http://localhost:3000/select_table?table=sqlite_master --";
  const response = await fetch(targetUrl);
  const result = await response.json();

  return c.json(result);
});

Mitigation Using a Whitelist

Since table names cannot be parameterized, validating them with a whitelist is necessary to prevent SQL Injection.

app.get("/select_table", (c) => {
  const table = c.req.query("table");

  // Validate table name using a whitelist
  const allowedTables = ["users"];
  if (!allowedTables.includes(table)) {
    return c.json({ message: "Invalid table name" }, 400);
  }

  const query = db.query(`SELECT id, username, role FROM ${table} WHERE role = ?`);
  const results = query.all("user");

  return c.json({
    message: `Data from table ${table}`,
    data: results,
  });
});

By enforcing a whitelist, we ensure only authorized tables are queried, providing a safeguard against SQL Injection in cases where parameterization isn’t possible.