Asked  7 Months ago    Answers:  5   Viewed   35 times

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

    }                   
}

 Answers

86
$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.

Wednesday, March 31, 2021
 
Zigglzworth
answered 7 Months ago
34

A query which updates no rows is NOT an error condition. It's simply a succesful query that didn't change anything. To see if an update actually did change anything, you have to use mysqli_affected_rows()

Wednesday, March 31, 2021
 
phirschybar
answered 7 Months ago
39

I can find nothing clear in the manual, but looking at the User Contributed Notes, the use of parameters is intended for actual values only, not table names, field names etc.

Normal string concatenation should (and can) be used.

$tablename = "tablename";
$stmt = $dbh->prepare("CREATE TABLE `$tablename` (id foo, int bar,...)");
Wednesday, March 31, 2021
 
kmunky
answered 7 Months ago
50

you forgetting the line to fetch the result. fetch() .

try that:

  $stmt->bind_result($first_name, $last_name);
  $stmt->fetch();  // ----- > you forget that line to fetch results.
  $stmt->close();
Wednesday, March 31, 2021
 
Anax
answered 7 Months ago
97

There are no parentheses in the SET clause of an UPDATE query.

http://dev.mysql.com/doc/refman/5.0/en/update.html

Hence the syntax error when the ( is hit. As long as you're trying to do things the right way with bound parameters, do it in the WHERE clause too!

Saturday, May 29, 2021
 
Dev
answered 5 Months ago
Dev
Only authorized users can answer the question. Please sign in first, or register a free account.
Not the answer you're looking for? Browse other questions tagged :