Prevent SQL Injection Attacks

This skill provides expert guidance on SQL injection prevention and database security, covering how injection attacks work, how to identify vulnerable code patterns, and how to implement robust defenses across languages, frameworks, and database systems. Primary defenses, in order of preference: parameterized queries/prepared statements (the gold standard), input validation with whitelist checks and strict data-type enforcement, correctly-implemented stored procedures with parameterized inputs, database-specific escaping as a last resort, and the principle of least privilege for database users. A defense-in-depth strategy combines these layers with a WAF and monitoring, plus both preventive and detective controls and regular security testing.

Use this skill when reviewing or writing database access code for injection vulnerabilities, hardening database user permissions, or setting up detection and testing for SQL injection. It demonstrates vulnerable-vs-secure code side by side across Java/JDBC (string concatenation vs. PreparedStatement with ? placeholders), Python/SQLAlchemy (f-string interpolation vs. text() with named parameters, or the ORM query-filter approach), PHP/PDO (concatenation vs. bindParam with typed placeholders), and Node.js/MySQL2 (template literals vs. parameterized ? queries with an args array).

Input validation combines format checks (e.g. numeric-only user IDs, a regex-validated email pattern) with whitelist validation against an allowed set of values (e.g. restricting a role field to user/admin/moderator). When parameterized queries aren't possible, database-specific escaping functions like mysqli_real_escape_string (MySQL) or pg_escape_string (PostgreSQL) are the fallback. For dynamic query construction - such as a user-selectable sort column - only whitelist-validated identifiers (checked against an allowed-columns set) should ever be concatenated into SQL, with all values still passed as parameters. SQL Server stored procedures accept typed parameters (@UserID INT, @Status NVARCHAR(20), @StartDate DATE) rather than interpolated strings.

Database-level hardening includes creating limited-privilege application users (e.g. GRANT SELECT, INSERT, UPDATE scoped to specific tables rather than broad access) and disabling dangerous MySQL settings like log_bin_trust_function_creators and local_infile, or enabling PostgreSQL Row Level Security policies scoped to the current application user. Detection includes a regex-based pattern matcher flagging common SQL metacharacters, UNION-based attacks, SQL keyword sequences, stored-procedure execution attempts, and script-injection patterns, logging any match. A ModSecurity WAF rule example uses the OWASP Core Rule Set's @detectSQLi operator to block and log matching requests with a severity score. For testing your own applications, sqlmap commands are shown against a login form (with cookie/session context, --level=3 --risk=2 --batch --report) and against GET parameters (--level=3 --risk=2 --batch --dump-all). A documented incident-response sequence for a discovered injection: immediately block malicious IPs and disable affected endpoints, patch and deploy fixes short-term, investigate logs and assess breach scope, invest long-term in comprehensive testing and security training, and report to relevant authorities if compliance requires it.

Developers and security engineers hardening database access code against SQL injection across multiple languages and databases, who need concrete secure-vs-vulnerable code patterns, database-level privilege hardening, detection rules, and a tested incident-response procedure - always validated through penetration testing and code review against their own applications.