Why does this SQL UPDATE query not work with a variable for WHERE?

Question

this is my first post here at Stack Overflow. I know the question has been asked many times before. I went through many answers, tried all of them (except the correct approach obviously) and don't know what to try anymore.

I have an SQL table where every row has an "edit" button. When clicking it, I pass over the id of the selected row to edit.php. There, I get it and update the given row based on the id with the user input from the form. The first column is id which is set to AUTO_INCREMENT.

On a side note, I get the same error, no matter if I use WHERE id=$id"; or WHERE id='$id'";

The code which I think is closest to the correct approach is as follows and generates the error message below the code:

<html>
    <title>
        Video Archiv - New
    </title>
    
    <body>
        <?php
            include("connect.php"); 
            $id=$_GET['id'];
            echo "Details von Video #$id editieren:<br /><br />";
            
            if(isset($_POST['update']))
            {
                $sql =  "UPDATE VideoArchiv             
                        SET ('".$_POST["titel"]."','".$_POST["schauspieler"]."')
                        WHERE id=$id";

                        $result = mysqli_query($connect,$sql);

                if (mysqli_query($connect,$sql) === TRUE) 
                {
                    echo "Record updated successfully";
                } 
                else 
                {
                    echo "Error updating record: " . $connect->error;
                }
            }
            ?>

        <form action="edit.php" method="post"> 
            
            <label> Titel:</label><br/>
            <input type="text" name="titel" required><br/>

            <label>Schauspieler</label><br/>
            <input type="text" name="schauspieler" required><br/>
            <br />
            <button type="submit" name="update">Speichern</button>
                
        </form>

        <?php
            include("back.php");
        ?>
    </body>
</html>

Error message:

Error updating record: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('a','d') WHERE id=9' at line 2

Thanks a lot for your help and sorry for the duplicate question, but I really can't find the solution and am pretty desperate.

UPDATE:

The following code gives this error:

Fatal error: Uncaught Error: Call to a member function bind_param() on bool in /homepages/25/d72758610/htdocs/multimedia/edit.php:30 Stack trace: #0 {main} thrown in /homepages/25/d72758610/htdocs/multimedia/edit.php on line 30

<html>
    <title>
        Video Archiv - New
    </title>
    
    <body>
        <?php
            include("connect.php"); 
            $id=$_GET['id'];
            $title = $_POST["titel"];
            $schauspieler = $_POST["schauspieler"];

            if(empty($title))
            {
                echo "error";
            }
            elseif(empty($schauspieler))
            {
                echo "error";
            }
            else
            {
                $sql = "UPDATE users SET title=?, schauspieler=? WHERE id=?";
                $stmt= $connect->prepare($sql);
                $stmt->bind_param("ssi", $title, $schauspieler, $id);
                if($stmt->execute())
                {
                      echo "Succes";
                }
                else
                {
                  echo "something went wromg";
                }
            }
            ?>

        <form action="edit.php" method="post"> 
            
            <label> Titel:</label><br/>
            <input type="text" name="titel" required><br/>

            <label>Schauspieler</label><br/>
            <input type="text" name="schauspieler" required><br/>
            <br />
            <button type="submit" name="update">Speichern</button>
                
        </form>

        <?php
            include("back.php");
        ?>
    </body>
</html>

I presume your `where` isn't the issue - looks like it's the fact you're doing your `set` as you would an `insert` https://dev.mysql.com/doc/refman/5.7/en/set-variable.html - also please note the SQL injection vulnerabilities in your code — Can O' Spam, Jan 27 '20 at 10:29
Related: **Warning:** Your code is vulnerable to SQL Injection attacks. You should use parameterised queries and prepared statements to help prevent attackers from compromising your database by using malicious input values. http://bobby-tables.com gives an explanation of the risks, as well as some examples of how to write your queries safely using PHP / mysqli. **Never** insert unsanitised data directly into your SQL. The way your code is written now, someone could easily steal, incorrectly change, or even delete your data. — ADyson, Jan 27 '20 at 10:31
The error message tells you to "check the manual that corresponds to your MySQL server version for the right syntax to use". Did you check the manual: https://dev.mysql.com/doc/refman/8.0/en/update.html ? — Jocelyn, Jan 27 '20 at 10:38
@Jocelyn Yes, I checked the manual of course. However, since the line of code worked for UPDATE, I though it'll work for UPDATE as well — TheToastMachine, Jan 27 '20 at 11:20
@ADyson SomeRandomPerson, thanks a lot for pointing out the security issue. Will get into the security things once the UPDATE works properly. — TheToastMachine, Jan 27 '20 at 11:21
@TheToastMachine well, "getting into the security things" will require you to re-write the code which generates the query. So actually it would make more sense to do it now, otherwise you'll just end up writing/testing it twice. Plus as well as removing security vulnerabilities, it can help you avoid unexpected syntax errors (e.g. due to apostrophes in the input values, or silly mistakes in your code). So personally I'd get into good habits and do it the right way now, rather than write it one way and then have to change it again afterwards. — ADyson, Jan 27 '20 at 11:38
`UPDATE VideoArchiv` suddenly because `UPDATE users` - are you sure this is right? Also you are using object oriented, I assume your connection is procedural based so you need to use Dileks second example to work best and stop the fatal errors — Can O' Spam, Jan 27 '20 at 11:42
@TheToastMachine that is my mistake changing users to VideoArchiv will make both solutions work — , Jan 27 '20 at 11:48
@Dilek - your first suggestion won't work unless the OP rewrites to use object oriented - second is the only option for the OP at the minute — Can O' Spam, Jan 27 '20 at 11:49
@ADyson I got your point, will keep that in mind check out the security measures. Thanks a lot — TheToastMachine, Jan 27 '20 at 15:15
@Dilek a hint: https://stackoverflow.com/a/22662582/285587 the OP needs to configure error reporting for mysqli — Your Common Sense, Jan 27 '20 at 15:37

