correct syntax for setting password using mysql 5.5.8 command line

Question

I've just downloaded and installed WAMPserver 2.1 and I want to set a password for the MySQL 5.5.8 database. I'm doing a tutorial at lynda.com and the tutor (Kevin Skoglund) instructions to type:

mysql> use mysql
Database changed

mysql> UPDATE user
    -> SET Password = PASSWORD('paSSword')
    -> WHERE user = 'root';

When I hit enter, I get this error about the WHERE statement:

ERROR 1064 (42000): 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' user='root'; at line 2

Does anyone know the correct syntax for the WHERE statement? His lessons were done in 2007, so I guess the syntax has changed because it worked for him in the video. This line was returned for him:

Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1  Warnings: 0
mysql>

Thanks

-1, very bad and insecure code. You should never store passwords like that. Use a secure hash and salt the passwords. — Johan
– Johan, Commented Sep 20, 2011 at 16:14
that's a bogus password, however, that is exactly how the tutor instructed. he just used different word with a number. — Chris22
– Chris22, Commented Sep 20, 2011 at 16:42
Never mind the exact password. You need to use a secure way of storing that info. This way of storing them is rubish. Your tutor needs to be flogged. — Johan
– Johan, Commented Sep 20, 2011 at 16:47
Wow. His lesson is at lynda.com lynda.com/PHP-tutorials/php-with-mysql-essential-training What is your suggestion. Maybe the lesson are old 2007 way. — Chris22
– Chris22, Commented Sep 20, 2011 at 16:53

Shai Mishali · Accepted Answer · 2011-09-20 16:09:18Z

10

This works on my test server:

mysql> UPDATE user SET password=PASSWORD('newpassword') WHERE user ='root';
mysql> Query OK

You are trying to set Password instead of password (lowercased)

Shai.

answered Sep 20, 2011 at 16:09

Shai Mishali

9,4425 gold badges60 silver badges83 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Chris22 Over a year ago

Shai, one more step after that is to type at mysql prompt: FLUSH PRIVILEGES; but I am getting this error: "ERROR 1064 (42000): 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 'FLUSH PRIVILEGES'; at line 1" . but in the video he gets: "Query OK, 0 rows affected (0.00 sec)" How to fix this?

Shai Mishali Over a year ago

Your syntax seems fine... Did you make sure you have the RELOAD privilege as stated in the manual? dev.mysql.com/doc/refman/5.0/en/flush.html

Chris22 Over a year ago

Thanks, Shai, I was just reading that page. I'm just learning PHP so I'm totally following the tutorial on lynda.com. The instructor has not introduced the RELOAD step yet, he just typed what showed you. I don't know the order of syntax to write and those instructions are not easy to follow. i understand what flush will do, but why i can not use it as the tutor did, is where i fail.

Chris22 Over a year ago

edit not your (Shai) instructions are not easy to follow, but the instructions at the dev.msql.com site are not easy to follow

Chris22 Over a year ago

Shai, i typed FLUSH privileges (lowercase) and it worked: "Query OK, 0 rows affected (0.03 sec)" I guess mySQL 5.5.8 is case sensitive in some cases?

mkj · Accepted Answer · 2016-11-08 13:34:46Z

6

I was having the same problem with the same lynda.com tutorial. The solution is:

UPDATE mysql.user SET Password=PASSWORD('cleartext password')
WHERE User='root';
FLUSH PRIVILEGES;

edited Nov 8, 2016 at 13:34

mkj

2,8515 gold badges25 silver badges28 bronze badges

answered Jan 19, 2013 at 21:40

Ivanavic

611 silver badge2 bronze badges

Comments

LucScu · Accepted Answer · 2017-08-01 14:27:42Z

2

Login to mysql as root, then

SET PASSWORD FOR 'user-name-here'@'hostname-name-here' = PASSWORD('new-password-here');

For a real scenario

SET PASSWORD FOR 'user_test'@'localhost' = PASSWORD('hello');

answered Aug 1, 2017 at 14:27

LucScu

3591 gold badge4 silver badges22 bronze badges

Comments

Jeremy Ma · Accepted Answer · 2013-05-20 15:04:13Z

1

I was having exactly the same problem, the problem was solved as follows:

UPDATE mysql.user SET Password=PASSWORD('YourPassword') WHERE User='root';

NOTE: The problem was that I put <>(angle brackets) instead of () (round brackets) into the program, I know it sounds really dumb, but this is what happened. Because () and <> appear to look the same on command line, I messes them up.

answered May 20, 2013 at 15:04

Jeremy Ma

111 silver badge1 bronze badge

Comments

Johan · Accepted Answer · 2011-09-20 17:15:27Z

-1

This code:

UPDATE user SET password=PASSWORD('newpassword') WHERE user ='root';

Will compile (for want of a better word), but is very insecure code:

Here's what the MySQL manual says about it:

Note
The PASSWORD() function is used by the authentication system in MySQL Server; you should not use it in your own applications. For that purpose, consider MD5() or SHA1() instead. Also see RFC 2195, section 2 (Challenge-Response Authentication Mechanism (CRAM)), for more information about handling passwords and authentication securely in your applications.

If you insist on storing passwords in your database, you should always salt them and use a secure hash.
MD5 and SHA1 are no longer secure. In 2005 (!) Bruce Scheier, a leading expert on this topic said: "It's time for us all to migrate away from SHA-1."
I suggest using SHA2 with a 512 bits hash length.

$user = mysql_real_escape_string($_POST['user']);
$password1 = mysql_real_escape_string($_POST['password1']);
$password2 = mysql_real_escape_string($_POST['password2']);
$oldpassword = mysql_real_escape_string($_POST['oldpassword']);

if not(empty($password1) and ($password1 == $password2) {
  $update = "UPDATE user 
  SET passhash = SHA2(CONCAT(id,'$password1'),512) 
  WHERE user = '$user' AND passhash = SHA2(CONCAT(id,'$oldpassword'),512)";

This ensures that the user can only change the password when he knows the old password.

The salt needs to be stored in the same row as the password, but does not need to be secret.
Just make sure you prefix it to the password.

Testing the password works like:

SELECT * FROM user 
WHERE username = 'root' 
  AND passhash = SHA2(CONCAT(id,'passwordexample'),512)

answered Sep 20, 2011 at 17:15

Johan

77.4k28 gold badges204 silver badges346 bronze badges

4 Comments

Chris22 Over a year ago

Johan, thanks very much for such details. I am just learning PHP today, so your code is very unfamiliar to me. I am in lesson 3 "configuring" which comes right after install WAMP. I'm learning through the lynda.com website. I'm a front-end coder. i'm using the mysql console and the prompts don't have the "$" sign. I'm not sure where to write what you have listed. i've asked a question at lynda.com to update the tutorial. It's not free and it seems to be very wrong from your and Shai's expertise.

Chris22 Over a year ago

maybe password for lesson don't have to be very secure? I don't know the lessons are 4 yrs old, thank you for your time and your help

Johan Over a year ago

The code is PHP code. You would type as part of your php page.

Nye Over a year ago

I know that this is a very old answer, but -1 because it's bafflingly wrong. Should just be deleted really; there's nothing salvageable. It's not like you actually have any choice about how MySQL stores its passwords so admonishing people for using it makes no sense.

Collectives™ on Stack Overflow

correct syntax for setting password using mysql 5.5.8 command line

5 Answers 5

5 Comments

Comments

Comments

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

Comments

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related