SQL Injection Explained: How to Prevent It in PHP, Python and Node.js with Prepared Statements

Submitted by jaypabs on Sunday, November 8, 2026 - 17:35.

SQL injection is one of the oldest web security bugs, and it still shows up in student projects, freelance work and even production apps. The cause is almost always the same line of code: user input pasted directly into an SQL string. The fix is also almost always the same: prepared statements.

In this guide you will see what SQL injection looks like, why escaping and "cleaning" input is not enough, and how to write safe queries in PHP (PDO), Python (sqlite3) and Node.js. Every example below is a small runnable script, and the output under each one was copied from a real run (PHP 8.3, Python 3.13, Node.js 24 and MariaDB 11.8). The demos use SQLite in memory where possible, so you can try them without setting up a database server.

What Is SQL Injection?

SQL injection happens when text typed by a user changes the structure of your SQL query instead of being treated as plain data. Look at this common login query:

$sql = "SELECT * FROM users WHERE username = '$username'";

If someone types admin' -- as the username, the query becomes ... WHERE username = 'admin' --'. The quote closes the string early, and -- turns the rest of the line into a comment. The attacker has rewritten your query. Depending on the code, that can mean logging in without a password, reading other tables, or changing data.

Example 1: A Vulnerable Login vs a Safe One in PHP

This script creates a user, then runs the same input through a vulnerable query and a prepared statement. It also shows the correct way to check a password with password_verify():

<?php
// Demo: vulnerable vs safe login check (SQLite in memory, runs anywhere)
$pdo = new PDO('sqlite::memory:', null, null, [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION]);
$pdo->exec("CREATE TABLE users (id INTEGER PRIMARY KEY, username TEXT, password_hash TEXT)");
$stmt = $pdo->prepare("INSERT INTO users (username, password_hash) VALUES (?, ?)");
$stmt->execute(['admin', password_hash('S3cret!pass', PASSWORD_DEFAULT)]);

$attack = "admin' --";

// VULNERABLE: user input is glued into the SQL string
$sql = "SELECT id, username FROM users WHERE username = '$attack'";
echo "Vulnerable SQL: $sql\n";
$row = $pdo->query($sql)->fetch(PDO::FETCH_ASSOC);
echo "Vulnerable result: " . ($row ? "found user {$row['username']}" : "no user") . "\n";

// SAFE: prepared statement, the input is only ever data
$stmt = $pdo->prepare("SELECT id, username, password_hash FROM users WHERE username = ?");
$stmt->execute([$attack]);
$row = $stmt->fetch(PDO::FETCH_ASSOC);
echo "Prepared result: " . ($row ? "found user {$row['username']}" : "no user") . "\n";

// Correct login: look up by username, then verify the hash in PHP
function login(PDO $pdo, string $username, string $password): bool {
    $stmt = $pdo->prepare("SELECT password_hash FROM users WHERE username = :u");
    $stmt->execute([':u' => $username]);
    $hash = $stmt->fetchColumn();
    return $hash !== false && password_verify($password, $hash);
}
var_dump(login($pdo, "admin' --", 'anything'));
var_dump(login($pdo, 'admin', 'wrong'));
var_dump(login($pdo, 'admin', 'S3cret!pass'));

Output:

Vulnerable SQL: SELECT id, username FROM users WHERE username = 'admin' --'
Vulnerable result: found user admin
Prepared result: no user
bool(false)
bool(false)
bool(true)

The vulnerable query found the admin account using only admin' --. The prepared statement searched for a user literally named admin' --, found nothing, and the login function returned false. Notice the pattern in login(): fetch the hash by username, then compare it in PHP with password_verify(). Never put the password itself in the WHERE clause.

Why Prepared Statements Work

With a prepared statement, the database receives the SQL and the values separately. The query is parsed first, with placeholders like ? or :name, and the values are sent afterward. No matter what characters a value contains (quotes, dashes, semicolons), it can only ever be a value. That is why prepared statements beat addslashes(), mysqli_real_escape_string() or "remove all quotes" tricks: those try to guess every dangerous character, and they also break honest input like the name O'Brien.

