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.
SOCIAL SHARE CARD GENERATOR