how safe are PDO prepared statements

by

Strictly speaking, there’s actually no escaping needed, because the parameter value is never interpolated into the query string.

The way query parameters work is that the query is sent to the database server when you called prepare(), and parameter values are sent later, when you called execute(). So they are kept separate from the textual form of the query. There’s never an opportunity for SQL injection (provided PDO::ATTR_EMULATE_PREPARES is false).

So yes, query parameters help you to avoid that form of security vulnerability.

Are they 100% proof against any security vulnerability? No, of course not. As you may know, a query parameter only takes the place of a single literal value in an SQL expression. You can’t make a single parameter substitute for a list of values, for example:

SELECT * FROM blog WHERE userid IN ( ? );

You can’t use a parameter to make table names or column names dynamic:

SELECT * FROM blog ORDER BY ?;

You can’t use a parameter for any other type of SQL syntax:

SELECT EXTRACT( ? FROM datetime_column) AS variable_datetime_element FROM blog;

So there are quite a few cases where you have to manipulate the query as a string, prior to the prepare() call. In these cases, you still need to write code carefully to avoid SQL injection.

More Related Contents:

  1. How can I prevent SQL injection in PHP?
  2. SQL injection that gets around mysql_real_escape_string()
  3. Are PDO prepared statements sufficient to prevent SQL injection?
  4. Is “mysqli_real_escape_string” enough to avoid SQL injection or other SQL attacks?
  5. Reference: What is a perfect code sample using the MySQL extension? [closed]
  6. How do I use password hashing with PDO to make my code more secure? [closed]
  7. SQL injections in ADOdb and general website security
  8. Why is using a mysql prepared statement more secure than using the common escape functions?
  9. What is the PDO equivalent of function mysql_real_escape_string?
  10. Insert form in MySQL with PDO [duplicate]
  11. Undefined index $_POST
  12. How to check if a row exists in MySQL? (i.e. check if username or email exists in MySQL)
  13. The ultimate clean/secure function
  14. Getting raw SQL query string from PDO prepared statements
  15. How do I set ORDER BY params using prepared PDO statement?
  16. “Invalid parameter number: parameter was not defined” Inserting data
  17. Having issue with matching rows in the database using PDO
  18. How can I properly use a PDO object for a parameterized SELECT query
  19. How do I return integer and numeric columns from MySQL as integers and numerics in PHP?
  20. Real escape string and PDO [duplicate]
  21. PHP PDO vs normal mysql_connect
  22. Can I create a database using PDO in PHP?
  23. MySQL Prepared Statements
  24. PDO::PARAM for type decimal?
  25. PHP PDO and MySQLi [duplicate]
  26. PDO fetch one column from table into 1-dimensional array
  27. Update query with PDO and MySQL
  28. Getting a PHP PDO connection from a mysql_connect()?
  29. Having issue with matching rows in the database
  30. Using PDO Prepared Statement and Incrementing a column value

Leave a Comment