312-50 Question 366
Single answer▪ SQL Injection CountermeasuresA retail company's customer portal was found vulnerable during an internal assessment. The tester successfully bypassed authentication by entering ' OR '1'='1 in the username field. The development team says they already strip single quotes from some inputs and hide database error messages in production. You have been asked to recommend the most effective countermeasure to prevent this type of SQL injection across the application without relying on fragile input patterns. Which action should the security team prioritize?
- A
Replace dynamic SQL string concatenation with parameterized queries or prepared statements throughout the application
- B
Continue filtering single quotes and add blacklisted keywords such as SELECT, UNION, and OR to the input validation rules
- C
Suppress verbose database error messages and return generic HTTP 500 responses for all database failures
- D
Encode all user input with HTML entity encoding before sending it to the database
Show answer and explanation
Correct answer: A
Explanation
The best answer is to use parameterized queries or prepared statements. SQL injection occurs when untrusted input is concatenated into SQL commands and changes the intended query structure. In the scenario, the payload altered the authentication query logic, which is a classic sign of unsafe dynamic SQL. Industry guidance consistently identifies parameterized queries as the primary defense. OWASP SQL Injection Prevention Cheat Sheet recommends prepared statements with parameterized queries as Defense Option 1, with allow-list input validation as a secondary measure where appropriate and escaping as strongly discouraged as a sole defense. Error suppression can reduce reconnaissance value for attackers but does not stop injection, and blacklist filtering is known to be unreliable. In practice, robust countermeasures also include least-privilege database accounts, stored procedures only when safely parameterized, secure coding review, and testing to verify that user input cannot modify SQL syntax.
- A. Correct.
Correct. Parameterized queries, also called prepared statements, separate SQL code from user-supplied data so input is treated as a value rather than executable SQL. This is the primary and most reliable defense against SQL injection in application code. In the given scenario, the vulnerability exists because user input is being incorporated into a query in a way that changes query logic. Using bound parameters directly addresses that root cause.
- B. Incorrect.
Incorrect. Blacklist-based filtering is a weak countermeasure because attackers can bypass keyword filters using alternate syntax, casing, comments, encoding, or DBMS-specific behaviors. Stripping single quotes also fails because SQL injection is not limited to a single character and can sometimes be achieved without quotes depending on query context. This option reflects a common but brittle misconception: trying to block malicious patterns instead of eliminating unsafe query construction.
- C. Incorrect.
Incorrect. Hiding detailed database errors is a useful hardening measure because it reduces information leakage, but it does not prevent SQL injection. An attacker can still exploit injectable parameters through blind techniques even when errors are suppressed. This is defense-in-depth, not the primary fix for the vulnerability described.
- D. Incorrect.
Incorrect. HTML entity encoding is intended for output handling in web pages to reduce cross-site scripting risk, not for preventing SQL injection. Encoding input before database use does not safely separate code from data in SQL statements. This option confuses output encoding for browser contexts with secure database query construction.