How to match string in textbox with password in database row

Question

I had a database field which stores passwords like this:

TeacherPassword

j.lu01
pesci02
cricket01

Now I have a textbox where the user enters in a password and if it matches with one of the rows then it navigates to the next page. Below is the code where it checks for the password.

 $teacherpassword = (isset($_POST['teacherpassword'])) ? $_POST['teacherpassword'] : '';

  if (isset($_POST['submit'])) {

    // don't use $mysqli->prepare here
    $query = "SELECT * FROM Teacher WHERE TeacherPassword = ? LIMIT 1";
    // prepare query
    $stmt=$mysqli->prepare($query);
    // You only need to call bind_param once
    $stmt->bind_param("s",$teacherpassword);
    // execute query
    $stmt->execute(); 
    // get result and assign variables (prefix with db)
    $stmt->bind_result($dbTeacherPassword);

But what I have done is that I have updated the password field so that the field does not show the passwrod as a string, but as a bunch of characters so that their passwords are safe. Below is the update I did:

UPDATE Teacher
SET TeacherPassword = Password(TeacherPassword);

This causes the password field to look like this now:

TeacherPassword

    *6FF132E0666AC8462BC
    *FCF5F8CE105D0748315
    *FD4FA4B60EEF1E24050

So what my question is that in my MYsqli code, how do I get the string password entered in the textbox j.lu01 to match with its hash password in the database row *6FF132E0666AC8462BC?

For future visitors: The question and the answers here, although correct, are a little bit outdated. If you are looking for a way to hash user passwords in the database, then you should use [`password_hash()`](https://www.php.net/manual/en/function.password-hash.php), You could also take a look at [How do you use bcrypt for hashing passwords in PHP?](https://stackoverflow.com/a/6337021/1839439) — Dharman, Dec 28 '19 at 17:00

John Woo · Answer 1 · 2012-08-06T15:17:51.497

6

if that's the case then you need to hash the password first before searching it on the database. and by the way i tried running on sqlfiddle and generates different hash value

Password('j.lu01') => *7A48257211A5C6BF45C30374789E9027726D829D
Password('jlu_01') => *FCF5F8CE105D0748315591596E8A4F87A2E12B1A

-- your password field is truncated that's why it can't find the match also
-- alter TeacherPassword field and change it's length to 42

can you please check the length of your TeacherPassword field?

SQLFiddle

edited Aug 06 '12 at 15:17

answered Aug 02 '12 at 22:55

John Woo

258,903
69
498
492

I changed the length to 42 but login still doesn't work. In the TeacherPassword field it still displays this `*FCF5F8CE105D0748315` – user1394925 Aug 02 '12 at 23:10
1

@user1394925 when I try it in my server the output of `Password('jlu_01')` is `*FCF5F8CE105D0748315591596E8A4F87A2E12B1A` then why is that in yours is only `*FCF5F8CE105D0748315` ? where is the remaining `591596E8A4F87A2E12B1A`? – John Woo Aug 06 '12 at 12:48

score 1 · Answer 2 · answered Aug 02 '12 at 21:52

1

Just change the query to:

SELECT * FROM Teacher WHERE TeacherPassword = Password(?) LIMIT 1

answered Aug 02 '12 at 21:52

John Rasch

62,489
19
106
139

I have typed in the password `jlu.01` but it doesn't seem to accept it, this is when I tried your code – user1394925 Aug 02 '12 at 22:03
run the following "select Password('jlu_01')" in mysql and see if what you get is the same as what is stored in the database. It could be that the values in the database had white spaces before you updated it , or that the value read from the text box had white space. – Aug 02 '12 at 22:10
@A.J. I have done the select statement and it works fine, it is able to find the hash password for jlu_01, it just doesn't seem to work in the mysqli SELECT and WHERE clause – user1394925 Aug 02 '12 at 22:14
Did you add quotes in the where clause... Password('...')? – Shawn Aug 07 '12 at 18:55
No I did not add quotes, shud I? – user1394925 Aug 08 '12 at 02:02

Julien Ch. · Accepted Answer · 2012-08-08T07:03:20.800

You password field is limited to 20 characters, thus you hashed password have been trimmed to that length, try:

SELECT * FROM Teacher WHERE TeacherPassword = LEFT(Password(?),20)

But :

PASSWORD is meant for mysql internal use, you should really use SHA1 instead
You should really resize your password field (if you still have the non-encoded values).
You should consider using "salting" your password with a random string (say 6 characters) prepended to the passwords when hashing them, so that 2 identical passwords gets encoded differently.

For that later, you need to add a salt column to you table and populate it with some random string.

Then you could encode using :

UPDATE Teacher SET TeacherPassword = SHA1(salt @password)

SELECT * FROM Teacher WHERE TeacherPassword = SHA1(salt @password)

A pretty good final solution could be :

ALTER TABLE Teacher CHANGE TeacherPassword TeacherPasswordOld VarChar(20)
ALTER TABLE Teacher ADD TeacherPassword VarChar(40)
ALTER TABLE Teacher ADD Salt VarChar(40)

For udpate pass 2 values : @salt and @password

Update Teacher SET Salt=@salt, Password=SHA1(Salt @password)

Where the salt can be generate in php like this :

$salt = ""; 
for ($i = 0; $i < 40; $i++) { 
   $salt .= substr(
     "./ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz0123456789", 
     mt_rand(0, 63), 
     1); 
}

For checking password then you will use

SELECT * from Teacher 
-- if no "new" password, check the old one
WHERE TeacherPassword is null and TeacherPasswordOld=LEFT(Password(@password),20)) 
-- else check the new pass
   OR TeacherPassword = Sha1(salt @password)

Salt generator taken Here

So if I add a salt column into the table, lets say I have 3 rows, what do I fill in the 3 rows under the "Salt" column? do I type in any random string? Can you give me an example of this? — user1394925, Aug 08 '12 at 02:05
The salt needs to be random generated, i updated my answer please take a look — Julien Ch., Aug 08 '12 at 07:04

JDGuide · Answer 4 · 2012-08-06T12:49:23.997

You can write your query like below :-

SELECT * FROM Teacher_Table WHERE HEX(t_password)=HEX('Your_Password') LIMIT 1;

Use HEX(), MD5() for better security. This query is tested on MySQL.

Without the use of above functions :-

 SELECT * FROM Teacher_Table WHERE t_password='Your_Password' LIMIT 1;

Also you can use PASSWORD() as mentioned in the above answer.There are many other ways for make your query secure when retrieve.

It is recommended to use such above functions for avoid hacker .

How to match string in textbox with password in database row

4 Answers4