MySQL UPDATE query problems

Question

I have a super easy question. I have a form that echoes out a mySQL record that the user can update. I make my changes, and it tells me that the update is successful, but when I look at the table, the changes do not go through. What is the problem here?

This is the first script.

<?php
require_once("models/config.php");
?>

<table border=1>
 <tr>
<td align=center>Edit Form</td>
  </tr>
  <tr>
   <td>
  <table>
  <?
  $personid=$_SERVER['QUERY_STRING'];

  $order = "SELECT * FROM persons where personid='$personid'";

  $result = mysqli_query($mysqli,$order);
  $row = mysqli_fetch_array($result);
  ?>
  <form method="post" action="edit_data.php">
  <input type="hidden" name="id" value="<? echo "$row[personid]"?>"> 
  <tr>Person ID:<? echo "$row[personid]"?></tr>
    <tr>        
      <td>First Name</td>
      <td>
        <input type="text" name="firstname" 
    size="20" value="<? echo "$row[firstname]"?>">
      </td>
    </tr>
    <tr>
      <td>Surname</td>
      <td>
        <input type="text" name="surname" size="40" 
      value="<? echo "$row[surname]"?>">
      </td>
    </tr>
    <tr>
      <td align="right">
        <input type="submit" 
      name="submit value" value="Edit">
      </td>
    </tr>
     </form>
    </table>
    </td>
 </tr>
  </table>
  </body>
  </html>

Which then goes through to this:

<?
require_once("models/config.php");

 $personid = $_POST['personid'];
 $firstname = mysqli_real_escape_string($mysqli, htmlspecialchars($_POST['firstname']));
 $surname = mysqli_real_escape_string($mysqli, htmlspecialchars($_POST['surname']));

 $order = "UPDATE persons SET firstname='$firstname', surname='$surname' WHERE   personid='$personid'";
$result = mysqli_query($mysqli,$order);
 if (!$result) {
echo "Error entering data! <BR>";
echo mysql_error();
} else {
echo "User updated to $firstname $surname <BR>";
}
?>

Is there something I am missing here?

Thanks in advance.

You are vulnerable to SQL injection attacks. and are using mysql_error() (the one WITHOUT an i), instead of mysqli_error() (WITH an i). — Marc B
– Marc B, Commented Mar 18, 2014 at 20:53
Your HTML input is named name="id" but your PHP references $_POST['personid']. — showdev
– showdev, Commented Mar 18, 2014 at 20:53
Mix of mysqli_* and mysql_* functions for one thing echo mysql_error(); so that won't help you. Change it to echo mysqli_error($mysqli); and it will tell you what's "not" going on. — Funk Forty Niner
– Funk Forty Niner, Commented Mar 18, 2014 at 20:54
I don't get any output for 'mysqli_error()'. I get a success message. — JLA
– JLA, Commented Mar 18, 2014 at 20:55

Olvathar · Accepted Answer · 2014-03-18 20:59:13Z

2

You are sending a hidden input named id and trying to use a $_POST['personid'] correct that

You may also pay attention to the comments you had (SQL Injection's one at least)

edited Mar 18, 2014 at 20:59

answered Mar 18, 2014 at 20:54

Olvathar

5513 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

JLA Over a year ago

Hi, yes - that was the exact problem! As I said in the comments above, I thought that the htmlspecialchars command and escape_string was enough. I'll be giving it a try and hopefully I;ll get some functioning code.

JLA Over a year ago

Hi, @Olvathar I've revised the script to include prepared statements and pasted it as an answer to my own question. Would you mind taking a look?

GolezTrol · Accepted Answer · 2014-03-18 20:57:48Z

0

Your form sends the id in the field id, while you refer to it as personid.

The reason why this appears to be working, is that the update in itself is correct. $personid is treated as an empty string, so the update correctly updates all records that have an empty personid, which is no record at all.

answered Mar 18, 2014 at 20:57

GolezTrol

116k19 gold badges186 silver badges215 bronze badges

Comments

JLA · Accepted Answer · 2014-03-19 16:44:00Z

0

OK, so here is a revised script with prepared statements. The script is working in the sense that updates are being made to the records. Two questions: 1) is this safe from My-SQL injections? 2) This is updating records successfully, but now it is echoing out "Error entering data!", how come?

<?
require_once("models/config.php");

$personid = $_POST['personid'];
$firstname = mysqli_real_escape_string($mysqli, htmlspecialchars($_POST['firstname']));
$surname = mysqli_real_escape_string($mysqli, htmlspecialchars($_POST['surname']));

$order = "UPDATE persons SET firstname=?, surname=? WHERE personid=?";
$stmt = mysqli_prepare($mysqli, $order);
mysqli_stmt_bind_param($stmt, "ssi", $_POST['firstname'], $_POST['surname'],   $_POST['personid']);
mysqli_stmt_execute($stmt); 


$result = mysqli_query($mysqli,$stmt);
if (!$result) {
echo "Error entering data! <BR>";
echo mysqli_error($mysqli);
} else {
echo "User updated to $firstname $surname <BR>";
}
?>

I'm sure the second question is a rather boneheaded one - do I just reverse the conditions?

answered Mar 19, 2014 at 16:44

JLA

1002 silver badges10 bronze badges

3 Comments

Nate C-K Over a year ago

On failure, mysqli_query returns FALSE (so $result === false). On success, it returns the number of rows updated. What you are doing treats 0 and FALSE the same way. It will help you debug if you treat them differently. I suggest you also print the parameter values in your script output to help you be sure you're really sending the database the values that you think you're sending.

JLA Over a year ago

putting in to '$result === false' works. Is the script OK in terms of SQL injection vulnerability?

Olvathar Over a year ago

First to say is that mysqli_stmt_execute() DO execute the query, you don't need mysql_query($mysqli,$stmt); line, you'll just update the line twice About SQL injection you can read here stackoverflow.com/questions/14199690/… that prepared statements are strong against SQL injection Perhaps you will also find usefull check mysqli_stmt_affected_rows($stmt) if you want to know if any row was affected

Collectives™ on Stack Overflow

MySQL UPDATE query problems

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

2 Comments

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related