Why cant you pass MYSQL functions into prepared PDO statements?

Question

In my mind, the following script should work:

$stmt = $db->prepare("UPDATE table SET status = ?, date_modified = ?");
$stmt->execute(array(1, 'NOW()'));

but when passing NOW() into the prepared statement, nothing happens. Replacing NOW() with an actual date (i.e. 2010-11-23) works just fine.

I am unable to find explanation online. Any ideas?

EDIT

Just to further clarify and rid of any confusion in the question, I want to actually pass a variable into the prepared statement HOWEVER, the variable will be set to one of five possible date/time functions for mysql.

e.g.

$var = 'NOW()';

$var = 'LAST_DAY(DATE_ADD(CURDATE(), INTERVAL 1 MONTH))';

$var = 'LAST_DAY(CURDATE())';

... and so on...

prepared statement turns into:

$stmt->execute(array(1, $var));

I know this will return the same NULL results, but I am worried if I simply change the sql statement to:

UPDATE table SET status = ?, date_modified = $var

I am opening myself to injection?

Answer 1

You do not need to pass NOW() as a parameter as there is no need to do any processing on it, given it is a built in SQL Function, so just include it in the actual query like below.

$stmt = $db->prepare("UPDATE table SET status = ?, date_modified = NOW()");

Alternatively, you can just set the date_modified to a TIMESTAMP field and it will automatically update the date_modified field on a SQL Update.

Answer 2

Prepared statements interpret everything you insert into them as a literal string. This is to prevent any type of unpredictable SQL injection.

What is actually happening is that NOW() is attempting to be inserted into the database just as it reads (literally, NOW()) instead of getting the actual date to insert. It is then probably showing blank in your database because you have a date column, which doesn't interpret NOW() as a date and therefore doesn't accept it.

If possible, you should try to execute the SQL without using any substitution methods as there is nothing dangerous to this approach.