Any reason why this stopped working? PHP / mysql

Question

So this was previously working using a replace but would a replace empty fields are that not present upon update?

Since moving to the update, the $id is not being read even though it's clearly shown on the statement.

function updateData($table,$id,$data){
    
        $extra = '';
        foreach ( $data as $key=>$row )
        {
            $set[]= $key."='".$row."'";
        }
        $set = implode(',',$set);

        if($id){
            $extra = "WHERE `id`='$id'";
        }

        $statement = "UPDATE `$table` SET ".$set." ". $extra;
        print_r($statement);
        $this->printDebug('Update Table Data',$statement);
        if($stmt = $this->conn->prepare($statement)){

            $stmt->execute() or trigger_error($stmt->error, E_USER_ERROR);
            $last_id = $stmt->insert_id;
            $response = array("message"=>"success","last_id"=>$last_id); 
            return ($response);

        } else {

            // die($this->conn->error);

            trigger_error($this->conn->error,E_USER_ERROR);

        }
    }

UPDATE `categories` SET category_status='1',featured='1',category_name='Health & Beauty',parent_id='0',price_low='',price_high='',category_description='',id='1',tags='null',date_added='2022-06-18 22:11:31',last_modified='2022-06-18 22:11:31',meta_type='category',meta_id='0',images='{"path":[""]}'

$insertData = $b->updateData('products',$_POST['id'],$_POST);

_"Since moving to the update, the $id is not being read even though it's clearly shown on the statement."_ What do you mean by "not being read"? Also, `if($id)` uses a [truthy](https://stackoverflow.com/questions/54765907/php-checking-a-variable-with-empty-or-truthy) test, which is probably a bad idea. Also, you're wide open to [SQL injection](https://stackoverflow.com/questions/332365/how-does-the-sql-injection-from-the-bobby-tables-xkcd-comic-work). Consider using [parameterized queries](https://stackoverflow.com/questions/4712037/what-is-parameterized-query). — kmoser, Jun 18 '22 at 22:11
Thanks for the suggestion about mySQL injection, it is getting updated (the replace function uses this). if(empty($id)){ die('empty string'); $extraz = "WHERE `id`='$id'"; } In the query, it has id='1' meaning id is available in the function but not being found? It's odd? — TerryB, Jun 18 '22 at 22:19

score 0 · Answer 1 · answered Jun 18 '22 at 22:52

It looks like your php function gets values for your table's id column from two different places.

the $id parameter.
the $data['id'] element of the $data array parameter.

And, from the line of SQL you showed us, your function was called with the $id parameter set to zero: your SQL has no WHERE clause. As you may know, UPDATEs without WHERE clauses try to update all rows in the table !!.

In your case the UPDATE failed because you have SET ... id=1 ... in the SQL. The id column is your primary key, so duplicate values are not allowed. (You can't set the id of every row in the table to 1.) Therefore your SQL statement failed.

You're lucky. Your table's data could have been obliterated. Be careful with WHERE clauses in UPDATE and DELETE statements, mmmk? If you get them wrong, you trash your data.

To get this working make sure your function's caller gives you the correct $id value. Or, remove that parameter completely from the function signature, and get $id from your $data array.

Any reason why this stopped working? PHP / mysql

1 Answers1