password compare within mysql

Anon

I am doing a login query that looks something like this:

(user passes two variables, userName & password from a form):

mysql_query("SELECT userID FROM users WHERE userName = '$userName' AND password='$password'");

I am guessing this is not case sensitive. I would like it to be case sensitive (for the password at least), and would rather mysql did the work, rather then doing:

SELECT * FROM users WHERE userName = '$userName'

and then do a comparison on the passwords.

Can mysql do case-sensitive compares?

Anon

Indeed, from MySQL manual:

"All data are saved in ISO-8859-1 Latin1 format. All comparisons for normal string columns are case insensitive. "

Which is true for operators, but not true for the function STRCMP(), which works similiar to the 'C' strcmp.

From MySQL manual, under "String Comparison Functions":

SELECT * FROM users WHERE STRCMP(userName,$userName) = 0;

0 = match
-1 = first argument smaller than the second
1 = otherwise

Anon

Hi..
I can't help you, but i think that you can..
is that I'm doing a school's work and I need some scripts with PHP.
Can you give something.. can be about autentication on a site, or something like this.
oh, sorry if I'm writing something wrong, is that I'm brasilian (Do you know Brasil?)
And I don't know English very well!!

Thank you very much!!

Anon

Try these columns, here on phpbuilder, they might help?

Authentication:
http://www.phpbuilder.com/columns/tim20000505
.php3

Sessions with user login:

http://www.phpbuilder.com/columns/mattias20000312.php3

the session variables are easy to use.

Try darkseed.net for more small scripts.

Good luck

Anon

I can't seem to get it working.
How do I have to do the SQL query?
I tried it like this:

$query = "SELECT userid FROM users WHERE STRCMP(username,$login) = 0 AND STRCMP(password,$password) = 0";

But that only tells me the following:

Unknown column 'test' in 'where clause'

(test is the username)

Any help would be greatly apprechiated!

Anon

Try this:

Author: Proteus (217.0.61.144)
Date: 2001-03-04 12:21:56

I can't seem to get it working.
How do I have to do the SQL query?
I tried it like this:

$query = "SELECT userid FROM users WHERE STRCMP(username,'$login') = 0 AND STRCMP(password,'$password') = 0";

The single quotes are the key (around the variables

Anon

Thank you SO much!Now it works! :-)
(And it was soo simple...)

prophetize

I can't not get my password protection to work, could someone please help me out...

razvanfm

Don't use STRCMP to compare to strings. It is not key sensitive. So you can have Jack and jack, and strcmp will return 0. Use hex('string') = hex('string'). For password use password('password'), or use MD5('password'). But you must insert in the table the password using insert into table_name (user,password) values ('user',PASSWORD('password')) or use insert into table_name (user,password) values ('user',MD5('password'))