How do I set ORDER BY params using prepared PDO statement?

by

Yes, you’re stuck inserting it directly in the SQL. With some precautions, of course. Every operator/identifier must be hardcoded in your script, like this:

$orders=array("name","price","qty");
$key=array_search($_GET['sort'],$orders);
$order=$orders[$key];
$query="SELECT * from table WHERE is_live = :is_live ORDER BY $order";

Same for the direction.

I wrote a whitelisting helper function to be used in such cases, it greatly reduces the amount of code that needs to be written:

$order = white_list($order, ["name","price","qty"], "Invalid field name");
$direction = white_list($direction, ["ASC","DESC"], "Invalid ORDER BY direction");

$sql = "SELECT field from table WHERE column = ? ORDER BY $order $direction";
$stmt = $db->prepare($sql);
$stmt->execute([$is_live]);

The idea here is to check the value and raise an error in case it is not correct.

More Related Contents:

  1. mysqli or PDO – what are the pros and cons? [closed]
  2. PDOException SQLSTATE[HY000] [2002] No such file or directory
  3. PHP PDO: charset, set names?
  4. How can I use PDO to fetch a results array in PHP?
  5. Why I am getting Cannot pass parameter 2 by reference error when I am using bindParam with a constant value?
  6. Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active
  7. Use bound parameter multiple times
  8. What is the difference between MySQL, MySQLi and PDO? [closed]
  9. how safe are PDO prepared statements
  10. Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?
  11. PHP PDO – Num Rows
  12. How to solve General error: 2006 MySQL server has gone away
  13. How to install pdo driver in php docker image?
  14. Replacing mysql_* functions with PDO and prepared statements
  15. MySQL retrieve variable from Stored Procedure in PHP PDO
  16. Calling stored procedure with Out parameter using PDO
  17. When *not* to use prepared statements?
  18. How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
  19. Setting a connect timeout with PDO
  20. In PHP with PDO, how to check the final SQL parametrized query? [duplicate]
  21. What is the best way to insert multiple rows in PHP PDO MYSQL?
  22. PDO MySQL: Insert multiple rows in one query
  23. How to bind LIKE values using the PDO extension?
  24. Error while using PDO prepared statements and LIMIT in query [duplicate]
  25. PDO prepared statement fetch() returning double results
  26. Warning: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  27. SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
  28. PDO::rowCount VS COUNT(*)
  29. PHP 7 RC3: How to install missing MySQL PDO
  30. Is a BLOB converted using the current/default charset in MySQL?

Leave a Comment