Example 2: PDO with MySQL or MariaDB (XAMPP Setup)

Most PHP projects on XAMPP talk to MySQL. Here is the same idea with a real MariaDB server. Change the port to 3306 and the database name to match your setup:

<?php
// PDO + MySQL/MariaDB (XAMPP: host localhost, user root, empty password)
$pdo = new PDO('mysql:host=127.0.0.1;port=3307;dbname=shop;charset=utf8mb4', 'root', '', [
    PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES => false,   // use real server-side prepared statements
]);

// Pretend this came from $_GET['student_no']
$studentNo = "2026-0001' OR '1'='1";

$stmt = $pdo->prepare('SELECT id, name FROM students WHERE student_no = :no');
$stmt->execute([':no' => $studentNo]);
echo "Rows for attack input: " . count($stmt->fetchAll()) . "\n";

$stmt->execute([':no' => '2026-0001']);    // reuse the same statement
echo "Found: " . $stmt->fetch()['name'] . "\n";

// INSERT: a name with an apostrophe no longer breaks the query
$ins = $pdo->prepare('INSERT INTO students (student_no, name) VALUES (?, ?)');
$ins->execute(['2026-0003', "Mark O'Brien"]);
echo "Inserted id " . $pdo->lastInsertId() . ": Mark O'Brien\n";

// LIKE search: put the % signs in the value
$stmt = $pdo->prepare('SELECT name FROM students WHERE name LIKE ? ORDER BY name');
$stmt->execute(['%' . "o'b" . '%']);
echo "Search o'b: " . implode(', ', array_column($stmt->fetchAll(), 'name')) . "\n";

Output:

Rows for attack input: 0
Found: Ana Cruz
Inserted id 3: Mark O'Brien
Search o'b: Mark O'Brien

Three habits are worth copying from this script: set PDO::ERRMODE_EXCEPTION so errors are not silently ignored, turn off emulated prepares so MySQL does the real parameter binding, and put the % wildcards for LIKE in the value, not in the SQL string. If you use mysqli instead of PDO, the same protection is available through $conn->prepare() and bind_param().

Example 3: Python sqlite3, UNION Attacks and ORDER BY

Python's f-strings make it very tempting to build SQL the unsafe way. This demo shows a UNION attack against a product search, the safe version, and how to handle a sort column, which cannot be a placeholder:

import sqlite3

