I have a problem matching strings using the BINARY keyword.
I have fields in a table 'auth' defined as
username CHAR(25) BINARY
passwd CHAR(25) BINARY
So obviously all string comparisons are case sensitive. However, I
noticed that trailing spaces in a string cause problems:
select 'user'='user '; will return 1
Leading spaces do not cause the same problem:
select 'user'=' user'; will return 0
If, for example, a valid username is 'user', then
select passwd from auth where username='user'
will work. However, if some idiot decides to insert trailing spaces after
his username, the following will also match to 'user':
select passwd from auth where username='user '
This doesn't bother me by itself, but this behaviour doesn't seem to be
consistant across INSERT, SELECT and UPDATE.
Assuming table 'blah' also contains a username field of type char(25)
BINARY, If I run:
insert into blah values ('user ', ... )
then
select ... from blah where username='user '
this does not return a match!
This is causing me problems, as then it will try to INSERT a new record
instead of UPDATE and I get a DUPLICATE KEY error!
This will work if I use strcmp, but it will be much slower. Does anyone
know why it does this?
Richard
