MySQL query and case sensitive

Navigation
2

I want my login.php to check if the password is uppercase, because when it exists, it recognizes it, since I have my database with the collation utf8_general_ci . I have already tried collation in utf8_bin and latin1_general_cs but recognize uppercase 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 from utf8_general_ci to any other?

    
asked by anonymous 20.05.2016 / 17:39

2 answers

1

You can do this check in php before. To make comparison between strings in MySql DB here is this simple way:

EX: 

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

That is: 

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 soon in login.php : 

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

I added BINARY to the query, and it worked. Result: 

$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 tip vlw.

    
20.05.2016 / 17:56
How to install OPENCV in PHP on a WAMP server? How to change the natbib language in LaTeX?