db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, price REAL);
CREATE TABLE users (id INTEGER PRIMARY KEY, email TEXT, password_hash TEXT);
INSERT INTO products (name, price) VALUES ('USB Mouse', 250), ('Keyboard', 450), ('Monitor', 6500);
INSERT INTO users (email, password_hash) VALUES ('[email protected]', 'not-a-real-hash');
""")

attack = "zzz' UNION SELECT id, email, password_hash FROM users --"

# VULNERABLE: f-string builds the SQL
sql = f"SELECT id, name, price FROM products WHERE name LIKE '%{attack}%'"
print("Vulnerable rows:", db.execute(sql).fetchall())

# SAFE: ? placeholder, wildcards added to the value, not to the SQL
rows = db.execute("SELECT id, name, price FROM products WHERE name LIKE ?", (f"%{attack}%",)).fetchall()
print("Safe rows:", rows)
rows = db.execute("SELECT id, name, price FROM products WHERE name LIKE ?", ("%key%",)).fetchall()
print("Normal search:", rows)

# Column names cannot be placeholders: use an allow-list for ORDER BY
def list_products(sort):
    allowed = {"name": "name", "price": "price", "newest": "id DESC"}
    order = allowed.get(sort, "name")
    return db.execute(f"SELECT name, price FROM products ORDER BY {order}").fetchall()

print("Sort by price:", list_products("price"))
print("Sort attack:", list_products("price; DROP TABLE users"))
print("users table still there:", db.execute("SELECT COUNT(*) FROM users").fetchone()[0])

Output:

Vulnerable rows: [(1, '[email protected]', 'not-a-real-hash')]
Safe rows: []
Normal search: [(2, 'Keyboard', 450.0)]
Sort by price: [('USB Mouse', 250.0), ('Keyboard', 450.0), ('Monitor', 6500.0)]
Sort attack: [('Keyboard', 450.0), ('Monitor', 6500.0), ('USB Mouse', 250.0)]
users table still there: 1

The vulnerable search returned a row from the users table, including an email and password hash, through a product search box. With the ? placeholder, the same input simply matched no products. The sort example shows an important limit: placeholders work for values, not for table or column names. For those, map the user's choice to a fixed list of allowed SQL fragments, exactly like the allowed dictionary. Anything not on the list falls back to a safe default.

Example 4: Node.js, Numeric IDs and IN Lists

Injection is not only about quotes. Numeric IDs taken from a URL are just as risky. This example uses the node:sqlite module that ships with recent Node.js versions; the same placeholder style works in mysql2 and pg (which uses $1, $2):

import { DatabaseSync } from 'node:sqlite';

const db = new DatabaseSync(':memory:');
db.exec(`CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT, total REAL);
         INSERT INTO orders (customer, total) VALUES ('Ana', 950), ('Ben', 6500), ('Carla', 300);`);

// Input from a URL like /orders?id=1 OR 1=1
const input = '1 OR 1=1';

// VULNERABLE: template literal puts the input inside the SQL
const bad = db.prepare(`SELECT * FROM orders WHERE id = ${input}`).all();
console.log('Vulnerable:', bad.length, 'rows returned');

// SAFE: placeholder + validate the type first
const id = Number.parseInt(input, 10);
console.log('Parsed id:', id);
const good = db.prepare('SELECT * FROM orders WHERE id = ?').all(id);
console.log('Safe:', good.length, 'row returned', good.map(r => r.customer));

// SAFE: IN (...) list with one placeholder per value
const ids = [1, 3];
const marks = ids.map(() => '?').join(', ');
const some = db.prepare(`SELECT customer FROM orders WHERE id IN (${marks})`).all(...ids);
console.log('IN list:', some.map(r => r.customer));

Output:

Vulnerable: 3 rows returned
Parsed id: 1
Safe: 1 row returned [ 'Ana' ]
IN list: [ 'Ana', 'Carla' ]

1 OR 1=1 turned a "show one order" page into "show every order". The safe version validates the input as an integer and passes it through a placeholder. For IN (...) lists, generate one ? per value and pass the values separately. Never join the array into the SQL text.

SQL Injection Prevention Checklist

Use prepared statements for every query that includes outside data: form fields, URL parameters, cookies, headers, uploaded file names and even data from your own database that originally came from users.
Use allow-lists for identifiers such as sort columns, sort direction and table names.
Validate types early: an ID should be an integer, a date should be a date.
Connect with a limited database user. Your web app rarely needs permission to drop tables or create users.
Do not show raw database errors to visitors. Log them and show a friendly message instead.
Hash passwords with password_hash() in PHP or a library like bcrypt or Argon2 in other languages, so a leak does not expose real passwords.
Use a static analysis tool. For PHP, tools like Psalm can trace user input into SQL queries and warn you before you ship.

What About ORMs?

Frameworks like Laravel (Eloquent), Django ORM and Prisma use parameter binding for you, which removes most of the risk. The danger returns when you drop down to raw SQL with string building, for example DB::raw() or whereRaw() in Laravel, or .raw() and .extra() in Django, with user input inside. When you need raw SQL in a framework, use its placeholder syntax there too.

Conclusion

SQL injection is completely preventable. Keep SQL and data separate with prepared statements, use allow-lists for anything that cannot be a placeholder, validate types, and give your app only the database permissions it needs. If you are maintaining an older project that builds queries with string concatenation, start by searching your code for "SELECT, "INSERT, "UPDATE and "DELETE strings that include a variable, and convert them one by one. It is one of the best security upgrades you can make in an afternoon.