Login Script - Query then Auth or just Query the Auth

Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.

Moderator: General Moderators

Post Reply
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Login Script - Query then Auth or just Query the Auth

Post by tecktalkcm0391 »

I just got thinking, which is better/more secure for a login:

1. To do a query where username = username and inputed enctyped password = database password, or

2. Get the row where username = username, then check the passwords against each other...

I've always done the 2nd because that is the way I learned, any comments on which one is better?
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Login Script - Query then Auth or just Query the Auth

Post by alex.barylski »

I find the record first based on username/email then compare the record password field using SHA256.
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Re: Login Script - Query then Auth or just Query the Auth

Post by tecktalkcm0391 »

Hockey wrote:I find the record first based on username/email then compare the record password field using SHA256.
Is your preference for any particular reason?
alex.barylski
DevNet Evangelist
Posts: 6267
Joined: Tue Dec 21, 2004 5:00 pm
Location: Winnipeg

Re: Login Script - Query then Auth or just Query the Auth

Post by alex.barylski »

None really. I try and keep most everything out of SQL, it's easier to change in PHP and more clear IMHO.

I suppose it has performance and security implications as well. I would guess that a query which relied on a single field as opposed to two would execute faster, although authentication only occurs once, so that point is moot.

I suppose it's more secure in nature due to only having to secure one field instead of two, but as long as the password is hashed in the database using something better than MD5, it doesn't matter where you SQL queries have weak spots, so again moot. What I'm trying to say, is SQLi won't yield usable passwords if their are properly hashed but there are other problems, which I am sure you are aware of.

Mostly code clarity. I prefer to keep the password check in PHP code. I recently switched to SHA256 for hashing passwords instead of MD5 and I wasn't sure (nor did I care) if MySQL supported that or not.
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Login Script - Query then Auth or just Query the Auth

Post by Kieran Huggins »

I'm the opposite, I usually do the check in SQL:

Code: Select all

SELECT id FROM users WHERE email='$email' AND password=PASSWORD($password);
then check the num_rows() returned by the query.
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Login Script - Query then Auth or just Query the Auth

Post by John Cartwright »

Kieran Huggins wrote:I'm the opposite, I usually do the check in SQL:

Code: Select all

SELECT id FROM users WHERE email='$email' AND password=PASSWORD($password);
then check the num_rows() returned by the query.
Kieran++, except I use SHA256
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Login Script - Query then Auth or just Query the Auth

Post by Kieran Huggins »

@Jcart: do you make the hash in php or mysql?

I'd be inclined to install it on the SQL side, but I'm curious a) why you prefer sha256, and b) why you prefer hashing in PHP (if you do)

MySQL sha256() support:
http://www.kfwebs.net/articles/article/ ... t-in-MySQL
movedx
Forum Newbie
Posts: 7
Joined: Wed Jun 18, 2008 6:59 am

Re: Login Script - Query then Auth or just Query the Auth

Post by movedx »

Kieran Huggins wrote:@Jcart: do you make the hash in php or mysql?
why you prefer hashing in PHP (if you do)
I'd hash at the PHP side to prevent sniffering between the PHP <-> SQL-Server link... perhaps he's using an SQL server on a remote host? That's the only reason I can think of as to why I'd hash/encrypt the data :)

Then again, you could process that data through an SSL tunnel *shrug*
User avatar
John Cartwright
Site Admin
Posts: 11470
Joined: Tue Dec 23, 2003 2:10 am
Location: Toronto
Contact:

Re: Login Script - Query then Auth or just Query the Auth

Post by John Cartwright »

Kieran Huggins wrote:@Jcart: do you make the hash in php or mysql?

I'd be inclined to install it on the SQL side, but I'm curious a) why you prefer sha256, and b) why you prefer hashing in PHP (if you do)

MySQL sha256() support:
http://www.kfwebs.net/articles/article/ ... t-in-MySQL
I will always opt to push everything as possible to SQL, however if relies on an extension or compiling conditions I will put it in PHP (which is what I do). The reason I'm using Sha256 is because from the limited research I did way back when, PASSWORD() uses Sha1 x2, obviously a weaker hash than Sha256. There is also the issue of backwards compatability, since there was a change in PASSWORD() in 4.1, I believe, when they changed the hash algorithm (even though you can use --oldpassword when compiling to fix this issue).
User avatar
tecktalkcm0391
DevNet Resident
Posts: 1030
Joined: Fri May 26, 2006 9:25 am
Location: Florida

Re: Login Script - Query then Auth or just Query the Auth

Post by tecktalkcm0391 »

to make a connection to mysql over SSL do you just need to ad 'MYSQL_CLIENT_SSL' to the client_flags section of mysql_connect?
User avatar
Kieran Huggins
DevNet Master
Posts: 3635
Joined: Wed Dec 06, 2006 4:14 pm
Location: Toronto, Canada
Contact:

Re: Login Script - Query then Auth or just Query the Auth

Post by Kieran Huggins »

Jcart wrote:I will always opt to push everything as possible to SQL, however if relies on an extension or compiling conditions I will put it in PHP (which is what I do). The reason I'm using Sha256 is because from the limited research I did way back when, PASSWORD() uses Sha1 x2, obviously a weaker hash than Sha256. There is also the issue of backwards compatability, since there was a change in PASSWORD() in 4.1, I believe, when they changed the hash algorithm (even though you can use --oldpassword when compiling to fix this issue).
You make good sense, sir.
Post Reply