SQL Security: User Management, SQL Injection & Encryption

In This Chapter

User Management & Permissions
SQL Injection Prevention
Encryption & Decryption

1 User Management & Permissions

The principle of least privilege: every user should have only the permissions they need and nothing more. A web app shouldn't connect with the root account. An intern shouldn't have DELETE access to production tables.

Creating Users

MySQL

-- Create a user for the web application
CREATE USER 'webapp'@'%'
IDENTIFIED BY 'Str0ng_P@ssw0rd!';

-- Create a read-only analytics user
CREATE USER 'analyst'@'10.0.0.%'
IDENTIFIED BY 'An@lytics2025!';

Granting Permissions

MySQL

-- Web app: read/write data, but can't change structure
GRANT SELECT, INSERT, UPDATE, DELETE ON mydb.* TO 'webapp'@'%';

-- Analyst: read-only access
GRANT SELECT ON mydb.* TO 'analyst'@'10.0.0.%';

-- View specific columns only (use a view for column-level control)
CREATE VIEW safe_customers AS
SELECT id, name, city FROM customers; -- no email, no phone
GRANT SELECT ON mydb.safe_customers TO 'intern'@'%';

-- Remove permissions
REVOKE DELETE ON mydb.* FROM 'webapp'@'%';

-- Apply changes
FLUSH PRIVILEGES;

Permission	Allows	Give To
`SELECT`	Read data	Almost everyone
`INSERT`	Add rows	App users, data entry
`UPDATE`	Modify rows	App users (careful)
`DELETE`	Remove rows	Admins only (usually)
`CREATE, ALTER, DROP`	Change schema	DBAs only
`ALL PRIVILEGES`	Everything	Never in production

Key Takeaways

Create separate users for each application/role — never share the root account
GRANT only the minimum permissions needed (least privilege)
Use views for column-level access control
REVOKE permissions when roles change; audit regularly

2 SQL Injection Prevention

Top

SQL injection is the #1 web application vulnerability. It happens when user input is inserted directly into a SQL query string, allowing attackers to modify the query's logic.

How SQL Injection Works

Vulnerable PHP Code

// NEVER DO THIS!
$query = "SELECT * FROM users WHERE email = '" . $_POST['email'] . "'";

// If attacker enters:  ' OR '1'='1
// The query becomes:
SELECT * FROM users WHERE email = '' OR '1'='1'
// This returns ALL users! The attacker just bypassed login.

// Even worse:  '; DROP TABLE users; --
SELECT * FROM users WHERE email = ''; DROP TABLE users; --'
// Your entire users table is deleted.

The Fix: Prepared Statements (Parameterized Queries)

PHP (PDO)

// SAFE: Parameterized query
$stmt = $pdo->prepare("SELECT * FROM users WHERE email = ?");
$stmt->execute([$_POST['email']]);

// The input is NEVER inserted into the SQL string.
// Even if the user types: ' OR '1'='1
// The database treats it as a literal string, not SQL code.

MySQL Prepared Statement

PREPARE stmt FROM 'SELECT * FROM users WHERE email = ?';
SET @email = 'user@example.com';
EXECUTE stmt USING @email;
DEALLOCATE PREPARE stmt;

Defense Layers

Layer	Protection
Prepared statements	Separates SQL code from data — the #1 defense
Input validation	Reject unexpected characters, enforce types/lengths
Least privilege	App user can't DROP tables even if injected
WAF (Web Application Firewall)	Blocks common injection patterns at the network level
Error handling	Don't expose database errors to users — they reveal structure

The only reliable fix is prepared statements. Input validation and escaping are useful layers, but they're not sufficient alone. Always use parameterized queries. Every modern language and framework supports them.

Key Takeaways

SQL injection lets attackers modify your queries by injecting code through user input
Always use prepared statements — never concatenate user input into SQL
Defense in depth: prepared statements + input validation + least privilege + error handling
Never expose database error messages to end users

3 Encryption & Decryption

Top

Encryption protects sensitive data (passwords, credit cards, medical records) so that even if the database is compromised, the data is unreadable without the key.

Hashing Passwords (One-Way)

Passwords should never be stored in plain text or with reversible encryption. Use a one-way hash:

MySQL

-- Store a hashed password
INSERT INTO users (email, password_hash)
VALUES ('sara@email.com', SHA2('my_secret_password', 256));

-- Verify a login (compare hashes)
SELECT * FROM users
WHERE email = 'sara@email.com'
  AND password_hash = SHA2('my_secret_password', 256);

In production, use bcrypt or argon2 in your application code (PHP's password_hash(), Python's bcrypt library). MySQL's SHA2() works but lacks salt and is too fast — making it vulnerable to brute-force attacks. Database-level hashing is a fallback, not the best practice.

Encrypting Data (Two-Way)

For data you need to read back (credit cards, SSNs), use AES encryption:

MySQL

-- Encrypt sensitive data
INSERT INTO payments (customer_id, card_encrypted)
VALUES (1, AES_ENCRYPT('4111-1111-1111-1111', 'encryption_key_here'));

-- Decrypt when needed
SELECT customer_id,
       AES_DECRYPT(card_encrypted, 'encryption_key_here') AS card_number
FROM payments;

Encryption at Rest vs In Transit

Type	Protects Against	How
At rest	Stolen hard drives, database file theft	TDE (Transparent Data Encryption), encrypted columns
In transit	Network eavesdropping	SSL/TLS connection between app and database
Column-level	Unauthorized queries on sensitive fields	AES_ENCRYPT on specific columns

Key Takeaways

Hash passwords with bcrypt/argon2 in your app — never store plain text
Use AES encryption for data you need to decrypt (credit cards, SSNs)
Protect data at rest (TDE), in transit (SSL/TLS), and at column level
Store encryption keys separately from the database — never in the same system

What's next? Performance Tuning — query optimization techniques, execution plans, and profiling.

All Chapters in SQL Advanced Course

Previous ChapterLocks & Deadlocks Next Chapter Performance Tuning

SQL Security

In This Chapter

1 User Management & Permissions

Creating Users

Granting Permissions

Key Takeaways

2 SQL Injection Prevention

How SQL Injection Works

The Fix: Prepared Statements (Parameterized Queries)

Defense Layers

Key Takeaways

3 Encryption & Decryption

Hashing Passwords (One-Way)

Encrypting Data (Two-Way)

Encryption at Rest vs In Transit

Key Takeaways

All Chapters in SQL Advanced Course

Courses

Services

Support

Connect