CODEWITHSUNDEEP
phpdatabase
dux
dux

Reputation: 25

Case sensitive where clause in MySQL

So I have a basic login form for my website. But I've been testing the website today and I discovered that if you register as 'Andy' you can log in as 'Andy' but also as 'andy'. To fix this issue I tried selecting the username from the database and comparing it to the $_POST['username'] via ===. But with no avail so I removed that part from the code again. This is my basic login code can anyone help?

mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);

session_start();

$DATABASE_HOST = 'localhost';
$DATABSE_USER = 'root';
$DATABSE_PASS = '';
$DATABSE_NAME = 'test';

$_POST['username'] = htmlspecialchars($_POST['username']);
$_POST['password'] = htmlspecialchars($_POST['password']);


$con = mysqli_connect($DATABASE_HOST, $DATABSE_USER, $DATABSE_PASS, $DATABSE_NAME);

if (mysqli_connect_errno()) {
    exit('Failed to connect to database' . mysqli_connect_errno());
}

if ($stmt = $con->prepare('SELECT id, password, tokens, videos FROM accounts WHERE username = ?')) {
    $stmt->bind_param('s', $_POST['username']);
    $stmt->execute();
    $stmt->store_result();

    if ($stmt->num_rows > 0) {
        $stmt->bind_result($id, $password, $tokens, $videos);
        $stmt->fetch();
        if (password_verify($_POST['password'], $password)) {
            session_regenerate_id();
            $_SESSION['loggedin'] = TRUE;
            $_SESSION['name'] = $_POST['username'];
            $_SESSION['id'] = $id;
            $_SESSION['tokens'] = $tokens;
            $_SESSION['videos'] = $videos;
            header('Location: home.php');
        } else {
            echo 'Incorrect username or password';
        }
    } else {
        echo 'Incorrect username or password';
    }
    $stmt->close();
}

Upvotes: 1

Views: 148

Answers (1)

Ivanka Todorova
Ivanka Todorova

Reputation: 10229

Based on character set and collation, non binary string comparison is case insensitive. One way is to use BINARY to compare your input with column value or use COLLATE operator to change collation in one that will result in case sensitive search.

In your case, change your query to:

SELECT id, password, tokens, videos FROM accounts WHERE BINARY username = 'Andy'

Or use COLLATE like described in MySQL docs: https://dev.mysql.com/doc/refman/8.0/en/case-sensitivity.html

Upvotes: 3

Related Questions