CompTIA DataSys+ (DS0-001)Database FundamentalsMedium
A database developer is writing a Python script to interact with a PostgreSQL database. The script needs to insert a new customer record with `CustomerID`, `FirstName`, `LastName`, and `Email`. To prevent SQL injection vulnerabilities and handle special characters correctly, which approach should the developer use when passing the customer data to the SQL `INSERT` statement?
- AString concatenation to build the SQL query with user input.
- BUsing f-strings (formatted string literals) to embed variables directly into the query string.
- CEscaping all special characters in the input using a custom function.
- DParameterized queries (prepared statements) with placeholders.
Show answer & explanationAnswer & explanation
Correct answer: D. Parameterized queries (prepared statements) with placeholders.
Parameterized queries (prepared statements) are the standard and most secure way to pass data to SQL statements. They separate the SQL command from the data, preventing SQL injection by ensuring that input values are treated as literal data, not as executable code.
Why the other options are wrong
- A. String concatenation is highly vulnerable to SQL injection, as user input can be interpreted as SQL code.
- B. F-strings, like simple string concatenation, embed variables directly and are also highly vulnerable to SQL injection.
- C. While escaping manually can help, it's prone to errors, incomplete, and less secure than parameterized queries, which handle all necessary escaping automatically and reliably.
Parameterized Queries (Prepared Statements)
A method of executing SQL queries where the SQL command and the data values are sent separately to the database. Placeholders are used in the SQL command for where data values will be inserted.
- Prevents SQL injection vulnerabilities.
- Improves performance by allowing the database to pre-compile the query plan.
- Handles data type conversion and special character escaping automatically.
- Standard practice for secure database interaction.
Memory trick: PARAMETERIZE your queries to BLOCK SQL INJECTION!