SQL Injection and Database Security

Protect databases from SQL injection with parameterized queries, give each user only the access they need with GRANT and roles, and follow everyday security habits.

Lesson 25 of 102Advanced18 min read

A database often holds the most valuable information a business has: customer details, orders, payments, passwords. Attackers know it. One of the oldest and still most common attacks on websites, SQL injection, tricks an application into running SQL that the attacker wrote, and it has led to some of the largest data breaches ever reported.

The good news is that SQL injection is completely preventable with one habit: never build SQL by gluing user input into a string. Use parameterized queries instead, and the database treats input strictly as data, never as code.

In this lesson you'll see exactly how an injection attack works, fix it in three popular languages, then protect the database itself by giving each user and application only the permissions they need with GRANT, REVOKE and roles. You'll finish with everyday habits that keep data safe.

How SQL injection works

Imagine a login form whose code builds a query like this (in Python):

Python
query = "SELECT * FROM users WHERE email = '" + email + "' AND password_hash = '" + pw_hash + "'"

A normal visitor types ana@example.com. An attacker types ' OR '1'='1' -- as the email. The query becomes:

SQL
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, so the password check disappears. The attacker is logged in as the first user, often an administrator. Other inputs can read whole tables or delete them.

Parameterized queries

A parameterized query (or prepared statement) sends the SQL and the values separately. Placeholders mark where values go, and the database never interprets them as SQL:

LanguageExample
Python (sqlite3)cur.execute("SELECT * FROM users WHERE email = ?", (email,))
PHP (PDO)$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?"); $stmt->execute([$email]);
Node.js (pg)client.query("SELECT * FROM users WHERE email = $1", [email])

Now ' OR '1'='1' -- is just an odd-looking email that matches nobody. Placeholders work for values only; table and column names can't be parameters, so if those must vary, choose them from a fixed list in your code. The PHP PDO lesson shows this in a full application.

Users, GRANT and REVOKE

Database servers such as PostgreSQL and MySQL have their own users, each with privileges: which statements they may run on which tables.

SQL
CREATE USER report_app WITH PASSWORD 'use-a-long-random-secret';   -- PostgreSQL
GRANT SELECT ON orders, products TO report_app;
REVOKE SELECT ON products FROM report_app;

Common privileges are SELECT, INSERT, UPDATE, DELETE, plus CREATE and DROP for changing structure. Follow the principle of least privilege: a reporting tool only needs SELECT; a website needs SELECT, INSERT and UPDATE on its own tables, never DROP. If the website is ever compromised, the damage is limited to what its user could do.

Roles and restricted views

A role is a named bundle of privileges. Grant privileges to the role once, then grant the role to people:

SQL
CREATE ROLE analyst;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO analyst;   -- PostgreSQL
GRANT analyst TO priya;

To share some columns but not others, create a view with only the safe columns, such as customer city but not email, and grant SELECT on the view instead of the table. SQLite is a single file with no users; its security comes from file permissions and the application in front of it.

SQL Security Syntax

Security statements

-- parameterized (placeholder styles vary)
SELECT * FROM users WHERE email = ?;     -- SQLite, MySQL, PDO
SELECT * FROM users WHERE email = $1;    -- PostgreSQL drivers

CREATE USER name WITH PASSWORD '...';    -- PostgreSQL
GRANT SELECT, INSERT ON table TO name;
REVOKE INSERT ON table FROM name;
CREATE ROLE role_name;
GRANT role_name TO name;

SQL Security Summary

  • SQL injection happens when user input is glued into SQL text and changes the query's meaning.
  • Parameterized queries send values separately with placeholders, which prevents injection.
  • Database users get privileges with GRANT and lose them with REVOKE.
  • Follow least privilege: each application and person gets only what it needs.
  • Roles group privileges; views can expose only safe columns; passwords are stored as hashes.

SQL Security Examples

Each example shows the code, the output it produces and what each part does.

Example 1

The attack in action

Searching customers by name, with the input x' OR '1'='1 pasted straight into the SQL text:

SQL
SELECT name, city
FROM customers
WHERE name = 'x' OR '1'='1';
Output
name  | city
------+-------
Ana   | Lisbon
Ben   | Leeds
Chloe | Lyon
Dev   | Leeds
Ema   | Porto
Line-by-line explanation
  1. Line 3The visitor's quote ended the string early, and OR '1'='1' is true for every row. Instead of one customer or none, the whole table comes back.
Example 2

The same search, made safe

Python
import sqlite3

conn = sqlite3.connect("shop.db")
search = "x' OR '1'='1"

rows = conn.execute(
    "SELECT name, city FROM customers WHERE name = ?",
    (search,),
).fetchall()
print(rows)

rows = conn.execute(
    "SELECT name, city FROM customers WHERE name = ?",
    ("Dev",),
).fetchall()
print(rows)
Output
[]
[('Dev', 'Leeds')]
Line-by-line explanation
  1. Line 4The same malicious input as before.
  2. Lines 7 to 8The ? placeholder and the separate tuple of values. The database compares names with the whole string, quotes included.
  3. Line 10No customer is called x' OR '1'='1, so nothing is returned.
  4. Lines 12 to 15Normal searches work exactly as before.
