My database remembers capitalization...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

My database remembers capitalization...

Post 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. :(
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

The password returned would not be escaped.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

What version of mysql are you running?
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Post 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.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Post 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' 
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Post by Chalks »

dangit. I knew it would be something simple.

Thanks for the fast responses as usual. :)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post 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.
User avatar
Chalks
Forum Contributor
Posts: 447
Joined: Thu Jul 12, 2007 7:55 am
Location: Indiana

Post 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.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Post by Kieran Huggins »

phpBB stores both a username and a "clean" username - maybe that's a pointer in the right direction?
Post Reply