0

I created a database without defining the char set so it defaulted to Latin. Now I need to make a case sensitive search so I used the command:

    $sql = "SELECT * FROM Users WHERE username = '$value' COLLATE SQL_Latin1_General_CP1_CS_AS";

The problem is that it returns no matches. In my web page I have the <meta charset="utf-8"> in the head of the page, so I wonder if the charset encoding could be the problem. If I make a search without the COLLATE SQL_Latin1_General_CP1_CS_AS it works but it is case insensitive.

What could be the problem? Should I convert the database to always use utf-8?

davide l
  • 177
  • 1
  • 3
  • 9
  • The charset isn't the problem, but you should always use UTF-8 (utf8mb4). You have to use a case sensitive or binary collation. http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html – Charlotte Dunois Dec 06 '14 at 18:54
  • I would use case insensitive for usernames, that way it's easier for people to remember their username and not forget if they had any uppercase characters in their username. But that's not for me to decide, it's your decision. – SameOldNick Dec 06 '14 at 18:58
  • If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation – A l w a y s S u n n y Dec 06 '14 at 19:05
  • @Rangad I will check if `$value` is a valid username before using it to avoid injections, thanks – davide l Dec 06 '14 at 19:27
  • Please read over [How cna I prevent SQL injection in PHP](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php) to address the vulnerability inherent in your code. – Michael Berkowski Dec 06 '14 at 21:46

1 Answers1

0

Try binary operator:

$sql = "SELECT * FROM Users WHERE BINARY username = '$_POST[userName]'";
Aditya
  • 1,241
  • 5
  • 19
  • 29