[Resolved] MySQL upgrade SELECT problems

[4rxsid]

Hi all,

previous environment:

mysql 4.0.18 (max debug)
php 4.3.1
windowsNT server
webserver - IIS

new environment:

mysql 4.1.9-nt-log
php 4.3.1
windows2003 server
webserver - Apache 2.0.52

I have a varchar(40) password field that I used to store sha() encrypted passwords in. In the previous environment, I could use :

$password = sha1($password);

$checkUser = mysql_query ("SELECT * FROM users WHERE User = '$userid' AND PASSWORD = '$password'",$web_connection) or exit (mysql_error());

and it worked just fine.
In the new environment...it doesn't work & no mysql_error is output.

The only signigicant difference I can see is with the newer db version and using Collation. I changed my table from the default latin1_swedish_ci to default-character-set=utf8 in the my.ini file for future table addition. I also change the current user table (to which my query goes against) to utf8_bin (I've tried a few others as well).
Do you think that the Collation column is my problem? The php code didn't change a bit.

Thanks

[4rxsid]

also
if you think this has to do with the password hash issue and
SET PASSWORD FOR 'some_user'@'some_host' = OLD_PASSWORD('newpwd');

thats not the issue. I did that when I setup the database. I can prove also that it's not the case when I take out the where password condition:

$checkUser = mysql_query ("SELECT * FROM users WHERE User = '$userid'",$web_connection) or exit (mysql_error()); 

So it's not the new password hash.

I'm guessing it's got to do with the Collation field, but I'm not sure nor how to resolve it if it is the issue.

[Roger_Ramjet]

Don't see why you were ever using a varchar column to store an sha1 hash as: "The hash is a 40-character hexadecimal number." ie fixed size. Now it may be that your characterset changes are messing things up by interpretting the hex as utf8 unicode. "The idea of the UTF8 character set is that various Unicode characters fit into byte sequences of different lengths:

*

  Basic Latin letters, digits, and punctuation signs use one byte.
*

  Most European and Middle East script letters fit into a two-byte sequence: extended Latin letters (with tilde, macron, acute, grave and other accents), Cyrillic, Greek, Armenian, Hebrew, Arabic, Syriac, and others. "

My fix would be to store the sha1 hash in a binary column of fixed length. I'll bet the problem goes away.

[4rxsid]

thanks for the reply Roger...but again, it's not the password field that's the problem. I can run the query just selecting the User field and that too doesn't work. No errors no nothing.

it fails the if :

    if ($getUser = mysql_fetch_array($checkUser)) {
        //doesn't go here.
    } else {
       //goes here.
    }

also...it works no problem from phpMyAdmin 2.6.1. same exact query returns the appropriate result.

any ideas?

[Roger_Ramjet]

Are you hashing the hash? My expectation would be that the user input page does a sha1 hash before posting the user's password, otherwide why bother. If your script is then as it appears from your post you have a double hash and no match.

[4rxsid]

the information in the users table was not entered via a form. I entered them (only 12 users) myself via phpmyadmin.

as mentioned before...this used to work (in the previous environment listed in my first post) till i upgraded to a new server and a new version of MySQL. I did a simple import of the data from the old NT server and previous version of MySQL.

so the only thing that has changed here is the MySQL version and the OS. The PHP code has remained unchanged.

to illustrate this even more, that it's not the password column that's the problem, i deleted the password column from the table and it still doesn't work. I'm back to the assumption that this has to do with MySQL's "Collation".

[4rxsid]

ok...finally got it to work. i had to set the columns to:

latin1_swedish_ci