SQL Query is not checking case sensitively

Question

I am attempting to create a system to validate a user's username and password. I am currently have issues with checking the user name... If someone's username is say, Burrito, when I pass the query to see if it is in the database, it will not check if the case of the letters is correct. Example: Username is Michael They can enter michael or MiChAeL and it will still think it is the same username.

here is what I am using to check the username:

$isValid = mysql_query("SELECT username FROM " . getSQL_Info(3) . " WHERE username = '" . $user . "'");

Thank you for your time and help. Also, if you see any problems with my code, if something could be better or what not, please tell me :)

score 3 · Answer 1 · edited May 23 '17 at 12:12

You need to check your MySQL encoding type. If it is utf8_general_ci or latin1_swedish_ci (or anything ending in _ci) it is case insensitive.

You could work around this by changing you table encoding or using:

SELECT username FROM table WHERE BINARY username = :username

I would recommend the forma.

Related: How can I make SQL case sensitive string comparison on MySQL?

Also, if you see any problems with my code, if something could be better or what not, please tell me :)

You are using the deprecated mysql_* functions
- http://php.net/manual/en/book.pdo.php
- http://php.net/manual/en/book.mysqli.php
I can't see any escaping in your code

Make the decision server-wide, so change the encoding. – Alain Collins Sep 21 '12 at 00:35 — Alain Collins, Sep 21 '12 at 00:35

score 1 · Accepted Answer · answered Sep 20 '12 at 23:53

1

you are using deprecated mysql functions
be aware of security
the solution of your problem is

SELECT * FROM foo WHERE (BINARY username="someNaMe")

answered Sep 20 '12 at 23:53

Gianpaolo Di Nino

1,139
5
17

Thanks! Not to bother you, but is it possible that you could explain to be a bit more of the deprecated function, I see that there is this PDO::query which from what I can tell replaced mysql_query(). Thanks. I would just like to know what the '->' means on the manual page for this function, I have looked many places and I am unable to find a suitable explanation. – MichaelMitchell Sep 20 '12 at 23:59
sorry, but you found the answer but yourself :) pdo or mysqli replaced the deprecated mysql_* functions. – Gianpaolo Di Nino Sep 21 '12 at 00:01
the arrow in oop (object oriented programming) is needed to call methods (defined for that object..). But this covers a huge topic :D – Gianpaolo Di Nino Sep 21 '12 at 00:04
@MichaelMitchell: take a look here http://net.tutsplus.com/tutorials/php/object-oriented-php-for-beginners/ – Gianpaolo Di Nino Sep 21 '12 at 00:06
Hey, on last thing, I have updated my functions and all, but now when I enter something like: `$con->query("SELECT username FROM table WHERE username='". $user ."'");` It will always return true even if the $user does not exist in the database. Is there a way to make it return false if the specified username does not exist? Thanks again! – MichaelMitchell Sep 21 '12 at 08:01

score 0 · Answer 3 · answered Sep 21 '12 at 06:25

0

Use Collate to compare case sensitive strings. Here is a link to the MySQL manual

answered Sep 21 '12 at 06:25

Yaroslav

6,178
10
44
85

SQL Query is not checking case sensitively

3 Answers3