Case Sensitive Database Query

Question

Possible Duplicate:
mysql case sensitive query

'm working on an PHP script that checks some values to the ones in the database. So far I thought it worked right, but I found an problem.

The query isn't looking for the specific characters.

So if I use this query:

SELECT *
FROM `facilitydb_login`
WHERE
facilitydb_login.`password` = 'PASS'

I want the result, because the password is PASS. But if you fill in pass (lowercase) the same entry is returned.

How can I make it case sensitive?

Is the `collation` for the column case sensitive? What database server are you using? If it's MySql (as you mention PHP so it's a fairly common combination) you can use COLLATE - http://dev.mysql.com/doc/refman/5.0/en/charset-collate.html — dash, Oct 19 '12 at 11:53
yes, i'm using MySQL. @dash, i don't know, how can i see that? — Mathlight, Oct 19 '12 at 11:57
@TWCrap Execute `SHOW FULL COLUMNS FROM db.table` where `db` and `table` are replaced with your database and table names — dash, Oct 19 '12 at 12:00
You can also use the `binary` keyword. See http://stackoverflow.com/questions/7857669/mysql-case-sensitive-query — dash, Oct 19 '12 at 12:01

score 6 · Accepted Answer · answered Oct 19 '12 at 11:56

6

you should use case sensitive collation, for example

SELECT *
FROM `facilitydb_login`
WHERE facilitydb_login.`password` collate latin1_general_cs = 'PASS'

CS in the name means that it's Case Sensitive

answered Oct 19 '12 at 11:56

Roman Pekar

Thank you, that did it. I will mark your answer in 7 minuts ;-) – Mathlight Oct 19 '12 at 11:59

Joe G Joseph · Answer 2 · 2012-10-19T13:10:23.487

2

you could make the column case sensitive

Assume you are using mysql database

ALTER TABLE facilitydb_login 
CHANGE password `password` VARCHAR(100) BINARY NOT NULL;

or If you want to make part of the select statement then

SELECT *
FROM `facilitydb_login`
WHERE BINARY 
facilitydb_login.`password` = 'PASS'

edited Oct 19 '12 at 13:10

answered Oct 19 '12 at 11:55

Joe G Joseph

2 Answers2