PHP UPDATE prepared statement

问题:

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

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

<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

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

                                }                   
                            }

回答1:

$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

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??

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

回答2:

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.

As simple as 1-2-3!

Note that instead of checking every function's result manually, you can set the reporting mode for mysqli once for all. To do so, add the following line before 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!