Example 3

Least privilege for two applications

SQL
-- PostgreSQL
CREATE ROLE shop_web LOGIN PASSWORD 'long-random-secret-1';
GRANT SELECT ON products TO shop_web;
GRANT SELECT, INSERT ON orders TO shop_web;

CREATE ROLE reports LOGIN PASSWORD 'long-random-secret-2';
GRANT SELECT ON orders, products TO reports;

-- later, logged in as reports:
DELETE FROM orders WHERE id = 1;
Output
CREATE ROLE
GRANT
GRANT
CREATE ROLE
GRANT
ERROR:  permission denied for table orders
Line-by-line explanation
  1. Lines 2 to 4The website can read products and create orders, but can't change prices or delete anything.
  2. Lines 6 to 7The reporting tool can only read.
  3. Line 10Even if the reports password leaked, nobody could delete orders with it.

Common SQL Security Mistakes

Building SQL with string formatting

Don't
cur.execute(f"SELECT * FROM users WHERE email = '{email}'")
Do
cur.execute("SELECT * FROM users WHERE email = ?", (email,))

Escaping quotes by hand

Don't
safe = email.replace("'", "''")
cur.execute("SELECT * FROM users WHERE email = '" + safe + "'")
Do
cur.execute("SELECT * FROM users WHERE email = ?", (email,))

Hand-made escaping misses edge cases. Placeholders handle every one of them.

The application connecting as an administrator

Don't
DATABASE_URL=postgres://postgres:admin@db/shop     # superuser, can DROP anything
Do
DATABASE_URL=postgres://shop_web:<secret>@db/shop  # only the privileges it needs

SQL Security in Real Projects

Security in practice

Security standards such as the OWASP Top 10 have listed injection among the most serious web risks for many years. Professional teams use parameterized queries or an ORM (a library that writes SQL for you, safely), run applications with limited database users, keep passwords and connection details out of code, encrypt backups, and review who has access regularly. Attackers try injection automatically against every site they find, so even small projects need these habits.

SQL Security FAQ

Do prepared statements stop every kind of SQL injection?

They stop injection through values, which is the most common kind. They can't bind table names, column names or sort directions, so choose those from a fixed allowlist in your code. Dynamic SQL built inside stored procedures needs the same care.

Do stored procedures prevent SQL injection?

Only if they don't build SQL strings from their inputs. A procedure that uses its parameters directly in queries is safe; one that concatenates them into dynamic SQL and runs it with EXECUTE is just as vulnerable as application code. Use parameters inside procedures too.

How should passwords be stored in a database?

Never in plain text and never with fast hashes such as MD5 or SHA-1. Hash them in the application with a slow password hashing algorithm such as bcrypt or Argon2id, which adds a random salt, and store only the result. To check a login, hash the attempt the same way and compare.

What does least privilege mean for databases?

Give each user or application only the permissions it needs. A reporting tool gets SELECT on a few views; a web application gets SELECT, INSERT, UPDATE and DELETE on its own tables but can't drop tables or create users. If one account is compromised, the damage stays limited.

Should database data be encrypted?

Encrypt connections with TLS so data can't be read on the network, and use encryption at rest, offered by most hosting providers, for disks and backups. Very sensitive fields, such as national ID numbers, may also need column-level encryption in the application, with keys stored separately from the database.

SQL Security Exercises

Answer each one and check it instantly. If you get stuck, open the hint before the answer.

Fill in the blankseasy

Complete the parameterized query in Python's sqlite3.

cur.execute("SELECT * FROM users WHERE email = ", (email,))
Multiple choiceeasy

What causes SQL injection?

Predict the outputmedium

With a parameterized query, a visitor searches for the name ' OR '1'='1. How many customers are returned?

Multiple choicemedium

Which statement lets the user reports read the orders table?

Multiple choicehard

What does the principle of least privilege mean?

SQL Security Quiz

5 questions to check what you learned.

  1. Which is the best defence against SQL injection?

    • Hiding error messages
    • Shorter table names
    • Using UPPER CASE keywords
    • Parameterized queries

  2. What does -- do in the injected input ' OR '1'='1' --?

    • Subtracts a number
    • Starts a comment, hiding the rest of the original query
    • Ends the database connection
    • Deletes a row

  3. What is a database role?

    • A named group of privileges that can be granted to users
    • A table column
    • A type of index
    • A backup

  4. Can a placeholder be used for a table name?

    • Yes, always
    • Only in SQLite
    • Only with GRANT
    • No, placeholders are for values; pick table names from a fixed list in code

  5. How should user passwords be stored?

    • As plain text in a protected table
    • As salted hashes from a slow algorithm such as bcrypt or Argon2
    • Encrypted with a key stored next to them
    • Reversed and uppercased

Related SQL topics

19ViewsSave a query as a named view, query it like a table, and use views to simplify reports and limit access.24Triggers & ProceduresRun SQL automatically when data changes with triggers, and save reusable routines in the database with stored procedures and functions.22TransactionsGroup changes into all-or-nothing transactions with BEGIN, COMMIT and ROLLBACK, and understand ACID.
Esc
↑↓ to navigateEnter to open