使用mysqli防止SQL注入，如何设置NULL或CURRENT_DATE? [英] using mysqli to prevent sql injection, how to set NULL or CURRENT_DATE?

问题描述

中，在bind_param()方法中，一个将参数绑定到查询. bind_param()的第一个参数是types，是一个字符串，其中每个字符代表数据类型传递，例如，字符串的"s".

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;

如何将NULL或CURRENT_DATE表示为参数?

how can one represent NULL or CURRENT_DATE as a parameter?

尝试将'CURRENT_DATE'作为字符串，但将其发布为"0000-00-00". (据我所知，非法日期"0000-00-00"对于不使用NULL的人来说是NULL的mysqlism，这显然是很多的.)

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).

使用参数，如何使用NULL和CURRENT_DATE?

using parameters, how can one use NULL and CURRENT_DATE?

@Johan指出NULL 确实有效.在测试中，这似乎是正确的，但事实并非如此.发生的事情是，从第一个NULL参数开始， all 参数被设置为NULL！

@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!

默认值与此无关.很多时候，我希望将UPDATE中的列设置为NULL或CURRENT_DATE-而不是INSERT-因此默认值在事务中不起作用.

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.

此外，编写触发器以弥补mysqli不足的想法也是非常糟糕的编程-我今天写了意大利面条式触发器，这是我要谈的.触发更新日志的事情是一回事-像这样的触发器将是持续的维护噩梦，几乎无法正确维护代码.

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.

推荐答案

mysqli参数有限且难以使用.它们不仅不接受NULL，而且不接受内部函数(如CURRENT_DATE)作为参数，每个参数均通过引用传递. (这意味着需要为每个参数创建一个参数-对于INOUT过程参数或SELECT INTO可能是必需的，但是对于普通dml来说只是浪费时间.)最重要的是，会话显然没有存储它们或db，以提高后续调用的效率.

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.

(例如，与Postgres准备好的语句比较，该语句已命名并可以重复使用(尽管仅在会话级别).这意味着db不需要重新学习该语句；这也意味着语句已经计划好了"，因此不仅可以学习语义，而且可以学习执行计划并重新使用执行计划以提高速度.)

(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.)

目前，我希望为MySQL写一些实际可用的东西.稍后再发布.

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

这篇关于使用mysqli防止SQL注入，如何设置NULL或CURRENT_DATE?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！