score 4 · Answer 1 · 2020-01-27T15:23:44.260

Very simple to avoid sql injections and use up to date codes and You have an error in your SQL syntax.

Here is an example :

   include("connect.php"); 
    $id=$_GET['id'];
    $title = $_POST["titel"];
    $schauspieler = $_POST["schauspieler"];

    if(empty($title)){
    echo "error";
    }elseif(empty($schauspieler)){
    echo "error";
    }else{

    $sql = "UPDATE VideoArchiv SET title=?, schauspieler=? WHERE id=?";
    $stmt= $connect->prepare($sql);
    $stmt->bind_param("ssi", $title, $schauspieler, $id);
    if($stmt->execute()){
      echo "Succes";
    }else{
      echo "something went wromg";
    }

    }

See more on : https://phpdelusions.net/mysqli_examples/update

UPDATE : First code will work for you, but if you still want to use procedural way then us this :

include("connect.php");
if ($_SERVER["REQUEST_METHOD"] == "POST") {

//Check if we get id 
$Testid = $_GET['id'];
if(empty($Testid)){
    echo "id is empty";
}else{
    $id = $_GET['id'];
}


$title = $_POST["titel"];
$schauspieler = $_POST["schauspieler"];

    if(empty($title )){
        echo "error". $title; 
    }elseif(empty($schauspieler)){
        echo "error". $schauspieler;
    }else{
       $sql = "UPDATE VideoArchiv SET title=?, schauspieler=? WHERE id=?";
       $stmt = mysqli_prepare($connect, $sql);
       mysqli_stmt_bind_param($stmt, 'ssi', $title, $schauspieler, $id);
       mysqli_stmt_execute($stmt); 
    }
}

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">

 <label> Titel:</label><br/>
 <input type="text" name="titel" required><br/>

 <label>Schauspieler</label><br/>
 <input type="text" name="schauspieler" required><br/>
 <br />
 <button type="submit" name="update">Speichern</button>

</form>

Comments are not for extended discussion; this conversation has been [moved to chat](https://chat.stackoverflow.com/rooms/206772/discussion-on-answer-by-dilek-why-does-this-sql-update-query-not-work-with-a-var). — Bhargav Rao, Jan 28 '20 at 08:52

Can O' Spam · Answer 2 · 2020-01-27T11:37:21.050

The issue that you have is the fact your code does not use the SET correctly, you currently have the following;

$sql =  "UPDATE VideoArchiv             
    SET ('".$_POST["titel"]."','".$_POST["schauspieler"]."')
        WHERE id=$id";

Which is used like you'd do an INSERT

To rectify the immediate issue, simply change to;

$sql =  "UPDATE VideoArchiv             
        SET field1 = '".$_POST["titel"]."',
            field2 = '".$_POST["schauspieler"]."'
        WHERE id=$id";

But this odes leave you open to SQL injection attacks, to do a quick and easy fix on this, something as simple as the following would be helpful;

$id = mysqli_real_escape_string($connect, $_POST["id"]);
$titel = mysqli_real_escape_string($connect, $_POST["titel"]);
$schauspieler = mysqli_real_escape_string($connect, $_POST["schauspieler"]);

$sql =  "UPDATE VideoArchiv             
        SET field1 = '{$titel}',
            field2 = '{$schauspieler}'
        WHERE id=$id";

I'd suggest reading into prepared statements as this would be a lot safer however

I know this has had the right answer to the question at hand prior to this post, but none have mentioned injection and how to resolve (even a soft way like here)

Thank you very much, also for pointing out the SQL injection issue. I'll get into the security related stuff once I get it working this way (I need to understand the bare basics first). Unfortunately none of your suggestions solve the issue and I'm still getting the same error. The SQL version is MySQL 5.7 — TheToastMachine, Jan 27 '20 at 11:26
@TheToastMachine - can you edit your question to include the new query? This will let us see what you're doing — Can O' Spam, Jan 27 '20 at 11:32

score -4 · Answer 3 · edited Jan 27 '20 at 10:37

-4

The following query can be used:

UPDATE VideoArchiv SET columnname1 = '".$_POST["titel"]."', columnname2 = '".$_POST["schauspieler"]."' WHERE id=$id

edited Jan 27 '20 at 10:37

MaartenDev

5,631
5
21
33

answered Jan 27 '20 at 10:29

Vishnu

1
1

3

Yes, this can fix the basic syntax issue. Although it would be more helpful to OP and other readers if you showed a version which didn't have obvious security vulnerabilities - and also it could easily get extra unexpected syntax errors if the POST variables contain things like apostrophes. – ADyson Jan 27 '20 at 10:31

Blive · Answer 4 · 2020-01-27T10:42:33.070

-4

Column Names are not givn in query

UPDATE table_name SET column_name1 = expr1, column_name2 = expr2, ... [WHERE condition];

So, your query will be something like this and check column names in database:

$sql =  "UPDATE VideoArchiv             
                SET titel='".$_POST["titel"]."',schauspieler='".$_POST["schauspieler"]."'
                WHERE id=$id";

Note: This is sql vulnerable, so please add mysql real escape function (https://www.php.net/manual/en/function.mysql-real-escape-string.php) or convert it to pdo.

edited Jan 27 '20 at 10:42

answered Jan 27 '20 at 10:31

Blive

88
3

2

Don't **have** to convert to PDO, while PDO does it better, mysqli does offer prepared statements - also your link is for the mysql real escape, not mysql**i** – Can O' Spam Jan 27 '20 at 10:46

Why does this SQL UPDATE query not work with a variable for WHERE?

UPDATE:

4 Answers4

Linked