0
  1. I have created a 'user' table which stores username and password.
  2. Inserted user details using this query (for password, used PASSWORD() function) :

INSERT INTO user (username, password) VALUES ('testuser3', PASSWORD('helloworld'));

  1. Now to validate user, used the following query :

SELECT * FROM user WHERE username = 'testuser3' AND password = PASSWORD('helloworld');

  1. Idea is if the SELECT query return a row, the user is authenticated otherwise not. But response I am getting is 'Empty set' even though username and password are correct.

So what do I have to update in this SELECT query to get the desired result?

NOTE : this question is very particular to the scenario mentioned in the question. To actual store password use salted hash concept. More can found in comments below by O. Jones and martinstoeckli

jayant
  • 366
  • 4
  • 14
  • 3
    Please, please, please read this: http://php.net/manual/en/faq.passwords.php The concepts apply to any programming language. Don't reinvent the flat tire. – O. Jones Oct 26 '15 at 15:48
  • @ÁlvaroG.Vicario: password is a keyword (not reserved) - because it is a function for hashing a string for use as a password – PaulF Oct 26 '15 at 16:27
  • Works OK here : http://sqlfiddle.com/#!9/36258/1 – PaulF Oct 26 '15 at 16:34
  • @ÁlvaroG.Vicario: reserved words have (R) after them - see my SQL Fiddle which works – PaulF Oct 26 '15 at 16:36
  • @PaulF You are absolutely right. I've removed my bogus comments. I can't figure out why I got an error when I first ran the code :( – Álvaro González Oct 26 '15 at 16:38
  • @O.Jones I was curious why the particular code was not working hence I asked. Thanks for the link though. – jayant Jan 25 '17 at 00:25

1 Answers1

0

Found a workaround. I created the table using this query :

CREATE TABLE user (id INTEGER NOT NULL AUTO_INCREMENT, username VARCHAR(32), password VARCHAR(64), PRIMARY KEY (id));

When I increased length of username and password to 128, query works.

jayant
  • 366
  • 4
  • 14
  • 1
    A hint for future readers, the MySql function [password()](https://dev.mysql.com/doc/refman/5.5/en/encryption-functions.html#function_password) should not be used in an application, see the note in the documentation. A secure password hash cannot be handled by SQL itself, it must be verified by the calling development language, an example in PHP can be found in this [answer](http://stackoverflow.com/a/38422760/575765). – martinstoeckli Jan 25 '17 at 16:38