Mysql query and case distinguish

Question

I wish my login.php check if the password has uppercase, because when it exists, it recognizes, because I have my database with the collation utf8_general_ci. I already tried to put the collation on utf8_bin and latin1_general_cs but recognize capital letters as lowercase. Query in php:

$id = $_POST['id'];
$password = $_POST['pw'];

$consulta = mysql_query("SELECT id,password,tipo_ut FROM utilizador WHERE id ='".mysql_real_escape_string($id)."' AND password = '".mysql_real_escape_string($password)."' LIMIT 1");

Does anyone have another suggestion? And will there be any problem changing an entire database of utf8_general_ci to some other?

Answer 1

You can do this check in php before. To compare strings in DB Mysql here’s this simple way:

EX:

select 'Miguel' LIKE BINARY 'miguel'; // diferente
select 'Miguel' LIKE 'miguel'; // igual

That is to say:

mysql_query("SELECT id,password,tipo_ut FROM utilizador WHERE id ='".mysql_real_escape_string($id)."' AND BINARY password = '".mysql_real_escape_string($password)."' LIMIT 1");

But in its context and if I understood correctly my advice is to do login.php:

$temMaiusculas = preg_match('/[A-Z]/', $password); // 1 (true, tem maiusculas) ou 0 (false, não tem)

Answer 2

For the sake of performance put BINARY after the same, example:

SELECT * FROM temp1 WHERE col1 = BINARY "ABC" AND col2 = "DEF" ;

For your code:

$consulta = mysql_query("SELECT id,password,tipo_ut FROM utilizador WHERE id ='".mysql_real_escape_string($id)."' AND password = BINARY '".mysql_real_escape_string($password)."' LIMIT 1");

Answer 3

I added BINARY in the query, and it worked. Upshot:

$id = $_POST['id'];
$password = $_POST['pw'];

$consulta = mysql_query("SELECT id,password,tipo_ut FROM utilizador WHERE id ='".mysql_real_escape_string($id)."' AND BINARY password = '".mysql_real_escape_string($password)."' LIMIT 1");

Thanks for the vlw tip.