PHP/SQL Login Form - how to check password in database

Question

at the moment, i check the username and passwort of a HTML form like this on my database:

$exc = $dbh->prepare("SELECT id, cid, fullname FROM user WHERE username LIKE :username AND password LIKE :password");

I need to check both CASE SENSITIVE when executing the query.

Database data:

username: louis
password: goal43

Form data:

username: LOUIS
password: GOAL43

returns true - how do i fix this ?

You've prepared the query! That's an important step done! The next step is to execute it! — Ben Hillier, Sep 22 '17 at 07:14
Tried to check this link. It might help you https://stackoverflow.com/questions/24142512/pdo-bindparam-case-sensitive — Sherwin Obciana, Sep 22 '17 at 07:20
You mean the BINARY in front of password ? gonna check that now. — , Sep 22 '17 at 07:21
LIKE is also a whole lot of fun if I have a `%` in the username (or password if they're being stored as plaintext) that I enter in the login form.... e.g. `a%` will probably return quite a lot of records — Mark Baker, Sep 22 '17 at 07:21
@SherwinObciana thats the solution i was looking for - BINARY in front of the password / username. — , Sep 22 '17 at 07:23
my project is under delevoping at the moment, there has been no time for security yet. — , Sep 22 '17 at 07:26

score 2 · Accepted Answer · answered Sep 22 '17 at 07:28

2

$exc = $dbh->prepare("SELECT id, cid, fullname FROM user WHERE BINARY username LIKE :username AND BINARY password LIKE :password");

I hope it helps you. The link I've given to you

answered Sep 22 '17 at 07:28

Sherwin Obciana

319
1
11

score 0 · Answer 2 · edited Sep 22 '17 at 07:25

0

You have written query in prepare. Next step to assign parameters to :username and :password parameter you can do the by bindParam(). Which is given below.

$stmt = $dbh->prepare("SELECT id, cid, fullname FROM user WHERE username = :username AND password = :password");
$stmt->bindParam(':username ', $username);
$stmt->bindParam(':password', $password);
$stmt->execute();

edited Sep 22 '17 at 07:25

Puja

451
2
5
20

answered Sep 22 '17 at 07:16

Pankaj Makwana

3,030
6
31
47

1

Why negative vote? Please provide proper explanation – Pankaj Makwana Sep 22 '17 at 07:17
@ downvoter, whats the wrong in Pankaj Makwana solution ? – Puja Sep 22 '17 at 07:17
1

The OP is most likely executing the query, the OP asked `I want to check if the letters are upper or lowercase` – Lawrence Cherone Sep 22 '17 at 07:20
i need to check for upper and lowercase - sorry for my bad english. – Sep 22 '17 at 07:24

score 0 · Answer 3 · answered Sep 22 '17 at 07:17

0

just use = instead of LIKE

LIKE is case-insensitive

answered Sep 22 '17 at 07:17

Michael M.

189
1
7

Yes, i didn't know about the BINARY also. – Michael M. Sep 22 '17 at 07:26

score 0 · Answer 4 · answered Sep 22 '17 at 07:42

0

This question is already asked and contains answer , you can see this link there are very good answers for your query Compare string with case sensitive data

answered Sep 22 '17 at 07:42

Muhammad Aadil Banaras

1,134
1
11
21

these are just other solutions. – Sep 22 '17 at 08:12
yes but you can use them to solve yours query , you can use like as well as = , but if you use = then query must be like this Select * from a_table where attribute = 'k' COLLATE Latin1_General_CS_AS this will match if cases of characters are matched – Muhammad Aadil Banaras Sep 22 '17 at 09:53

PHP/SQL Login Form - how to check password in database

4 Answers4