PHP UPDATE 准备语句

PHP UPDATE prepared statement

提问by 001221

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

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

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.

当我执行脚本时，我从脚本中收到一条消息，说 0 行已插入，我希望这会说 1 行已插入，当然还要更新表。我不完全确定我准备好的声明，因为我已经做了一些研究，我的意思是它因示例而异。

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

索引.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

类/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);

    }                   
}

回答by Bill Karwin

$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);

Re your questions:

回复您的问题：

I get a message from my script saying 0 Rows Inserted

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

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.

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

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

我需要声明所有字段还是只更新一个字段就可以了？？

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

在 UPDATE 语句中只设置一列是可以的。其他列不会改变。

回答by Your Common Sense

In fact, prepared statements are not that complex as everyone thinks. Quite contrary, a prepared statement based code is the most simple and tidy way to execute a query. Take, for example, your code.

事实上，准备好的报表并没有大家想象的那么复杂。恰恰相反，基于准备好的语句的代码是执行查询的最简单和整洁的方式。以您的代码为例。

public function update($content, $id) {
    $stmt = $this->mysqli->prepare("UPDATE datadump SET content=? WHERE id=?");
    $stmt->bind_param('si', $content, $id);
    $stmt->execute();
    return $stmt->affected_rows;
}

As you can see, the code could be very simple and concise, if used properly!

如您所见，如果使用得当，代码可以非常简单和简洁！

You need basically only three lines:

你基本上只需要三行：

1. Prepare your query with placeholders
2. Then bind the variables (setting correct types for them first, where "i" stands for integer, "s" for string and so on)
3. And then execute the query.

1. 使用占位符准备查询
2. 然后绑定变量（首先为它们设置正确的类型，其中“i”代表整数，“s”代表字符串等等）
3. 然后执行查询。

As simple as 1-2-3!

就像 1-2-3 一样简单！

Note that instead of checking every function's result manually, you can set the reporting mode for mysqlionce for all. To do so, add the following line before mysqli_connect()/new mysqli:

请注意，您可以一次性设置 mysqli 的报告模式，而不是手动检查每个函数的结果。为此，请在mysqli_connect()/之前添加以下行new mysqli：

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

the result will be pretty much the same as with trigger_error but without a single extra line of code!

结果将与 trigger_error 几乎相同，但没有额外的一行代码！

回答by Manu Burrero Sánchez

I want to clean up Bill Karwin's awesome code

我想清理 Bill Karwin 的精彩代码

$stmt = $this->mysqli->prepare("UPDATE datadump SET content=? WHERE id=?") or die ($this->mysqli->error);

$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) or die ($stmt->error);

// 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 = (string)$_POST['content'] ?: '';

/* Execute the prepared Statement */
$status = $stmt->execute() or die ($stmt->error);


printf("%d Row inserted.\n", $stmt->affected_rows);

I recommend using "or die" instead of if clause I recommend forcing a variable type to take values:

我建议使用“or die”而不是 if 子句我建议强制变量类型取值：

// If id brings value: '12abc', PHP automatically stops it at 12
$id = (int)$_ POST ["id"];