Question 21892 in Mysql Question posted in
The official MySQL documentation can be found here.

Make Username and Password Input Case Sensitive – Mysql

Hi I would like to add here a case sensitive error trap on my login function, by the way i am using MVC FRAMEWORK anyone be of help ? I want to make the username and password case sensitive so that is the input doesn’t match an error exception will occur…………… I have tried but failed maybe someone can assist me on hot to go about this dilemma

//THIS IS THE CODE OF MY CONTROLLER


public function login() {
        if(isLoggedIn()) {
            header("Location: " .URLROOT . "/");
        }
        $data = [
            'title' => 'Login page',
            'username' => '',
            'password' => '',
            'usernameError' => '',
            'passwordError' => ''
        ];

    

        //Check for post
        if($_SERVER['REQUEST_METHOD'] == 'POST'){
            //Sanitize post data
            $_POST = filter_input_array(INPUT_POST);

            $data = [
                'username' => trim($_POST['username']),
                'password' => trim($_POST['password']),
                'usernameError' => '',
                'passwordError' => '',
            ];
            $findUser = $this->userModel->findUser($data);


            //Validate username
            if(empty($data['username'])){
                $data['usernameError'] = 'Please enter a username.';
            }else if($findUser === false){
                $data['usernameError'] = "Username not registered";
            }

            //Validate username
            if(empty($data['password'])){
                $data['passwordError'] = 'Please enter a password.';
            }else if($findUser === false){
                $data['passwordError'] = "Password not registered";
            }
           

            $findUser = $this->userModel->getUserDetails($data);



            //Check if all errors are empty
            if(empty($data['usernameError']) && empty($data['passwordError'])){
                $loggedInUser = $this->userModel->login($data['username'], $data['password']);

                if($loggedInUser){
                    $this->createUserSession($loggedInUser);
                }else {
                    $data['passwordError'] = 'Password is incorrect. Please try again.';

                    $this->view('users/login',$data);
                }


            }
            
        }else{
            $data = [
                'username' => '',
                'password' => '',
                'usernameError' => '',
                'passwordError' => ''
            ];
        }



//THIS IS THE CODE OF MY MODEL



public function login($username, $password) {
        $this->db->query('SELECT * FROM user WHERE username = :username');

        //Bind value
        $this->db->bind(':username', $username);

        $row = $this->db->single();

        $hashedPassword = !empty($row) ? $row->password:'';

        if(password_verify($password, $hashedPassword)){
            return $row;
        }else {
            return false;
        }
    }


        $this->view('users/login', $data);
    }

Case sensitive error trap

Tags:

2

Answers


  1. 0

    If you need to make a case-sensitive query, it is very easy to do using the BINARY operator, which forces a byte by byte comparison:

    SELECT * FROM `table` WHERE BINARY `column` = 'value'
    Login or Signup to reply.
  2. 0

    The password is already case-sensitive, since it’s using the native password_hash and password_verify functions, it can be easily tested with:

    var_dump(password_verify('AAA', password_hash('AAA', PASSWORD_DEFAULT))); // true
var_dump(password_verify('AAA', password_hash('aaa', PASSWORD_DEFAULT))); // false

    If you really want to have the username case-sensitive, you can also use a case-sensitive collation for the username field, such as utf8mb4_0900_as_cs, more info here.

    ALTER TABLE `users` CHANGE COLUMN `username` `username` VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_as_cs' NOT NULL;

    Test case:

    INSERT INTO `users` (`username`) VALUES ('test');

SELECT * FROM `users` WHERE `username`='TEST'; /* returns nothing as expected */
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search