PHP-MYSQL Update Error

Question

In my database, in 'users' table there are coloumns as uname , name, sname , password and email. 'uname' is unique.
I couldn't update name,sname and email values.
I am receiving "

Error updating record: Erreur de syntaxe près de 'manager''' à la ligne 2

Firstly I do not know why it is in French. And 'manager' is the username that I defined in database.
$uname = $_SESSION['username']; value comes from previous script.

<?php
require_once("db_functions.php");
session_start();

$uname =  $_SESSION['username'];
$new_name="";
$new_sname="";
$new_email="";

if( !(isset($_SESSION['update'])) ||  $_SESSION['update'] != "1" )
{
$errorMsg= "Problem has occured in Update page";
echo $errorMsg;
// header  can be added.
}

else
{

 if(isset($_POST['Submit_update']))
    {
        $conn=db_connect();
       if ($conn) 
       {
        $SQL_select="SELECT * FROM users WHERE username=$uname";
        $select_result=mysqli_query($conn,$SQL_select);

        $new_name=mysqli_real_escape_string($conn,$_POST['name']);
        $new_sname=mysqli_real_escape_string($conn,$_POST['sname']);
        $new_email=mysqli_real_escape_string($conn,$_POST['email']);

      $SQL_update="UPDATE users SET name='$new_name', sname='$new_sname',
       email='$new_email' WHERE uname='$uname'";
      $update_result=mysqli_query($conn,$SQL_update);

        if ($update_result) { echo "Record updated successfully"; }
        else {  echo "Error updating record: " . mysqli_error($conn); }     
   mysqli_close($conn);

       }
else {
    $errorMsg=" Fail to Connect Database";
    echo $errorMsg;
  }   

    }


  }


?>

<!DOCTYPE html>
<html>
<head>
<title>Upload Page</title>
</head>
<body>
<form name="Update_Form" method="post" action="update.php">
    Name:<input type="text" name="name" value=""/><br/>
    <P>
    Surname:<input type="text" name="sname" value=""/><br/>
    <P>
    E-Mail:<input type="text" name="email" value=""/><br/>

    <input type="submit" name="Submit_update" value="Update"/>
</form>
</body>


</html>

Paste the line no/filename you are getting the error. It does not seem to come from the snippet you posted. — Hyder B., Apr 22 '16 at 20:53
In case anyone wonders why I closed the question. `WHERE username=$uname";` < string. — Funk Forty Niner, Apr 22 '16 at 21:01
@Fred-ii- That's not the query that's getting the error, the error is on the `UPDATE`. But if the `SELECT` is working, it means that `$uname` has quotes around it, which will then cause a problem when he puts additional quotes around it in the `UPDATE`. — Barmar, Apr 22 '16 at 21:03
The right solution for both queries is to use prepared queries with `mysqli_stmt_bind_param`. — Barmar, Apr 22 '16 at 21:03
@Barmar *"Firstly I do not know why it is in French. And 'manager' is the username that I defined in database. $uname = $_SESSION['username']; value comes from previous script."* and `"SELECT * FROM users WHERE username=$uname"` — Funk Forty Niner, Apr 22 '16 at 21:08
@Fred The error begins with `Error updating record:`. That's the error message printed after he performs the `UPDATE` query. He's never checking for errors from the `SELECT` query. — Barmar, Apr 22 '16 at 21:14
@Barmar put an answer in then, I reopened it. I'm not touching this. Sure hope they won't be chasing you down a deep rabbit hole. — Funk Forty Niner, Apr 22 '16 at 21:16
@Barmar I've corrected my `SELECT` query. I check it in a for loop with `$select_result` . It's okay now. But when I change my `UPDATE` query to `$SQL_update="UPDATE 'users' SET 'name'='$new_name', 'sname'='$new_sname', 'email'='$new_email' WHERE 'uname'='$uname'"; ` The error became like that `Error updating record: Erreur de syntaxe près de ''users' SET 'name'='', 'sname'='', 'email'='' WHERE 'uname'=''manager''' à la ligne 1` — Elvan, Apr 22 '16 at 21:21

Barmar · Accepted Answer · 2016-04-22T21:55:15.850

1

I suspect there's a quote in $uname. Since you're not escaping $uname, it's ending the string value.

You should use a prepared query instead of substituting variables, then you don't need to escape anything.

$stmt_update = mysqli_prepare($conn, "UPDATE users SET name= ?, sname= ?,
   email=? WHERE uname=?") or die("Error preparing update: " . mysqli_error($conn);
mysqli_stmt_bind_param($stmt_update, "ssss", $_POST['name'], $_POST['sname'], $_POST['email'], $uname);
mysqli_stmt_execute($stmt_update) or die(echo "Error updating record: " . mysqli_stmt_error($stmt_update));

If you have quotes around the value in $uname so that where username=$uname works without putting quotes into the query, you should not do that, it makes using the variable harder for the rest of the code. It will prevent the above query from working, because it will look for the literal quotes in the table contents.

edited Apr 22 '16 at 21:55

answered Apr 22 '16 at 21:25

Barmar

741,623
53
500
612

It's a good bet that $uname contains single quotes. Very likely, if the SELECT statement succeeded. Likely the value obtained from `$_SESSION['username']` is already quoted. (I detest the pattern of enclosing values in single quotes and escaping them prior to using them in a SQL statement. Using prepared statements with bind placeholders gets away from all that nonsense. If we aren't going to do that, then the job of quoting and escaping belongs where the values are concatenated into the SQL text. That's where the `mysqli_real_escape_string` wrapper function belongs. +10 – spencer7593 Apr 22 '16 at 21:42
@Barmar I changed my code as you wrote. Now there is no Error but the values don't updated in database. – Elvan Apr 22 '16 at 21:54
@Elvan Make sure you haven't put quotes into `$uname`. I've updated my answer to explain the problem with that. – Barmar Apr 22 '16 at 21:55
@Barmar`$uname = $_SESSION['username'];` `$SQL_select="SELECT * FROM users WHERE uname=$uname"; $select_result=mysqli_query($conn,$SQL_select); $stmt_update = mysqli_prepare($conn, "UPDATE users SET name= ?, sname= ?,email=? WHERE uname=?") or die("Error preparing update: " . mysqli_error($conn)); mysqli_stmt_bind_param($stmt_update, "ssss", $_POST['name'], $_POST['sname'], $_POST['email'], $uname); mysqli_stmt_execute($stmt_update) or die("Error updating record: " . mysqli_stmt_error($stmt_update));` It is like that now. Am I missing a point? – Elvan Apr 22 '16 at 22:22
What is the value of `$uname`? Does it have quotes in it? Take them out. – Barmar Apr 22 '16 at 22:23
What is it in `$_SESSION['username']`, though? – Barmar Apr 22 '16 at 22:36
User logins to system with a username. Then I kept it in `$_SESSION['username']`. If I write it as `$_SESSION[username]`, it says `constant username is not defined ` and again adds quotes. Then I add `$remove[]=" ' "; $uname =str_replace($remove, "", $uname); ` I don't know if it's efficient but quotes disappeared. And update has worked. – Elvan Apr 22 '16 at 23:41
I'm not talking about the quotes that you put around `username`, I know that's required. I want to know what `var_dump($_SESSION['username']);` shows. – Barmar Apr 22 '16 at 23:42
That shows that there are quotes around `manager`. You need to fix the code that's filling in the session variable so it doesn't do that. – Barmar Apr 22 '16 at 23:51

PHP-MYSQL Update Error

1 Answers1