Parameterized queries allow you to pass values into queries through special parameters rather than directly substituting variables. This enhances security and protects against SQL injection, as well as speeds up the execution of repeated queries through plan caching.
Dynamic SQL allows you to build and execute queries on the fly (e.g., using EXEC sp_executesql in MS SQL or PREPARE/EXECUTE in PostgreSQL). You must be very careful: directly substituting values into the query text can lead to SQL injections, syntax errors, and poor performance.
Example of a parameterized query (in Python with the psycopg2 library):
cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
Example of dynamic SQL in PostgreSQL:
EXECUTE format('SELECT * FROM %I WHERE value = %L', tablename, value);
Question: Why is simply escaping quotes ('), as some old applications do, insufficient to protect against SQL injections?
Answer:
Escaping quotes will not protect against all types of injections, especially if an attacker inserts a SQL structure (e.g., "; DROP TABLE users;"). Moreover, it does not cover all the nuances of different data types and does not protect against bypassing filters through encoding or special characters. Always use parameterized queries.
History
History
History
In a CRM, the letter "O'Connor" caused an error in the application that built SQL queries through simple string concatenation. Due to an unescaped single quote, no card with that surname could be saved, which was only discovered a year later.