CompTIA DataSys+ (DS0-001)Database Management and MaintenanceEasy
A database administrator needs to implement a solution to prevent SQL injection vulnerabilities in a web application. The application frequently uses user-supplied input to construct SQL queries. Which of the following methods is the MOST effective way to mitigate this risk?
- AEscaping special characters in user input.
- BRestricting database user permissions to only necessary actions.
- CImplementing a web application firewall (WAF).
- DUsing parameterized queries or prepared statements.
Show answer & explanationAnswer & explanation
Correct answer: D. Using parameterized queries or prepared statements.
Parameterized queries (or prepared statements) separate the SQL code from the user-supplied data. This ensures that the input is treated purely as data, not as executable code, fundamentally preventing SQL injection attacks.
Why the other options are wrong
- A. Escaping special characters is a good practice but is prone to errors (e.g., forgetting to escape, double-escaping) and may not cover all attack vectors, making it less robust than parameterized queries.
- B. Restricting database user permissions is an essential security measure for limiting the damage of a successful attack, but it does not prevent the SQL injection vulnerability from occurring in the first place.
- C. A WAF can provide an additional layer of defense by filtering malicious traffic, but it's an external control and cannot fully prevent vulnerabilities within the application's code itself.
Parameterized Queries
A method of constructing SQL queries where placeholders are used for user input, and the input values are passed separately to the database.
- Separates SQL code from data, preventing malicious input from being executed.
- Often implemented using 'prepared statements' in programming languages.
- The most effective defense against SQL injection vulnerabilities.
Memory trick: Parameterize your input, don't just concatenate!