Database Security Fundamentals

Database security covers the practices that protect stored data from unauthorized access, tampering, and leakage. Because databases typically hold an organization's most sensitive information — customer records, payment details, credentials — security failures here tend to be far more damaging than in almost any other part of an application.

Three practical areas cover most of what a working developer needs to understand: preventing SQL injection, applying the principle of least privilege to database accounts, and encrypting sensitive data at the column level.

Why it matters

The database holds what attackers want most: customer records, passwords, payment details. SQL injection has caused some of the largest data breaches on record, and it is entirely preventable. An application account that can do only what the application needs limits the damage when something else goes wrong.

SQL Injection

SQL injection happens when untrusted input (typically from a user) is concatenated directly into a SQL query string, allowing an attacker to change the meaning of the query by injecting their own SQL. The fix is to always use parameterized queries (also called prepared statements), where the query structure and the data values are sent to the database separately — the database never interprets a parameter's contents as SQL syntax, no matter what it contains.

A Vulnerable Query vs. a Parameterized Query

The difference is not stylistic — it is the entire security boundary between "user input" and "executable SQL."

Least-Privilege Database Users

The principle of least privilege means every database account should have only the permissions it actually needs to do its job, and nothing more. An application's everyday database user should typically be able to SELECT, INSERT, UPDATE, and DELETE only in the specific tables it uses — it should not have permission to DROP tables, alter schema, or access unrelated tables. This limits the damage if that account's credentials are ever compromised (for example, through a SQL injection vulnerability or a leaked connection string): the attacker is confined to whatever that narrow account can do, rather than gaining full control of the database.

Encrypting Sensitive Columns

Not all data needs the same protection, but fields like national ID numbers, payment card details, or health records often warrant encryption at the column level, in addition to standard access controls. Column-level (or "field-level") encryption means the value is stored in encrypted form in the database, and only application code holding the correct decryption key can turn it back into readable data — so even someone who gains direct read access to the database (through a backup leak, an over-permissioned account, or a misconfigured export) sees ciphertext instead of the raw sensitive value. This is a defense-in-depth measure: it doesn't replace access control, encryption in transit, or least privilege, but adds a further layer specifically for the most sensitive fields.

Examples

-- VULNERABLE: user input concatenated directly into the query string
-- If username is: admin' --
-- the query becomes: SELECT * FROM users WHERE username = 'admin' --' AND password = '...'
-- The "--" comments out the password check entirely, logging the attacker in as admin.
String query = "SELECT * FROM users WHERE username = '" + username + "' AND password = '" + password + "'";

-- SAFE: parameterized query — the database treats username/password strictly as data
SELECT * FROM users WHERE username = ? AND password = ?;
-- Executed with parameters bound separately, e.g. via a prepared statement:
-- stmt.setString(1, username); stmt.setString(2, password);

Vulnerable version: attacker logs in as admin without knowing the password.
Parameterized version: the literal string "admin' --" is compared as a whole
username value, matches no row, and login correctly fails.

-- Instead of using a superuser/admin account in the application, create a scoped one:
CREATE USER app_user WITH PASSWORD 'a-strong-generated-secret';

GRANT SELECT, INSERT, UPDATE, DELETE ON orders, order_items, customers TO app_user;
-- Deliberately no GRANT on other tables, and no DROP/ALTER/CREATE privileges at all.

REVOKE ALL ON payments_ledger FROM app_user;

CREATE ROLE
GRANT
REVOKE
(app_user can now read/write only the tables the application needs, and cannot
touch schema or the restricted payments_ledger table even if compromised.)

Exercise

This exercise uses PostgreSQL roles, so it cannot run in the playground. Create a login role app_reader for a reporting tool that may read the customers and orders tables and nothing else. It must not be able to insert, update or delete. Then show the parameterised form of the query that looks up a customer by email, as an application would send it.

Expected result: connected as app_reader, a SELECT on customers succeeds and a DELETE is refused with a permission error.

-- TODO: create the role app_reader with a password, able to log in

-- TODO: grant it read access to customers and orders only

-- TODO: the lookup by email, written with a placeholder instead of the value

CREATE ROLE app_reader LOGIN PASSWORD 'use-a-long-random-password';

GRANT SELECT ON customers, orders TO app_reader;

-- Sent by the application as a prepared statement; the email travels separately as data.
SELECT id, name FROM customers WHERE email = $1;

Quick quiz

Interview questions