PHP UPDATE准备语句 [英] PHP UPDATE prepared statement

问题描述

我正在尝试学习使用准备好的语句来避免SQL注入等的正确方法.

Hi I'm trying to learn the proper way to use prepared statements to avoid SQL injections etc.

执行脚本时，我从脚本中收到一条消息，提示已插入0行，我希望这表示已插入1行，并且当然会更新表.我不完全确定自己准备好的陈述，因为我已经进行了一些研究，我的意思是每个例子都各不相同.

When I execute the script I get a message from my script saying 0 Rows Inserted, I expect this to say 1 Rows Inserted and of course update the table. I'm not entirely sure on my prepared statement, as I've done some research and I mean it varies from example to example.

当我更新表时，我需要声明所有字段，还是只更新一个字段就可以了吗?

When I'm updating my table do I need to declare all the fields or is it ok to just update one field??

任何信息都将非常有帮助.

Any information would be very helpful.

index.php

index.php

<div id="status"></div>

    <div id="maincontent">
    <?php //get data from database.
        require("classes/class.Scripts.inc");
        $insert = new Scripts();
        $insert->read();
        $insert->update();?>

       <form action="index2.php" enctype="multipart/form-data" method="post" name="update" id="update">
              <textarea name="content" id="content" class="detail" spellcheck="true" placeholder="Insert article here"></textarea>
        <input type="submit" id="update" name="update" value="update" />
    </div>

classes/class.Scripts.inc

classes/class.Scripts.inc

public function update() {
    if (isset($_POST['update'])) {
                    $stmt = $this->mysqli->prepare("UPDATE datadump SET content=? WHERE id=?");
                    $id = 1;
                    /* Bind our params */                           
                    $stmt->bind_param('is', $id, $content);
                    /* Set our params */
                    $content = isset($_POST['content']) ? $this->mysqli->real_escape_string($_POST['content']) : '';

                /* Execute the prepared Statement */
                        $stmt->execute();
                                    printf("%d Row inserted.\n", $stmt->affected_rows);

                                }                   
                            }

推荐答案

$stmt = $this->mysqli->prepare("UPDATE datadump SET content=? WHERE id=?");
/* BK: always check whether the prepare() succeeded */
if ($stmt === false) {
  trigger_error($this->mysqli->error, E_USER_ERROR);
  return;
}
$id = 1;
/* Bind our params */
/* BK: variables must be bound in the same order as the params in your SQL.
 * Some people prefer PDO because it supports named parameter. */
$stmt->bind_param('si', $content, $id);

/* Set our params */
/* BK: No need to use escaping when using parameters, in fact, you must not, 
 * because you'll get literal '\' characters in your content. */
$content = $_POST['content'] ?: '';

/* Execute the prepared Statement */
$status = $stmt->execute();
/* BK: always check whether the execute() succeeded */
if ($status === false) {
  trigger_error($stmt->error, E_USER_ERROR);
}
printf("%d Row inserted.\n", $stmt->affected_rows);

回答您的问题:

我从脚本中收到一条消息，提示已插入0行

I get a message from my script saying 0 Rows Inserted

这是因为绑定参数时，它们的顺序颠倒了.因此，您正在id列中搜索$ content的数值，该数值可能解释为0.因此UPDATE的WHERE子句匹配零行.

This is because you reversed the order of parameters when you bound them. So you're searching the id column for the numeric value of your $content, which is probably interpreted as 0. So the UPDATE's WHERE clause matches zero rows.

我需要声明所有字段，还是可以只更新一个字段?

do I need to declare all the fields or is it ok to just update one field??

在UPDATE语句中只设置一列是可以的.其他列将不会更改.

It's okay to set just one column in an UPDATE statement. Other columns will not be changed.

这篇关于PHP UPDATE准备语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！