SQL Injection & Security

SQL injection is one of the oldest and most damaging security flaws on the web, and it's entirely preventable. This lesson shows how it happens, the one reliable fix — parameterised queries — and the database-side practices that limit damage when something goes wrong.

How injection happens

Imagine a login page whose code builds SQL by pasting user input into a string:

// ✗ NEVER do this
const sql = "SELECT * FROM users WHERE email = '" + email + "' AND password_hash = '" + hash + "'";

If someone types this as their email:

' OR '1'='1' --

the query the database receives becomes:

SELECT * FROM users WHERE email = '' OR '1'='1' --' AND password_hash = '...'

The quote closes the string early, OR '1'='1' is always true, and -- turns the rest into a comment. The attacker is logged in without a password. The same trick can read other tables (with UNION SELECT), change data, or delete it.

See it for yourself

-- What the application intended: find one customer by email
SELECT name, email FROM customers
WHERE email = 'nobody@example.com';

-- The same query after an attacker supplies:  x' OR '1'='1
SELECT name, email FROM customers
WHERE email = 'x' OR '1'='1';

Instead of nothing, the attacker sees every customer.

The fix: parameterised queries

# Python (sqlite3): the ? is a placeholder
cursor.execute(
    "SELECT name, email FROM customers WHERE email = ?",
    (email,),
)

// Node.js (node-postgres): $1 is a placeholder
const result = await client.query(
  "SELECT name, email FROM customers WHERE email = $1",
  [email]
);

With placeholders, the SQL text and the data travel to the database separately. The database parses the query first, then treats the parameter purely as a value — whatever it contains, it can never become SQL code. Every database library supports this (?, $1, :name or @name depending on the driver), and ORMs use it automatically.

Rules that prevent injection

• Always use parameters for values. Never build SQL with string concatenation, f-strings or template literals containing user input.
• Parameters can't be identifiers. Table and column names can't be placeholders. If users choose a sort column, map their choice to a fixed list (an allow-list) in code.
• Escaping by hand is not enough. It's easy to miss a case; parameterisation is the reliable fix.
• ORMs help but aren't magic. Their "raw SQL" functions are just as vulnerable if you concatenate input.

Least privilege

CREATE ROLE shop_app LOGIN PASSWORD '…';
GRANT SELECT, INSERT, UPDATE ON customers, orders, order_items TO shop_app;
GRANT SELECT ON products TO shop_app;

CREATE ROLE analyst LOGIN PASSWORD '…';
GRANT SELECT ON order_summary TO analyst;   -- a view without personal data

REVOKE DELETE ON customers FROM shop_app;

Each application or person should connect with an account that can do only what it needs. The web app shouldn't be able to drop tables; analysts can read a view without seeing emails. If an attacker does get in, least privilege limits the damage. (SQLite has no users — it relies on file permissions.)

Protecting sensitive data

• Passwords: never store them, even encrypted. Store a slow, salted hash (bcrypt, scrypt, Argon2) computed by the application.
• Encryption: use TLS for connections to the database, and encryption at rest for disks and backups.
• Personal data: collect only what you need, restrict access with views and permissions, and follow laws such as the GDPR.
• Backups: protect them as carefully as the live database — and test restoring them.
• Errors: don't show raw database error messages to users; they reveal table and column names.

Try it yourself

Run the intended query, then replace the email value with an injected one like x' OR '1'='1 and see what it exposes. In application code, a parameter would have prevented this.