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;
    }
  }

There is **no more support** for `mysql_*` functions, they are [**officially deprecated**](https://wiki.php.net/rfc/mysql_deprecation), **no longer maintained** and will be [**removed**](http://php.net/manual/en/function.mysql-connect.php#warning) in the future. You should update your code with [PDO](http://php.net/pdo) or [MySQLi](http://php.net/msqli) to ensure the functionality of your project in the future. — Arjan, May 24 '13 at 05:10

score 6 · Answer 1 · answered May 24 '13 at 02:50

6

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.

answered May 24 '13 at 02:50

Kai Qing

18,359
5
34
56

1

Actually, that query is only making the `username` field case sensitive whilst the password field is not. You'd need to use `SELECT * FROM users WHERE BINARY username='$username' AND BINARY password='$password'` – Acidic9 Jun 04 '17 at 07:39
1

Also, you shouldn't be storing passwords as plain text. After using md5 or some encryption method, case sensitivity shouldn't be a problem as most encryption methods will be all lower case – Acidic9 Jun 04 '17 at 07:40

score 2 · Answer 2 · answered May 24 '13 at 05:38

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';

score 2 · Answer 3 · answered May 24 '13 at 06:00

2

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

answered May 24 '13 at 06:00

techie_28

2,025
4
37
56

Thanks for that. very helpful indeed – Charles May 28 '13 at 10:19
this can clear some facts too http://stackoverflow.com/questions/11716420/if-mysql-is-case-insensitive-how-does-it-works-for-the-password-field-in-login – techie_28 May 29 '13 at 05:19

score 1 · Answer 4 · answered May 24 '13 at 02:50

1

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.

answered May 24 '13 at 02:50

Pirion

529
2
6

Thanks for the explanation. Was thinking there was a way around enycription. Thanks – Charles May 24 '13 at 02:54
2

@Pirion - Hashing the password is the way to go, but it is not a good idea to use MD5 or a function of the SHA-* family, because they are ways too fast. Instead use a slow key-derivation function like BCrypt or PBKDF2. Have a look at the new PHP function [password_hash()](http://de3.php.net/manual/de/function.password-hash.php). – martinstoeckli May 24 '13 at 06:44
@Barmar - I cannot think of a case, where user "ADMIN" should be a different user than user "admin". Case sensitivity for passwords makes sense, but for usernames? – martinstoeckli May 24 '13 at 06:49
I agree, but that seems to be what the OP wants. – Barmar May 24 '13 at 06:54

score 0 · Answer 5 · answered May 24 '13 at 03:43

Compare the username and password that are returned by the query using PHP, which is case-sensitive.

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

score 0 · Answer 6 · answered Apr 25 '14 at 03:51

0

Just change collection of username and password filed to latin1_general_cs in you database.

answered Apr 25 '14 at 03:51

Atif Mahmood

8,146
2
39
43

score 0 · Answer 7 · answered Jul 23 '16 at 06:26

<?php
// user input

$username="abc";
$password="123";

$sql=SELECT username,password FROM tablename WHERE username='".$username."' AND password ='".$password."' limit 1
$result=mysqli_query($coni,$sql);
if(!mysqli_num_rows($result) == 1)
{
    echo "not found";
}
else
{
    while ($row=mysqli_fetch_array($result)) 
    { 
        $getu=$row[0];
        $getp=$row[1];
    }
    if($username===$getu && $password===$getp)
    {
        echo "username".$getu." password".$getp;
        echo "Login success";
    }
    else
    {
        echo "no";
    }
}
?>

Sauer Voussoir · Answer 8 · 2017-08-22T15:45:07.710

This program determine Username or Email that are exists is not validated. This code is in JSON format server response. To able to compare case sensitive by using WHERE BINARY in the statement.

<?php
require "main.php";
$response = array();
$num = 0;

if($_SERVER['REQUEST_METHOD']=='POST'){
if(isset($_POST['username']) and
    isset($_POST['email']) and
        isset($_POST['password'])){

            $username= $_POST["username"];
            $password= $_POST["password"];
            $email= $_POST["email"];


            $mysql_qry = "select * from users where binary username like '$username' and binary email like '$email'";
            $result = mysqli_query($conn ,$mysql_qry);

            if(mysqli_num_rows($result) > 0 ){
                //User already exists, choose different username or email!
                $response['error'] = true;
                $response['message'] = "Username and Email is already exists!";
                $num = 1;
            }

            $mysql_qry = "select * from users where binary username like '$username'";
            $result = mysqli_query($conn ,$mysql_qry);

            if(mysqli_num_rows($result) > 0 && $num == 0){
                //User already exists, choose different username or email!
                $response['error'] = true;
                $response['message'] = "Username is already exists!";
                $num = 1;
            }

            $mysql_qry = "select * from users where binary email like '$email';";
            $result = mysqli_query($conn ,$mysql_qry);

            if(mysqli_num_rows($result) > 0 && $num == 0){
                //User already exists, choose different username or email!
                $response['error'] = true;
                $response['message'] = "Email is already exists!";
                $num = 1;
            }


            if($num == 0){
                $mysql_qry = "insert into users (username, password, email) values ('$username', '$password', '$email')";

                if($conn->query($mysql_qry) === TRUE ){
                    //Registration Successful!
                    $response['error'] = true;
                    $response['message'] = "Registration Successful!";
                }else{
                    //Registration Failed!
                    $response['error'] = true;
                    $response['message'] = "Registration Failed!";
                }
            }   
}else{
    //Require fields are missing!
    echo "missing";
}   
}else{
//Invalid Request!
echo "invalid";
}

echo json_encode($response);
?>

score 0 · Answer 9 · answered Jul 25 '18 at 19:04

0

With PHP's password_hash() function, the case sensitivity issue shouldn't come up as it takes care of that.

answered Jul 25 '18 at 19:04

Rotimi

4,494
4
16
27

Case sensitive php mysql login

9 Answers9