Multiple parameters in SQL query in PHP

Question

I'm trying to use the following code to edit a row in a database using a userid parameter.

But with the code I get this error:

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 'SET firstname = IF(? <> '', ?, firstname), SET surname = IF(? <' at line 3

I'm really not sure what's the problem as everything looks in order in the SQL query. I've tried taking out the IF(? <> '', ?, ) but it still gives the error. I've double checked that all the column names in the table are correct. What am I doing wrong?

<?php


        include 'database_conn.php';

        if(!$conn)
        {
            echo "Error connecting to database";
        }

        if (mysqli_connect_errno()) 
        {
            echo "<p>Connection failed:".mysqli_connect_error()."</p>\n";
        }


        $id = isset($_GET['userId']) ? $_GET['userId'] : NULL; 

        $newUsername = isset($_REQUEST['updateUsername']) ? $_REQUEST['updateUsername'] : NULL;
        $newEmail = isset($_REQUEST['updateEmail']) ? $_REQUEST['updateEmail'] : NULL;
        $newPassword = isset($_REQUEST['updatePassword']) ? $_REQUEST['updatePassword'] : NULL;
        $newForename = isset($_REQUEST['updateForename']) ? $_REQUEST['updateForename'] : NULL;
        $newSurname = isset($_REQUEST['updateSurname']) ? $_REQUEST['updateSurname'] : NULL;
        $newDob = isset($_REQUEST['updateDob']) ? $_REQUEST['updateDob'] : NULL;


        $sql = "UPDATE Users
                SET username = IF(? <> '', ?, username),
                SET firstname = IF(? <> '', ?, firstname),
                SET surname = IF(? <> '', ?, surname),
                SET email = IF(? <> '', ?, email),
                SET password = IF(? <> '', ?, password),
                SET dateofbirth = IF(? <> '', ?, dateofbirth)
                WHERE id = ?";


        $stmt = mysqli_prepare($conn, $sql)
            or die(mysqli_error($conn));  

        mysqli_stmt_bind_param($stmt, "ssssssi", $newUsername, $newForename, $newSurname, $newEmail,
                            $newPassword, $newDob, $id)
            or die(mysqli_error($conn));  

        mysqli_stmt_execute($stmt)
            or die(mysqli_error($conn));  




        if($stmt)
        {
            echo "Details updated successfully";   
        }
        else
        {
            die(mysqli_error($conn));  
        }

        mysqli_close($conn);

        ?>

What exactly are you trying to do with that SQL syntax? `IF(? <> '', ?, username)` is all over the place. Are you just trying to set the username equal to `$_REQUEST['updateUsername'])`? I **strongly** recommend checking [**this existing answer**](http://stackoverflow.com/a/6514730/2341603). — Obsidian Age, Mar 15 '17 at 01:21
That isn't how UPDATE works. RTM https://dev.mysql.com/doc/refman/5.7/en/update.html — Funk Forty Niner, Mar 15 '17 at 01:35
Plus, I hope you're not going live with this; in storing plain text passwords. — Funk Forty Niner, Mar 15 '17 at 01:37
I found it on another stack overflow thread, wasn't massively sure what it was doing. All I want to do is not overwrite the value in the row with a blank string if no value is entered in the form. No Fred, it's not going live. — , Mar 15 '17 at 01:45
I'd like to know the link for it, because the syntax is wrong. Best you ping me when replying @Ajek doing as I did here. I'm not always present. — Funk Forty Niner, Mar 15 '17 at 01:47
http://stackoverflow.com/questions/11753665/stop-sql-from-updating-blank-or-empty-fields-from-my-update-form Here you are @Fred-ii- Is there any other way I can prevent the data in the table being overwritten with blank strings? — , Mar 15 '17 at 01:48
@Ajek You did something wrong; the syntax used in there was correct. Use only one `SET` and follow the syntax used in there and in the mysql manual on `UPDATE` I provided earlier. — Funk Forty Niner, Mar 15 '17 at 01:49
@Fred-ii- I've managed to get it working. Completely overlooked the multiple `SET` commands I included. However now I'm getting `mysqli_stmt_bind_param(): Number of variables doesn't match number of parameters in prepared statement` how exactly doesn't it match? I'm asking for 7 variables in the $sql and I'm asking for 7 variables in the mysqli_stmt_bind_param() call? — , Mar 15 '17 at 01:58
@Ajek I posted an answer below earlier. Make sure that you aren't trying to overwrite an existing row with a constraint on it or a foreign key. This could also be related to an auto_incremented column. and make sure all POST arrays contain values and that the columns can accept NULL values. This could have something to do with the `id` and the ternary operator used with `NULL` and an AI'd column does not accept NULL values, should this be the case. — Funk Forty Niner, Mar 15 '17 at 01:59
@Fred-ii- What exactly do you mean by make sure all POST arrays contain values? If the columns cannot accept NULL values will I need to use another method to achieve this? — , Mar 15 '17 at 02:19
@Ajek if you have an AI (auto incremented) column and that this fails `$id = isset($_GET['userId']) ? $_GET['userId'] : NULL;`, then you will need to change that. AI'd doesn't accept NULL values. If one of those POST arrays fail and that one or more columns is set to `NOT NULL`, then that too could account for the error. Everything needs to match and the column types, and the lengths to be long enough to hold the data. Edit: I have to go to bed now. Keep me posted under my answer below. I'll see what I can do tomorrow. — Funk Forty Niner, Mar 15 '17 at 02:24
@Fred-ii- I've just quickly removed the `IF(? <> '', ?, )` parts of the `UPDATE` query and now the code works perfectly. So the code works, it's that IF that's making things go haywire. With the IF removed if any fields are left blank then the database row is overwritten with a blank string and the user's previous data is lost. How can I prevent this from happening? — , Mar 15 '17 at 02:29

score 0 · Accepted Answer · edited May 23 '17 at 12:17

MySQL's "UPDATE" uses only one SET separated by commas:

https://dev.mysql.com/doc/refman/5.7/en/update.html

Your query should read as:

$query = "
  UPDATE `users`
  SET 
    `username` = IF(? <> '', ?, `username`),
    `firstname` = IF(? <> '', ?, `firstname`),
    `surname` = IF(? <> '', ?, `surname`),
    `email` = IF(? <> '', ?, `email`), 
    `password` = IF(? <> '', ?, `password`), 
    `dateofbirth` = IF(? <> '', ?, `dateofbirth`)
  WHERE `id` = ?
";

and seeing you based yourself on the following Q&A, have changed the syntax for it:

Stop sql from updating blank or empty fields from my update form

as per a link you left for me in the comments area.

You said in comments that this wasn't a live site. However, if and when you do decide to go live with this, use password_hash() and password_verify().

References:

Since storing plain text passwords is dangerous.

Note: The password column will need to be 60+ in length. The manual suggests 255 as being a good bet.

Multiple parameters in SQL query in PHP

1 Answers1