using mysqli to prevent sql injection, how to set NULL or CURRENT_DATE?

in the mysqli php library, in the bind_param() method one binds the parameters to the query. bind_param()'s first argument is types, a string where each character represents the datatype pass, eg, 's' for string.

$query = "update users set update_usr_id = ? where user_id = ?";
$arg1 = ($update_usr_id=$usr_id) ? '2011-01-01' : 'CURRENT_DATE';
$arg2 = $update_usr_id;

how can one represent NULL or CURRENT_DATE as a parameter?

for example, tried 'CURRENT_DATE' as string, but that posted as "0000-00-00". (as far as I can tell "0000-00-00", an illegal date, is a mysqlism for NULL for people who don't use NULL, which apparently is quite a few).

usin开发者_JS百科g parameters, how can one use NULL and CURRENT_DATE?

@Johan pointed out that NULL does work. on testing, this appears to be true, but is not. what happens is that, starting with the first NULL parameter, all parameters are set to NULL!

default values have nothing to do with this. There are many times I would wish to set a column to NULL or CURRENT_DATE in an UPDATE - not an INSERT - so default values play no part in the transaction.

also, the idea of writing a trigger to cover the inadequacy of mysqli is pretty bad programming - having written spaghetti triggers in my day, I am one to talk. triggers to update logs are one thing - triggers like this will be a constant maintenance nightmare with little odds the code will ever be maintained correctly.

If you want a default parameter, you can either specify a default in the database schema.
For current_date you need to set the datatype to timestamp not date, that way MySQL will automatically insert now() into the field upon missing data.

Or create a trigger

DELIMITER $$

CREATE TRIGGER BEFORE INSERT ON FOR EACH ROW
BEGIN
  IF new.mydate IS NULL THEN SET new.mydate = NOW();
END $$

DELIMITER ;

If you want to pass null just set the var to null:

$var = null;

Links
http://dev.mysql.com/doc/refman/5.1/en/alter-table.html
http://dev.mysql.com/doc/refman/5.1/en/create-trigger.html

mysqli parameters are limited and difficult to use. Not only do they not accept NULL and internal functions like CURRENT_DATE as parameters, each parameter to be passed by reference. (This means a parameter needs to be created for each parameter - which might be necessary for INOUT procedure arguments or SELECT INTO, but for normal dml is simply a waste of time.) On top of that, they are evidently not stored by the session or db to make subsequent calls more efficient.

(Compare to Postgres prepared statement, for example, which is named and my be re-used (howbeit only at the session level). This means the db does not need to be re-taught the statement; it also means the statement is already "planned", so not only the semantics but the execution plan is learned and re-used to increase speed.)

Currently writing something for MySQL that is, I hope, actually usable. Will post later.