Mysql - Capital letters in strings

Question

In my database I have a Varchar entry: zZz

Note the capital.

I am letting the use input their username and checking with the entry. But I am trying to make it go wrong by incorrecly writing the username zzz - note all lowercase.

The Mysql is saying everything is ok and it is returning true, when I am expecting it to return false. What am I doing wrong. Here is my function ($db is a valid database handle):

function IsUsername($db, $Username)
{
    $stmtIsUsername = $db->prepare("SELECT Username FROM members WHERE "
              . "Username = :Username");
    $stmtIsUsername->execute(array(':Username' => $Username));
    $ret = ($stmtIsUsername && isset($stmtIsUsername) && $stmtIsUsername->rowCount() > 0) ? true : false;
    $stmtIsUsername->closeCursor(); // mysql_free_result equivalent
    return $ret;
}

In my database Username = zZz

I am calling the following ($db is valid)

$userInput = $_GET['username']; // Which is "zzz"
if(IsUsername($db, $userInput))
{
    echo "All is OK";
} else
{
    echo "The user is not valid";
}

The echo is annoyingly "All is OK". What am I doing wrong?

Duplicate: http://stackoverflow.com/questions/3936967/mysql-case-insensitive-select — David Fleeman, Nov 26 '13 at 19:47
Its not a duplicate. I want zZz and zzz to be different. Your link wants the two to be the same. — Rewind, Nov 26 '13 at 20:02

score 2 · Accepted Answer · answered Nov 26 '13 at 19:50

2

The default character set and collation are latin1 and latin1_swedish_ci, so nonbinary string comparisons are case insensitive by default. This means that if you search with col_name LIKE 'a%', you get all column values that start with A or a. To make this search case sensitive, make sure that one of the operands has a case sensitive or binary collation. For example, if you are comparing a column and a string that both have the latin1 character set, you can use the COLLATE operator to cause either operand to have the latin1_general_cs or latin1_bin collation:

col_name COLLATE latin1_general_cs LIKE 'a%' col_name LIKE 'a%' COLLATE latin1_general_cs col_name COLLATE latin1_bin LIKE 'a%' col_name LIKE 'a%' COLLATE latin1_bin If you want a column always to be treated in case-sensitive fashion, declare it with a case sensitive or binary collation. See Section 13.1.10, “CREATE TABLE Syntax”.

[http://dev.mysql.com/doc/refman/5.0/en/case-sensitivity.html]

answered Nov 26 '13 at 19:50

viljun

370
3
12

This sounds like the answer, but I am a hobby programmer and have not understood a word. What do I do in my code? Just seen the link. Reading it now – Rewind Nov 26 '13 at 19:55
So I have read the link, and can I check its my mysql code that has to change to: $stmtIsUsername = $db->prepare("SELECT Username FROM members WHERE Username COLLATE Latin1_General_CS_AS = :Username"); Have I read it correctly. Also does this make the search slower? – Rewind Nov 26 '13 at 20:09
Reread the link - its the table you are telling me to change. It works now. Thanks. – Rewind Nov 26 '13 at 20:29
Sorry... I suppose you use nonbinary strings. I mean the string is type VARCHAR or TEXT. You should write something like this: SELECT Username FROM members WHERE Username COLLATE latin1_general_cs LIKE 'a%' ...to search usernames starting with a-letter. Cs means case sensitive. Latin1 is the character set of the column. You'll get the right character set writing 'show create table members;' – viljun Nov 26 '13 at 20:29

Mysql - Capital letters in strings

1 Answers1