--- # === IDENTITY === id: software/security/sql-injection-prevention/2026 canonical_question: "How do I prevent SQL injection across languages and frameworks?" aliases: - "SQL injection prevention best practices" - "parameterized queries vs string concatenation" - "how to use prepared statements" - "SQL injection prevention Python Java Node.js Go" - "OWASP SQL injection cheat sheet" - "CWE-89 remediation" - "prevent SQL injection in ORM" - "SQL injection testing with sqlmap" entity_type: software_reference domain: software > security > sql_injection_prevention region: global jurisdiction: global temporal_scope: 2020-2026 # === VERIFICATION === last_verified: 2026-02-24 confidence: 0.95 version: 1.0 first_published: 2026-02-24 # === TEMPORAL VALIDITY === temporal_validity: status: stable last_breaking_change: null next_review: 2026-08-23 change_sensitivity: low # === CONSTRAINTS === constraints: - "NEVER concatenate or interpolate user input directly into SQL strings — this is the root cause of all SQL injection vulnerabilities" - "Dynamic table names, column names, and ORDER BY clauses cannot be parameterized — use strict server-side allowlists instead" - "Stored procedures are NOT automatically safe — they must use parameterized queries internally, never EXEC with concatenation" - "ORMs reduce but do not eliminate SQL injection risk — raw query methods in every ORM bypass parameterization" - "Input validation and WAFs are defense-in-depth layers only — they must never be the sole protection" # === SKIP CONDITIONS === skip_this_unit_if: - condition: "User is asking about NoSQL injection (MongoDB, Redis, etc.)" use_instead: "NoSQL injection prevention — different attack vectors and mitigations apply" - condition: "User needs to fix XSS (cross-site scripting) rather than SQL injection" use_instead: "software/security/xss-prevention — output encoding, not input parameterization" - condition: "User is looking for general OWASP Top 10 overview rather than SQL injection specifically" use_instead: "software/security/owasp-top-10 — covers all 10 categories" # === AGENT HINTS === inputs_needed: - key: language question: "Which programming language or framework are you using?" type: choice options: ["Python", "Node.js", "Java", "Go", "PHP", "C#/.NET", "Ruby", "Other"] - key: database question: "Which database system?" type: choice options: ["PostgreSQL", "MySQL", "SQL Server", "SQLite", "Oracle", "Other"] - key: uses_orm question: "Are you using an ORM or raw SQL?" type: choice options: ["ORM (SQLAlchemy, Prisma, Hibernate, etc.)", "Raw SQL / query builder", "Both"] # === DISTRIBUTION === canonical_source: "https://knowledgelib.io/software/security/sql-injection-prevention/2026" suggested_citation: "Source: knowledgelib.io — AI Knowledge Library (verified 2026-02-24)" # === RELATED UNITS === related_kos: depends_on: [] related_to: - id: "software/security/owasp-top-10/2026" label: "OWASP Top 10 Web Application Security Risks" - id: "software/security/xss-prevention/2026" label: "Cross-Site Scripting (XSS) Prevention" - id: "software/patterns/database-indexing-strategies/2026" label: "Database Indexing Strategies" - id: "software/patterns/sql-query-optimization/2026" label: "SQL Query Optimization Patterns" solves: [] alternative_to: [] often_confused_with: - id: "software/security/xss-prevention/2026" label: "XSS Prevention (output encoding, not input parameterization)" # === SOURCES (7 authoritative sources) === sources: - id: src1 title: "SQL Injection Prevention Cheat Sheet" author: OWASP Foundation url: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html type: community_resource published: 2024-12-15 reliability: authoritative - id: src2 title: "SQL Injection — Web Security Academy" author: PortSwigger url: https://portswigger.net/web-security/sql-injection type: technical_blog published: 2025-06-01 reliability: high - id: src3 title: "CWE-89: Improper Neutralization of Special Elements used in an SQL Command" author: MITRE url: https://cwe.mitre.org/data/definitions/89.html type: community_resource published: 2025-01-01 reliability: authoritative - id: src4 title: "Query Parameterization Cheat Sheet" author: OWASP Foundation url: https://cheatsheetseries.owasp.org/cheatsheets/Query_Parameterization_Cheat_Sheet.html type: community_resource published: 2024-12-15 reliability: authoritative - id: src5 title: "Testing for SQL Injection — OWASP Web Security Testing Guide" author: OWASP Foundation url: https://owasp.org/www-project-web-security-testing-guide/latest/4-Web_Application_Security_Testing/07-Input_Validation_Testing/05-Testing_for_SQL_Injection type: community_resource published: 2024-06-01 reliability: authoritative - id: src6 title: "SQL Injection Cheat Sheet" author: PortSwigger url: https://portswigger.net/web-security/sql-injection/cheat-sheet type: technical_blog published: 2025-06-01 reliability: high - id: src7 title: "Practical Identification of SQL Injection Vulnerabilities" author: CISA url: https://www.cisa.gov/sites/default/files/publications/Practical-SQLi-Identification.pdf type: official_docs published: 2024-01-01 reliability: authoritative --- # SQL Injection Prevention Across Languages and Frameworks ## TL;DR - **Bottom line**: ALWAYS use parameterized queries (prepared statements) — they separate SQL structure from data, making injection structurally impossible. - **Key tool/command**: `cursor.execute("SELECT * FROM users WHERE id = ?", (user_id,))` — parameterized query pattern (syntax varies by language). - **Watch out for**: Dynamic table/column names and ORDER BY clauses cannot be parameterized — use strict server-side allowlists for these. - **Works with**: Every major language and database — Python, Node.js, Java, Go, PHP, C#, Ruby; PostgreSQL, MySQL, SQL Server, SQLite, Oracle. ## Constraints - NEVER concatenate or interpolate user input directly into SQL strings — this is the root cause of all SQL injection - Dynamic table names, column names, and ORDER BY clauses cannot be parameterized — use strict server-side allowlists - Stored procedures are NOT automatically safe — internal EXEC with string concatenation is still vulnerable - ORMs reduce but do not eliminate risk — raw query escape hatches (`.raw()`, `@Query`, `execute()`) bypass parameterization - Input validation and WAFs are defense-in-depth only — never the sole protection - Escaping special characters is fragile and database-specific — parameterization is always preferred ## Quick Reference | # | Vulnerability | Risk | Vulnerable Code | Secure Code | |---|---|---|---|---| | 1 | Classic injection (tautology) | Critical | `"SELECT * FROM users WHERE name='" + input + "'"` | `"SELECT * FROM users WHERE name = ?"` with params | | 2 | UNION-based injection | Critical | `"SELECT id FROM items WHERE cat='" + cat + "'"` | Parameterized query + column type validation | | 3 | Blind boolean injection | High | Same as #1; attacker infers data via true/false responses | Parameterized queries + generic error messages | | 4 | Time-based blind injection | High | Same as #1; attacker uses `SLEEP()` / `pg_sleep()` | Parameterized queries + query timeout limits | | 5 | Second-order injection | High | Stored user input later interpolated into SQL | Parameterize ALL queries, including those using stored data | | 6 | Stored procedure injection | High | `EXEC('SELECT * FROM ' + @table)` | Use `sp_executesql` with parameters or allowlist table names | | 7 | LIKE clause injection | Medium | `"WHERE name LIKE '%" + search + "%'"` | `"WHERE name LIKE ?"` with `'%' + escaped_input + '%'` as param | | 8 | ORDER BY injection | Medium | `"ORDER BY " + column` | Server-side allowlist: `if column in ALLOWED_COLS` | | 9 | Integer injection | Medium | `"WHERE id = " + id` (no quotes) | Parameterize even numeric values — always | | 10 | Batch/stacked queries | Critical | `"SELECT ...; DROP TABLE users"` via multi-statement | Disable multi-statement execution; parameterize queries | ## Decision Tree > Full script: [decision-tree.txt](scripts/decision-tree.txt) (32 lines) ``` START |-- What language/framework? | |-- Python? | | |-- Using SQLAlchemy/Django ORM? --> Use ORM query API (avoid .raw()/.text()) | | |-- Using psycopg2/sqlite3? --> Use %s or ? placeholders with params tuple # ... (see full script) ``` ## Step-by-Step Guide ### 1. Identify all SQL query construction points Audit your codebase for any location where SQL strings are built. Search for string concatenation, interpolation, or formatting near SQL keywords. [src1] ```bash # Search for common vulnerable patterns in Python grep -rn "execute.*\".*+\|execute.*f\"" --include="*.py" . # Search in JavaScript/TypeScript grep -rn "query.*\`.*\${\|query.*\".*+" --include="*.js" --include="*.ts" . # Search in Java grep -rn "createQuery.*\".*+\|Statement.*\".*+" --include="*.java" . ``` **Verify**: Every result from these searches should be reviewed and remediated. ### 2. Replace string concatenation with parameterized queries For each vulnerable query found, refactor to use the language-appropriate parameterized query syntax. [src1] [src4] ```python # BEFORE (vulnerable) cursor.execute("SELECT * FROM users WHERE email = '" + email + "'") # AFTER (secure) cursor.execute("SELECT * FROM users WHERE email = %s", (email,)) ``` **Verify**: The refactored query should produce identical results with normal input and reject `' OR 1=1 --` payloads. ### 3. Handle dynamic identifiers with allowlists Table names, column names, and ORDER BY fields cannot be parameterized. Build a strict allowlist. [src2] ```python ALLOWED_SORT_COLUMNS = {'name', 'created_at', 'email', 'id'} def safe_order_by(column, direction='ASC'): if column not in ALLOWED_SORT_COLUMNS: raise ValueError(f"Invalid sort column: {column}") if direction.upper() not in ('ASC', 'DESC'): raise ValueError(f"Invalid sort direction: {direction}") return f"ORDER BY {column} {direction.upper()}" ``` **Verify**: Passing `"name; DROP TABLE users"` as column should raise `ValueError`. ### 4. Secure LIKE clauses Escape wildcard characters in user input before incorporating into LIKE patterns. [src1] ```python import re def safe_like_search(cursor, search_term): # Escape SQL LIKE special characters in user input escaped = re.sub(r'([%_\\])', r'\\\1', search_term) cursor.execute( "SELECT * FROM products WHERE name LIKE %s", (f"%{escaped}%",) ) ``` **Verify**: Input `%admin%` should search for the literal string "%admin%", not match everything. ### 5. Configure defense-in-depth measures Apply the principle of least privilege and enable additional safeguards. [src3] [src7] ```sql -- Create a restricted application database user CREATE USER app_reader WITH PASSWORD 'strong_password'; GRANT SELECT ON users, products, orders TO app_reader; -- Do NOT grant DELETE, DROP, ALTER, or EXECUTE to application accounts -- Enable query logging for audit ALTER SYSTEM SET log_statement = 'all'; ``` **Verify**: Application user should fail on `DROP TABLE` or `ALTER TABLE` attempts with a permissions error. ### 6. Test with automated SQL injection scanners Run sqlmap or OWASP ZAP against your application to verify no injection points remain. [src5] ```bash # Test a specific URL parameter with sqlmap sqlmap -u "http://localhost:8080/api/users?id=1" --batch --level=3 --risk=2 # Test with OWASP ZAP (CLI mode) zap-cli quick-scan -s all http://localhost:8080 ``` **Verify**: Both tools should report zero SQL injection vulnerabilities. Any finding requires immediate remediation. ## Code Examples ### Python / psycopg2: Parameterized Query ```python # Input: user-supplied email string # Output: matching user record or None import psycopg2 conn = psycopg2.connect("dbname=myapp user=app_reader") cur = conn.cursor() # Secure: %s placeholder, params as tuple email = request.args.get('email') cur.execute( "SELECT id, name, email FROM users WHERE email = %s", (email,) # Note trailing comma for single-element tuple ) user = cur.fetchone() cur.close() conn.close() ``` ### Python / SQLAlchemy ORM: Safe Query ```python # Input: user-supplied search term # Output: list of matching users from sqlalchemy import select from models import User, Session session = Session() search = request.args.get('q') # Secure: ORM handles parameterization stmt = select(User).where(User.name.ilike(f"%{search}%")) results = session.execute(stmt).scalars().all() # DANGEROUS: avoid .text() with f-strings # session.execute(text(f"SELECT * FROM users WHERE name = '{search}'")) ``` ### Node.js / pg (node-postgres): Parameterized Query ```javascript // Input: user-supplied id from request // Output: user record object const { Pool } = require('pg'); const pool = new Pool(); async function getUser(userId) { // Secure: $1 numbered placeholder const result = await pool.query( 'SELECT id, name, email FROM users WHERE id = $1', [userId] ); return result.rows[0]; } ``` ### Java / JDBC: PreparedStatement ```java // Input: user-supplied username from HTTP request // Output: matching user or null String username = request.getParameter("username"); String sql = "SELECT id, name, email FROM users WHERE username = ?"; try (PreparedStatement stmt = conn.prepareStatement(sql)) { stmt.setString(1, username); // Secure: typed parameter binding ResultSet rs = stmt.executeQuery(); if (rs.next()) { return new User(rs.getInt("id"), rs.getString("name")); } } // NEVER use: Statement.executeQuery("... '" + username + "'") ``` ### Go / database/sql: Parameterized Query ```go // Input: user-supplied email from query string // Output: user struct or error func getUser(db *sql.DB, email string) (*User, error) { var u User // Secure: $1 placeholder (PostgreSQL) or ? (MySQL) err := db.QueryRow( "SELECT id, name, email FROM users WHERE email = $1", email, ).Scan(&u.ID, &u.Name, &u.Email) if err != nil { return nil, err } return &u, nil } ``` ### PHP / PDO: Prepared Statement ```php // Input: user-supplied product ID // Output: product record array $pdo = new PDO('mysql:host=localhost;dbname=shop', 'app_reader', $pass); $pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); // Use real prepared stmts $productId = $_GET['id']; $stmt = $pdo->prepare('SELECT id, name, price FROM products WHERE id = :id'); $stmt->execute(['id' => $productId]); // Secure: named parameter binding $product = $stmt->fetch(PDO::FETCH_ASSOC); ``` ### C# / ADO.NET: SqlParameter ```csharp // Input: user-supplied search term // Output: list of matching records string search = Request.Query["q"]; string sql = "SELECT Id, Name FROM Users WHERE Name LIKE @search"; using var cmd = new SqlCommand(sql, connection); cmd.Parameters.Add(new SqlParameter("@search", $"%{search}%")); using var reader = await cmd.ExecuteReaderAsync(); while (await reader.ReadAsync()) { results.Add(new User(reader.GetInt32(0), reader.GetString(1))); } ``` ## Anti-Patterns ### Wrong: String concatenation in Python ```python # BAD — direct string concatenation allows injection email = request.args.get('email') cursor.execute("SELECT * FROM users WHERE email = '" + email + "'") # Input: ' OR '1'='1' -- => returns ALL users ``` ### Correct: Parameterized query in Python ```python # GOOD — parameterized query separates structure from data email = request.args.get('email') cursor.execute("SELECT * FROM users WHERE email = %s", (email,)) # Input: ' OR '1'='1' -- => searches for literal string, finds nothing ``` ### Wrong: Stored procedure with EXEC concatenation ```sql -- BAD — dynamic SQL inside stored procedure CREATE PROCEDURE GetUser @name NVARCHAR(100) AS EXEC('SELECT * FROM users WHERE name = ''' + @name + '''') ``` ### Correct: Stored procedure with sp_executesql ```sql -- GOOD — parameterized dynamic SQL CREATE PROCEDURE GetUser @name NVARCHAR(100) AS EXEC sp_executesql N'SELECT * FROM users WHERE name = @n', N'@n NVARCHAR(100)', @n = @name ``` ### Wrong: ORM raw query with interpolation ```javascript // BAD — Prisma $queryRaw with template literal interpolation const name = req.query.name; const users = await prisma.$queryRaw`SELECT * FROM users WHERE name = ${name}`; // Note: Prisma's tagged template IS safe, but this pattern looks identical to: const users2 = await prisma.$queryRawUnsafe( `SELECT * FROM users WHERE name = '${name}'` // VULNERABLE ); ``` ### Correct: ORM query builder or safe raw query ```javascript // GOOD — Use Prisma's query builder const users = await prisma.user.findMany({ where: { name: req.query.name } }); // Or if raw SQL is needed, use parameterized $queryRaw (tagged template) const users2 = await prisma.$queryRaw` SELECT * FROM users WHERE name = ${req.query.name} `; // Tagged template literal — Prisma auto-parameterizes ``` ### Wrong: Relying only on WAF or input validation ```python # BAD — blocklist approach is always bypassable def sanitize(input): blocked = ['SELECT', 'DROP', 'INSERT', '--', ';'] for word in blocked: input = input.replace(word, '') return input # Bypass: "SeLeCt", "SEL/**/ECT", URL encoding, Unicode tricks ``` ### Correct: Parameterize first, validate second ```python # GOOD — parameterization as primary defense, validation as depth import re def validate_email(email): if not re.match(r'^[a-zA-Z0-9._%+-]+@[a-zA-Z0-9.-]+\.[a-zA-Z]{2,}$', email): raise ValueError("Invalid email format") return email email = validate_email(request.args.get('email')) cursor.execute("SELECT * FROM users WHERE email = %s", (email,)) ``` ## Common Pitfalls - **PHP PDO emulated prepares**: By default, PDO uses emulated prepared statements which are vulnerable. Fix: `$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, false)`. [src1] - **ORM raw query escape hatches**: Every ORM has a `.raw()` or `.execute()` method that bypasses parameterization. Fix: Audit all raw query usage; parameterize within raw queries or use query builder instead. [src2] - **Integer values left unparameterized**: Developers skip parameterization for numeric inputs thinking quotes protect them. Fix: Parameterize ALL values regardless of type — `WHERE id = ?` not `WHERE id = ` + id. [src3] - **Second-order injection overlooked**: Data stored safely can be dangerous when retrieved and used in a later query without parameterization. Fix: Treat ALL data as untrusted, even data from your own database. [src6] - **LIKE wildcards not escaped**: User input containing `%` or `_` changes LIKE query behavior. Fix: Escape `%`, `_`, and `\` in user input before adding wildcards. [src1] - **Multi-statement execution enabled**: Some drivers allow semicolons to stack queries. Fix: Disable multi-statement mode — e.g., MySQL `allowMultiQueries: false`. [src4] - **Error messages expose schema**: Detailed SQL error messages help attackers map your database. Fix: Return generic error messages to clients; log details server-side only. [src5] - **Testing only login forms**: SQL injection can occur in any parameter — search, sort, filters, headers, cookies. Fix: Test all input vectors with sqlmap or OWASP ZAP. [src5] ## Diagnostic Commands ```bash # Scan a URL for SQL injection with sqlmap sqlmap -u "http://target.com/page?id=1" --batch --level=3 --risk=2 # Scan with specific parameter and POST data sqlmap -u "http://target.com/login" --data="user=admin&pass=test" --batch # Run OWASP ZAP active scan from CLI zap-cli active-scan http://target.com # Check PostgreSQL for dangerous dynamic SQL in functions psql -c "SELECT proname, prosrc FROM pg_proc WHERE prosrc LIKE '%EXECUTE%' AND prosrc LIKE '%||%';" # Search codebase for vulnerable patterns (Python) grep -rn --include="*.py" "execute.*f\"\|execute.*%.*%" . # Search codebase for vulnerable patterns (JavaScript) grep -rn --include="*.js" "query.*\`.*\${\|query.*+.*req\." . # Check MySQL for stored procedures with dynamic SQL mysql -e "SELECT ROUTINE_NAME FROM INFORMATION_SCHEMA.ROUTINES WHERE ROUTINE_DEFINITION LIKE '%CONCAT%' AND ROUTINE_DEFINITION LIKE '%EXECUTE%';" ``` ## When to Use / When Not to Use | Use When | Don't Use When | Use Instead | |---|---|---| | Any application constructs SQL queries with user input | Application uses only static SQL with no external input | No injection risk — but parameterize anyway as a practice | | Building REST APIs that accept query parameters | Using a GraphQL layer with strict typing and no raw SQL | GraphQL resolvers with ORM query builders | | Migrating legacy code with string-concatenated SQL | Interacting with a NoSQL database (MongoDB, Redis) | NoSQL injection prevention techniques | | Writing stored procedures that accept parameters | Using a managed query builder that auto-parameterizes (Prisma, Drizzle) | ORM already handles it — but audit for .raw() usage | | Need to support dynamic column names or ORDER BY | All query structure is static (no dynamic columns/sorting) | Standard parameterized queries suffice | ## Important Caveats - Parameterized queries protect VALUES only — they cannot parameterize identifiers (table names, column names, schema names). Always use allowlists for dynamic identifiers. - ORM protection varies by method — `User.objects.filter(name=x)` in Django is safe, but `User.objects.raw(f"SELECT * FROM users WHERE name = '{x}'")` is not. Audit every raw query method. - Some database drivers have subtle differences in placeholder syntax: `?` (SQLite, MySQL JDBC), `%s` (psycopg2), `$1` (node-postgres, asyncpg), `@param` (ADO.NET), `:param` (cx_Oracle, Hibernate). - Multi-database applications need consistent parameterization across all database connections — a single unprotected query path is sufficient for compromise. - SQL injection is ranked #3 in the OWASP Top 10 (2021) and CWE-89 remains one of the most exploited vulnerabilities in production applications as of 2026. ## Related Units - [OWASP Top 10 Web Application Security Risks](/software/security/owasp-top-10/2026) - [Cross-Site Scripting (XSS) Prevention](/software/security/xss-prevention/2026) - [Database Indexing Strategies](/software/patterns/database-indexing-strategies/2026) - [SQL Query Optimization Patterns](/software/patterns/sql-query-optimization/2026)