dongwei8440 2017-06-20 12:25 采纳率: 100%
浏览 186
已采纳

“datetime-local”空输入导致MySQL查询失败

On my website, hosted on a LAMP stack, I have a simple html POST form with a bunch of inputs, and two datetime-local inputs

<form>
...
    <input id="PublicationDate" type="datetime-local" name="PublicationDate"/>
    <input id="ExpirationDate" type="datetime-local" name="ExpirationDate"/>
...
</form>

These input are matched against two Datetime field inside a MySQL table:

...
PublicationDate DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
ExpirationDate DATETIME,

Using this php code:

// Prepare query
$Query = $this->DB->prepare("
INSERT INTO Article(Title, Subtitle, Author, PublicationDate, ExpirationDate, Body)
VALUES(?, ?, ?, IFNULL(?, NOW()), ?, ?)");
$Query->bindParam(1, $Data["Title"]);
$Query->bindParam(2, $Data["Subtitle"]);
$Query->bindParam(3, $Username);
$Query->bindParam(4, $Data["PublicationDate"]); // <-
$Query->bindParam(5, $Data["ExpirationDate"]); // <-
$Query->bindParam(6, $Data["Body"]);

At the beginning, with the same MySQL/Php setup but without the actual fields in the html form, everything worked fine: PublicationDate was set to NOW() and ExpirationDate to NULL.

After I added the fields, if I leave those fields empty (in order to have NOW() and NULL as values) the query fails.

I think this problem is caused by the datetime-local inputs, because I have no problem leaving Subtitle field empty in order to have the corresponding MySQL row set to NULL. Any idea how I can solve this problem?

EDIT:

The $Data array is the $_POST array passed as a parameter to a function

  • 写回答

1条回答 默认 最新

  • dongyao1915 2017-06-20 12:44
    关注

    You are checking one of the values for NULL when you insert it and the other one not at all.

    However, when a form is sent in, the values will not be NULL, they will be empty strings so you probably want to set them to NULL if they are empty:

    $Query->bindParam(4, $Data["PublicationDate"] ?: null);
    $Query->bindParam(5, $Data["ExpirationDate"] ?: null);
    

    Note that this is very basic, you should add real validation to your fields to check if they match what you are expecting.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?

悬赏问题

  • ¥15 MATLAB yalmip 可转移负荷的简单建模出错,如何解决?
  • ¥15 数学的三元一次方程求解
  • ¥20 iqoo11 如何下载安装工程模式
  • ¥15 本题的答案是不是有问题
  • ¥15 关于#r语言#的问题:(svydesign)为什么在一个大的数据集中抽取了一个小数据集
  • ¥15 C++使用Gunplot
  • ¥15 这个电路是如何实现路灯控制器的,原理是什么,怎么求解灯亮起后熄灭的时间如图?
  • ¥15 matlab数字图像处理频率域滤波
  • ¥15 在abaqus做了二维正交切削模型,给刀具添加了超声振动条件后输出切削力为什么比普通切削增大这么多
  • ¥15 ELGamal和paillier计算效率谁快?