why i trying to update the data, but it show me the error on the line "$result=mysqli_query($connection,$query);" - PHP

caocaocaocao
February 18, 2023
211 views
0 votes
2 Answers

I have a problem on this, I can’t find where is the problem in my code, anyone help me, pls.

<?php

if($_POST['submit']) {
    $username = $_POST['username'];
    $password = $_POST['password'];
    $id = $_POST['id'];

    $query = "UPDATE users SET ";
    $query .="username = '$username' ";
    $query .="password = '$password' ";
    $query .="WHERE id = $id";

    $result = mysqli_query($connection, $query);

    if(!$result) {

        die ('QUERY FAILED' . mysqli_error($connection));

    }

}

?>

I need to update the new data into MySQL, but it show me the error message:

Fatal error: Uncaught mysqli_sql_exception: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘password=’av’ WHERE id=’

Answers

- MajharulNayem
- February 18, 2023 at 6:53 am
- 0 votes
0
Missing ‘,’ in your query.
```
<?php

if($_POST['submit']) {
    $username = $_POST['username'];
    $password = $_POST['password'];
    $id = $_POST['id'];

    $query = "UPDATE users SET ";
    $query .= "username = '$username', "; // missing ','
    $query .= "password = '$password' ";
    $query .= "WHERE id = $id";

    $result = mysqli_query($connection, $query);

    if(!$result) {

        die ('QUERY FAILED' . mysqli_error($connection));

    }

}

?>
```
The Update query should be :

UPDATE users SET username = 'username', password = 'password' where id = 1
Login or Signup to reply.

- user1191247
- February 18, 2023 at 12:54 pm
- 0 votes
0
As correctly pointed out by Majharul, the error is caused by the missing comma (,) between the columns listed in your SET clause. The error is almost always immediately preceding the part of the query returned in the error: password='av' WHERE id=.

More importantly, you should never store passwords in plain text, nor should you be simply concatenating strings and/or interpolating variables directly into your SQL. This is a very obvious SQL Injection vulnerability and easy to exploit. You should be using parameterized prepared statements to pass your variables into your query.

This is a simplistic example (validation of user input should be added) of how you might improve your code:
```
<?php

if($_POST['submit']) {
    $username = $_POST['username'];
    $password = password_hash($_POST['password'], PASSWORD_DEFAULT);
    $id = $_POST['id'];

    /* Prepare your UPDATE statement */
    $stmt = mysqli_prepare($connection, 'UPDATE users SET username = ?, password = ? WHERE id = ?');

    /* Bind variables to parameters */
    mysqli_stmt_bind_param($stmt, 'ssi', $username, $password, $id);

    /* Execute the statement */
    $result = mysqli_stmt_execute($stmt);

    if(!$result) {

        die ('QUERY FAILED' . mysqli_error($connection));

    }

}
```
Please read PHP docs for password_hash() for more detailed explanation.
Login or Signup to reply.