Search code examples
phpmysqlibindparam

Using mysql's INTERVAL with prepared statement

I prepare my DB request for prevent SQL injection with the extension Mysqlnd. A request like this work on my site :

SELECT a, b FROM table where a = ?;

This next request doesn't work on my site:

SELECT a, b FROM table where b > DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL ? ?);

Error log : PHP Fatal error: Call to a member function execute() on a non-object in ..." This is because the syntax of the request is wrong.

When I try it in my DB IDE, the double question mark count as one and not as 2 parameters.

How can I resolve this problem ?

Solution

  • With a placeholder, you can bind only data literals, in other words - strings and numbers.

    INTERVAL accepts two arguments, expression and unit.

    While the expression part is a number and can be bound all right, the unit part is a keyword and therefore cannot be bound. So you can only whitelist it. Here is a white-listing function I wrote that could help with the matter.

    $unit = white_list($_GET['unit'], ["DAY","MINUTE","SECOND"], "Invalid time unit name");
$sql = "SELECT a, b FROM table where b > DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL ? $unit)";

    it is not very tidy but at least concise and safe.