Skip to content
Tutorials Center
My learning

Topics

Lesson 72 of 79 ยท Security and Administration

Injection, Encryption and Data Protection

See SQL injection work against string-built queries and fail against parameters, then learn encryption at rest and in transit, password hashing, masking and GDPR basics.

Access control decides who may use the database. This lesson is about what happens when that line is crossed or bypassed: an attacker who tricks your application into running their SQL, a stolen disk or backup, a sniffed network connection, a leaked password table. Each threat has a standard defence, and a secure system layers several of them, because any single one can fail.

You will run the most famous attack, SQL injection, against a small login function and watch the fix stop it. Then you will see the main kinds of encryption, why passwords are hashed and not encrypted, how to mask and erase personal data, and what privacy laws such as the GDPR ask of database designers. The SQL-tutorial versions of two topics are SQL Injection and Encryption. Before this lesson, read Authentication, Authorization and Access Control.

Where data can leak

Injection Attacker input becomes SQL.
Stolen storage Disks, backups, laptops.
Network Traffic read in transit.
Leaked passwords A dump of the users table.
1 / 2

SQL injection

SQL injection happens when an application builds a SQL statement by pasting user input into a string. The database cannot tell which part came from the programmer and which from the user, so a clever input can change the structure of the query. It has been among the most damaging web vulnerabilities for decades, and the cause is always the same mistake.

Below is a login check written the dangerous way, next to the same check written with parameters. This runs live in Python's sqlite3 module (Pyodide on this site). The attacker types a name that closes the quote and adds a condition that is always true, followed by --, which makes the rest of the line a comment.

Python Injection: string-built query versus parameters

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Read the printed statement for the attack: the condition became name = '' OR '1'='1' and the password test vanished into the comment, so the query returns every user and the attacker is "logged in". With placeholders (?) the statement text is fixed first and the values travel separately, so the attacker's whole string is just a name that nobody has, and the query returns an empty list.

Real attacks go further than bypassing a login: UNION queries read other tables, stacked statements drop tables (in drivers that allow several statements per call), and error messages reveal the schema.

Defences

  1. Parameterized queries (prepared statements). Always. Placeholders keep data from becoming code. Every language's database library supports them, and so do ORMs and query builders when used normally.
  2. Never build SQL by concatenating or formatting strings with input, even "safe-looking" input such as numbers from a form.
  3. Identifiers cannot be parameters. If a user chooses a sort column, map the choice to a fixed list of allowed names in your code (an allow-list), do not paste it in.
  4. Least privilege. The application account should not be able to drop tables or read tables it does not need, so a successful injection stays small.
  5. Validate input (type, length, format) as defence in depth, but never as the only defence. Escaping functions are fragile compared with parameters.
  6. Hide error details from users, and log them privately.

Here is the allow-list rule for a dynamic ORDER BY, which placeholders cannot handle:

Python Allow-list for a sort column

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

The attacker's value is never placed in the SQL. Only a string from the program's own dictionary reaches the query.

Encryption

Encryption turns readable data into ciphertext that only a holder of the key can reverse. Databases use it in three places.

In transit. The connection between application and database is encrypted with TLS, so that anyone on the network path sees only noise. Servers can require it, and clients should verify the server's certificate, otherwise an attacker can sit in the middle. Replication links and backups in transit need the same treatment.

At rest. Data on disk, in backups and in snapshots is encrypted, so a stolen drive, a discarded server or a copied backup file is useless without the key. Options are full-disk encryption of the operating system, transparent data encryption (TDE) built into the engine (available in SQL Server, Oracle, MySQL Enterprise and others, with encrypted storage options in cloud databases), and encrypted SQLite files via extensions such as SQLCipher.

Application-level (column or field) encryption. The application encrypts a value, say a national id, before it is sent, and the database only ever sees ciphertext. This protects even against a compromised database administrator, but at a price: the database can no longer search, sort or index that value in a useful way, except for exact matches if you also store a keyed hash.

TEXT
# Read-only: typical server settings (PostgreSQL / MySQL, not runnable here)
ssl = on                         # accept TLS connections
ssl_min_protocol_version = TLSv1.2
# MySQL: require_secure_transport = ON
# Client: sslmode=verify-full    # check the certificate and host name

Key management is the hard part. Keys must not be stored next to the data they protect, nor in source code. Use a key management service or hardware security module, rotate keys on a schedule, and restrict who can use them. Encryption with a leaked key is no protection at all.

