Case sensitive php mysql login

Question

I have a simple authentication script that is working but i discovered that for a user admin with password admin, a user ADMIN with password ADMIN can also log in. How do i make this script case sensitive. Also i am aware of the enycriptions that can be done so that the password is not stored as text, just need to figure out how to make this case sensitive.

        if($_SERVER['REQUEST_METHOD'] == "POST" &&  mysql_real_escape_string($_POST['username'])!="" && mysql_real_escape_string($_POST['password']) !="") { // receive form sent via POST method

        $username = mysql_real_escape_string($_POST['username']); // prevent sql injection by using  "mysql_real_escape_string()"
        $password = mysql_real_escape_string($_POST['password']);
        $data = mysql_query("SELECT * FROM users WHERE username='$username' AND password='$password'");

    if(mysql_num_rows($data) >0) {   // if the query returns a result then create session variables to show user is authenticated
      $_SESSION['logged'] = 1;
      $_SESSION['user'] = $username;
    }
  }

Solution 1

You can use the BINARY type to force case sensitivity:

"SELECT * FROM users WHERE BINARY username='$username' AND password='$password'"

It should be noted that mysql_ functions are deprecated. You should switch to mysqli or PDO and use prepared statements.

Solution 2

First, you really should hash passwords. A change in capitalization will give you a different hash, so that sorts the issue.

If you want to compare user names in a case sensitive way, you probably also want to store them that way and be able to create different users for the logins 'foo' and 'Foo'. This is possible but you need to change the database table. More specific, you need to change the column type for the username column to include a character set and collation.

ALTER TABLE users MODIFY COLUMN username VARCHAR(64) CHARACTER SET 'latin1' COLLATION 'latin1_general_cs';

Solution 3

You need to set the Collation of your password field to a value which has a trailing "cs" not "ci" like

"latin1_swedish_cs" instead of "latin1_swedish_ci"

ci indicates case insensitive and cs is for case sensitive

Solution 4

Unless there is a reason not to, hash the password (I believe MD5 is still accepted although SHA-256 is preferred). Hashes produce a layer of security when passing traffic because decoding them needs to be brute forced. It also stores uppercase A and lowercase a as different hashes so it takes care of the case sensitivity issue.

If not, you need to use a case sensitive correlation on your password field.

View more solutions