Using mysql's INTERVAL with prepared statement

Question

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 ?

I have not tested it, but I would assume you cannot parameterise the DAY/WEEK/... part of that query — RiggsFolly, Dec 19 '19 at 11:01
Does https://stackoverflow.com/questions/54128115/how-do-i-bind-an-interval-param-with-pdo help? — Nigel Ren, Dec 19 '19 at 11:02
@NigelRen no really helpful because I want to choose with my parameter if it's a MONTH/DAY/HOUR/... — Clément M, Dec 19 '19 at 11:08

Your Common Sense · Accepted Answer · 2019-12-19T12:03:27.700

3

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.

edited Dec 19 '19 at 12:03

answered Dec 19 '19 at 11:07

Your Common Sense

156,878
40
214
345

Or instead of white_list just use PHP's native `in_array` function – undefined Dec 09 '20 at 14:42
`$unit = in_array($_GET['unit'], ["DAY","MINUTE","SECOND"]) ? $_GET['unit'] : "Invalid";` – undefined Dec 09 '20 at 14:53
`$unit = "Invalid"` will result in SQL error – Your Common Sense Dec 09 '20 at 14:54
instead of `: "Invalid"` you can exit() die() throw new error etc – undefined Dec 10 '20 at 07:49
so it won't be "just in_array()" anymore but a chunk of PHP code. Then, for simplicity you may want to wrap it in a user-defined function. And call it white_list() – Your Common Sense Dec 10 '20 at 07:51

Gódi Péter · Answer 2 · 2022-08-31T06:06:23.857

Whitout extra functions, and the full INTERVAL list:

$timeFormat = "MINUTE"; //get the $timeFormat value from elsewhere
$timeFormatWhitelist = array("MICROSECOND","SECOND","MINUTE","HOUR","DAY","WEEK","MONTH","QUARTER","YEAR","SECOND_MICROSECOND","MINUTE_MICROSECOND","MINUTE_SECOND","HOUR_MICROSECOND","HOUR_SECOND","HOUR_MINUTE","DAY_MICROSECOND","DAY_SECOND","DAY_MINUTE","DAY_HOUR","YEAR_MONTH");

if (in_array($timeFormat, $timeFormatWhitelist)) {
    $query = "SELECT * FROM table WHERE `dateTime` <= DATE_SUB(CURRENT_TIMESTAMP(),INTERVAL ? $timeFormat)";
    // your prepared statement and do something with the result
}

Using mysql's INTERVAL with prepared statement

2 Answers2