Passwords: hash, do not encrypt

If the database stores passwords at all, it should store only a hash of each one. A hash function turns input into a fixed-size fingerprint and cannot be reversed. At login, you hash the entered password and compare. Encryption is the wrong tool because anyone with the key (or a thief who finds it) gets every password back.

Plain fast hashes such as SHA-256 are still not enough, because attackers can test billions of guesses per second, and identical passwords would show identical hashes. Two ingredients fix that:

  • A unique random salt per password, stored next to the hash, so equal passwords get different hashes and precomputed tables become useless.
  • A deliberately slow password-hashing function (Argon2id, scrypt or bcrypt) so each guess is expensive.

You should always use your language's password API (for example password_hash in PHP or the argon2 and bcrypt libraries) and not write your own. The demo below uses plain SHA-256 with explicit salts only to make the salt idea visible, because the in-browser Python has no slow hashing function. Do not copy it into production.

Python Salts make equal passwords look different

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

With different salts the two stored hashes differ even though the passwords are identical, and a check works only with the right salt and the right password. In the table you would store both the salt and the hash (modern libraries pack them into one string).

Masking, minimization and erasure

Not everyone who needs data needs all of it. Data masking hides sensitive parts: a support agent sees ****1234, not the whole card number. Pseudonymization replaces direct identifiers with codes, so analysts can study behaviour without learning who anyone is. Data minimization means not collecting what you do not need in the first place; the safest data is data you never stored. Test and development databases should never contain raw production personal data.

SQL Masking, pseudonymization and erasure

Press Run (or Ctrl + Enter) to see the result here.

A caution on pseudonyms: if user-1 is derived by a rule that someone could reverse, or if a few attributes together (city, birth date, job) single out one person, the data can often be re-identified. Pseudonymized data is still personal data under privacy law.

GDPR basics for database designers

The EU's General Data Protection Regulation (and similar laws elsewhere) applies to personal data about people in the EU, wherever the company is. This is not legal advice, but the rules shape database work in recognizable ways:

  • Lawful basis and purpose limitation. You record why you hold data and use it only for that purpose.
  • Data minimization and storage limitation. Keep only what you need, only as long as you need it. This means retention periods and scheduled deletion jobs.
  • Rights of the person. People may ask for access to their data, correction, portability (an export) and erasure ("right to be forgotten"). Your design must be able to find every copy of a person's data, including logs, search indexes, caches and backups (backups are usually expired on a schedule rather than edited).
  • Security by design. Appropriate technical measures such as the encryption, access control and masking in this chapter.
  • Breach notification. A leak of personal data must be reported to the authority within a short deadline (72 hours in the GDPR), which needs logging and monitoring to even notice it.

Designs that help: keep personal data in few, well-known tables; reference people by id elsewhere, so erasure touches one place; and keep audit logs free of unnecessary personal details.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. Why does the injected input ' OR '1'='1' -- bypass a string-built login query?

    The input closes the string literal and adds its own SQL. Because the database sees one text, it cannot tell programmer code from user data.
  2. 2. What is the most reliable defence against SQL injection?

    With placeholders the statement is fixed before the values are bound, so input can never become SQL.
  3. 3. How should a column name chosen by the user (for ORDER BY) be handled?

    Placeholders work for values, not identifiers. An allow-list ensures only known column names reach the SQL.
  4. 4. Why are passwords stored as salted slow hashes instead of encrypted text?

    With encryption a stolen key reveals every password. Salted, slow hashes (Argon2id, scrypt, bcrypt) resist both reversal and mass guessing.
  5. 5. What does encryption at rest protect against?

    At-rest encryption protects stored files. Network traffic needs TLS, and a user with valid credentials is the job of access control.

Summary

  • SQL injection occurs when user input is pasted into SQL text. Use parameterized queries every time; allow-list identifiers such as sort columns.
  • Layer defences: least privilege, input validation, hidden error details.
  • Encrypt in transit (TLS with certificate checks), at rest (disk, TDE, encrypted files) and, for the most sensitive fields, in the application. Key management decides whether encryption works.
  • Store passwords as unique-salted, slow hashes (Argon2id, scrypt, bcrypt) made by a library, never as plain or encrypted text.
  • Mask, pseudonymize and minimize personal data; remember that pseudonymized data can often be re-identified.
  • GDPR-style rules need retention limits, the ability to find and erase one person's data, security by design and breach detection.

Next: Project: Library Management System.