-2

Still new to this and i have come to a point where i am stuck as i cannot see what is going wrong.

I have a users page, when i click a user it opens a different page which shows user details. If i enter data in the input boxes and click update it runs update_user_data.php which should taken the new data and update the table. This however is not working.

It is returning "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 'WHERE username= 'jRogers'' at line 1"

Code is below. Please can anyone see where i am going wrong?

<?php
require '../php/config.php';

$usernameold = $_POST["username"];
$emailold = $_POST["email"];
$passwordold = $_POST["password"];

$sql = "UPDATE users SET username='$usernameold', email='$emailold', password='$passwordold', WHERE username= '" .$usernameold."'";

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

?>
jRogers
  • 37
  • 1
  • 10
  • this is a typo question – Funk Forty Niner Oct 08 '17 at 22:04
  • 1
    get rid of the last comma – John Conde Oct 08 '17 at 22:04
  • 3
    ...and the question IMHO. – Funk Forty Niner Oct 08 '17 at 22:04
  • 1
    You have a redundant comma before the `where` clause – Mureinik Oct 08 '17 at 22:04
  • 3
    Your script is at risk of [SQL Injection Attack](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) Have a look at what happened to [Little Bobby Tables](http://bobby-tables.com/) Even [if you are escaping inputs, its not safe!](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) Use [prepared parameterized statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php). – John Conde Oct 08 '17 at 22:04
  • 2
    **Never store plain text passwords!** Please use **[PHP's built-in functions](http://php.net/manual/en/function.password-hash.php)** to handle password security. If you're using a PHP version less than 5.5 you can use the password_hash() **[compatibility pack](https://github.com/ircmaxell/password_compat)**. Make sure you **[don't escape passwords](http://stackoverflow.com/q/36628418/1011527)** or use any other cleansing mechanism on them before hashing. Doing so changes the password and causes unnecessary additional coding. – John Conde Oct 08 '17 at 22:05
  • 1
    don't get your friends to upvote this, 2 upvotes, really?? – Funk Forty Niner Oct 08 '17 at 22:05
  • thanks for the advice everyone. I have got rid of the comma however i now get "Parse error: syntax error, unexpected '"'"' (T_CONSTANT_ENCAPSED_STRING) Line 8 – jRogers Oct 08 '17 at 22:07
  • No friends on here to up vote. – jRogers Oct 08 '17 at 22:08
  • Ill look into prepare statements now. Good advice – jRogers Oct 08 '17 at 22:09
  • [PHP Parse/Syntax Errors; and How to solve them?](https://stackoverflow.com/questions/18050071/php-parse-syntax-errors-and-how-to-solve-them) – John Conde Oct 08 '17 at 22:10

1 Answers1

1

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 'WHERE username= 'jRogers'' at line 1

The trick with MySQL is to check the string point before the "use near" location identified by the error.

So:

 ... password='$passwordold', WHERE username= '" .$usernameold."'";
                           ^^^

Before 'WHERE username= 'jRogers is the comma, the issue is that commas are entity deviders and should not be used at the end of the section.

Fix:

Remove the trailing comma before the MySQL Instruction word.

PLEASE heed the comments relating to the terrible security and bad practises employed in your SQL and Php.

Bonus:

As commented by John Conde, your issue:

  "Parse error: syntax error, unexpected '"'"'

is caused by having an odd / unbalanced number of quotes (of either type). This is probably caused by your failure to escape your PHP variables (a serious security issue as already mentioned in comments). So if any of your PHP variable values contains only one quote of a pair, this will be interpreted literally by MySQL, unless it is escaped.

Recommended fix: Read the link in all the comments; Use Prepared Statements.

Community
  • 1
  • 1
Martin
  • 22,212
  • 11
  • 70
  • 132