Page 1 of 1

My database remembers capitalization...

Posted: Sat Nov 17, 2007 7:05 pm
by Chalks
And I don't want it to!

Code: Select all

$user = mysql_real_escape_string($user);
  $user = strtolower($user);
  $pass = mysql_real_escape_string($pass);

//make sure user exists
  $sql = "SELECT * FROM auther WHERE name = '" . $user . "'";
  $data = mysql_fetch_array(mysql_query($sql));
  if(isset($data['password']) && $data['password']==$pass)
    echo "GOOD USER";

//propagate initial table
//  $sql = "INSERT INTO auther (name, password, permissions) VALUES('$user', '$pass', 'full')";  
//  mysql_query($sql);
The commented out portion at the bottom was how I put the initial table data in.

My problem is that I want it to complete ignore capitalization of the username. So if user types "USERNaMe", that should be the same thing as "usernAME". Here are the $sql values for the following usernames (I just used "echo $sql" to get this):

barbARA:
INSERT INTO auther (name, password, permissions) VALUES('barbara', 'thepasswordhash', 'full')

then, I commented out the insert query, and un commented the select query, and got these results:

barbARA:
SELECT * FROM auther WHERE name = 'barbara'GOOD USER

barbara:
SELECT * FROM auther WHERE name = 'barbara'



I can not for the life of me figure out what the difference between those two select statements are, yet one of them gets the result I want, and the other doesn't. Could someone _please_ tell me what I'm missing? Thanks!




Edit: I know for a fact that I was typing the correct password in every single time. I also know for a fact that everything is spelled right... except for the capitalization, which shouldn't be creating this problem. :(

Posted: Sat Nov 17, 2007 8:28 pm
by feyd
The password returned would not be escaped.

Posted: Sat Nov 17, 2007 8:51 pm
by RobertGonzalez
What version of mysql are you running?

Posted: Sun Nov 18, 2007 7:44 pm
by Chalks
feyd wrote:The password returned would not be escaped.
but if I put the password into the database escaped, wouldn't it come out escaped too? Also, if an escaped string only contains numbers and letters to begin with, I thought that function didn't do anything to it?
Everah wrote:What version of mysql are you running?
phpMyAdmin says: "MySQL client version: 4.1.22"



This problem isn't really a huge deal, since it does work if you match capitalization... it's just annoying.

Posted: Sun Nov 18, 2007 7:48 pm
by John Cartwright
Mysql does not store the information escaped.. since you are comparing an escaped version with a non-escaped version it won't work.

Typically one would check against the user/pass combo entirely in the query

Code: Select all

... WHERE user = '$user' AND pass = '$pass' 

Posted: Sun Nov 18, 2007 7:51 pm
by Chalks
dangit. I knew it would be something simple.

Thanks for the fast responses as usual. :)

Posted: Mon Nov 19, 2007 9:30 am
by RobertGonzalez
This was actually discussed a few weeks ago. Escaping is preparing the data for output, even if the data is being output to a table.

Imagine doing something like this:

Code: Select all

<?php
echo 'I don\'t want to see a slash';
?>
You wouldn't expect to see the slash before the second single quote would you? No, because it is escaped prior to output. Same for database data. It is storing the data, not the escaped data. Escaping it makes it acceptable to the database.

Posted: Mon Nov 19, 2007 5:02 pm
by Chalks
OK... wow, I'm an idiot. I spent a good 40 minutes trying to fix my real escaped stuff, and it is, now. Which is great. But that wasn't the problem. My problem that before I hash my password, I concatenate it with the user name... BEFORE I set the username to lowercase. This, of course didn't hash the same way:

hash of:
password + UsErNaMe

!=

hash of:
password + username



Why I didn't just print my password hash when I first encountered the problem... I don't know. Sheesh.

Posted: Mon Nov 19, 2007 5:12 pm
by Kieran Huggins
phpBB stores both a username and a "clean" username - maybe that's a pointer in the right direction?