79
How do you handle security and prevent SQL injection in MySQL?
Level: Expert — Security is critical; SQL injection remains one of the most common and dangerous vulnerabilities.
SQL Injection (SQLi) is an attack where a malicious user inserts (injects) harmful SQL code through application input fields, tricking the database into running unintended commands — potentially exposing, altering, or deleting data.
The root cause: SQL injection happens when user input is concatenated directly into a SQL query. The #1 defense is to never build queries with string concatenation — always use parameterized queries / prepared statements.
How an Injection Works
Vulnerable Code
- Input:
' OR '1'='1
- Query becomes always true
- Attacker bypasses login
Safe Code
- Input treated as data, not SQL
- Placeholders (?) bind values
- Injection impossible
Security Best Practices
- Use prepared statements: Parameterized queries separate code from data (the strongest defense).
- Validate & sanitize input: Enforce type, length, and format on all user input.
- Least privilege: Give accounts only the permissions they need (no root for apps).
- Use stored procedures: Encapsulate logic and limit direct table access.
- Encrypt sensitive data: Hash passwords (bcrypt), use TLS/SSL for connections.
- Hide error details: Don't expose raw SQL errors to users.
- Patch regularly: Keep MySQL updated against known vulnerabilities.
Quick Example — Prepared Statements
-- VULNERABLE (never do this — string concatenation)
-- "SELECT * FROM users WHERE email = '" + userInput + "'";
-- Input: ' OR '1'='1 -> returns ALL users!
-- SAFE: MySQL prepared statement with a placeholder
PREPARE stmt FROM
'SELECT * FROM users WHERE email = ? AND status = ?';
SET @email = 'rumman@example.com';
SET @status = 'Active';
EXECUTE stmt USING @email, @status; -- input is bound as data, not SQL
DEALLOCATE PREPARE stmt;
Quick Example — Least Privilege & Encryption
-- Create an app user with ONLY the needed privileges
CREATE USER 'app_user'@'%' IDENTIFIED BY 'Str0ng!Pass';
GRANT SELECT, INSERT, UPDATE ON shop.orders TO 'app_user'@'%';
-- (No DROP, no GRANT, no access to other databases)
-- Store password hashes, never plain text
INSERT INTO users (email, password_hash)
VALUES ('a@x.com', SHA2('userPasswordHere', 256));
-- Require encrypted (TLS) connections for a user
ALTER USER 'app_user'@'%' REQUIRE SSL;
Interviewer tip: The one-liner they want — "Prevent SQL injection by using parameterized/prepared statements so input is treated as data, not code. Combine this with input validation, least-privilege accounts, stored procedures, encryption, and hidden error messages."