MYSQL Query

[Emicarn]

I'm able to write a new user to the database flawlessly.  It works and all data goes in.

When I'm trying to query for a log in routine it's not finding the user.

The query is written as:

Code: [Select]

$sqlfind = sprintf("select * from players where username = '$playername' and (password) = ('%s');", mysql_real_escape_string(md5('$playerpassword')));


It is returning no data found but the database clearly shows the user.  When I query the password as MD5 in phpMyAdmin it shows a matching string.

What am I overlooking here? 

I've tried the same query in phpMyAdmin and it returns zero rows.

[Sagefire135]

I THINK the problem is that you are trying to put a variable inside without using the %s thing. the query is literally looking for someone with the username $playername. do the same thing you did with the password and it should work.

Code: [Select]

$sqlfind = sprintf("select * from players where username = '%s' and password = '%s'", $playername, mysql_real_escape_string(md5('$playerpassword')));


[pixlepix]

I THINK the problem is that you are trying to put a variable inside without using the %s thing. the query is literally looking for someone with the username $playername. do the same thing you did with the password and it should work.

Code: [Select]

$sqlfind = sprintf("select * from players where username = '%s' and password = '%s'", $playername, mysql_real_escape_string(md5('$playerpassword')));


No, that isnt the problem. Or my whole game wouldnt work. Can we see the query putting the data into the players table?

[Nox]

@emicarn
1)
Not sure it's the reason, but you have swapped the target of escaping.

You do not need to escape password if you apply md5 to it first since afaik md5 contains only query-safe characters
But if the $playername is originated from user input, it needs to be escaped

2) MD5 is not considered safe method anymore, plus the most common case of rainbow tables, today it's usually some sha (sha1 is not ideal imho anymore too, I use the extreme variant sha512) + salt

----

Did you try to print the $sqlfind variable? Try echo $sqlfind and use it in phpMyAdmin, if it works, the problem is somewhere else (and we'd need to see more of the code)

[Emicarn]

@emicarn
 
Did you try to print the $sqlfind variable? Try echo $sqlfind and use it in phpMyAdmin, if it works, the problem is somewhere else (and we'd need to see more of the code)

Tried this and fed it into phpMyAdmin  - returned zero rows.

[Emicarn]

I THINK the problem is that you are trying to put a variable inside without using the %s thing. the query is literally looking for someone with the username $playername. do the same thing you did with the password and it should work.

Code: [Select]

$sqlfind = sprintf("select * from players where username = '%s' and password = '%s'", $playername, mysql_real_escape_string(md5('$playerpassword')));


No, that isnt the problem. Or my whole game wouldnt work. Can we see the query putting the data into the players table?

The query to add users:

Code: [Select]


$adduser = sprintf("INSERT INTO players (username, password, email, shipname, faction, pos_x, pos_y, class, beta_tester, admin, confirmed, vessel_val, points, v_points, c_hull, c_shield, c_engine, c_battery, c_aux_power, c_sensors, c_beam_weapon, c_torpedo_weapon, c_dc, c_transporters, c_crew, c_marines, c_torpedos, m_hull, m_shield, m_engine, m_battery, m_aux_power, m_sensors, m_beam_weapon, m_torpedo_weapon, m_dc, m_transporters, m_crew, m_marines, m_torpedos, join_date, join_time, login_date, login_time) values ('%s', '%s', '%s', '%s', '%s', '$posX', '$posY', 'Gun Boat', '$isbeta_tester', '$isadmin', '$isconfirmed', '47', '0', '0', '10', '10', '10', '5', '5', '1', '2', '1', '5', '1', '10', '5', '10','10', '10', '10', '5', '5', '1', '2', '1', '5', '1', '10', '5', '10', '$date', '$time', '$date', '$time');", mysql_real_escape_string($playername), mysql_real_escape_string(md5($playerpassword)), mysql_real_escape_string($playeremail), mysql_real_escape_string($playershipname), mysql_real_escape_string($playerfaction));



[JGadrow]

The issue here is that you have single quotes around the $playerpassword variable inside of your md5() call. Thus, you're ALWAYS getting the md5 hash of the string '$playerpassword' instead of the user-entered password that you're expecting.

Try the following instead (minor re-write):

Code: (php) [Select]

$queryPattern = "SELECT * FROM players WHERE username = '%s' AND password = '%s';";
$sqlfind = sprintf($queryPattern, mysql_real_escape_string($playername), md5($playerpassword));
As a note, you can also use && in place of AND in the query. I prefer using && to keep it consistent with other programming languages' logical evaluation operators. But that's just personal preference.

*Edit - Wrote query inside of a variable to prevent code scrolling in my forum theme.

[Emicarn]

The issue here is that you have single quotes around the $playerpassword variable inside of your md5() call. Thus, you're ALWAYS getting the md5 hash of the string '$playerpassword' instead of the user-entered password that you're expecting.

Try the following instead (minor re-write):

Code: (php) [Select]

$queryPattern = "SELECT * FROM players WHERE username = '%s' AND password = '%s';";
$sqlfind = sprintf($queryPattern, mysql_real_escape_string($playername), md5($playerpassword));
As a note, you can also use && in place of AND in the query. I prefer using && to keep it consistent with other programming languages' logical evaluation operators. But that's just personal preference.

*Edit - Wrote query inside of a variable to prevent code scrolling in my forum theme.

Good point on the &&. 

I dumped this in and tried it.  No rows returned.  It returned an echo string the exact same as the one I tried earlier from the original code.

Even cleared the players table and readded a new user.  Same result thinking maybe I forgot the test password [I'm old!].   

[Emicarn]

Quick Side Note:

I took out the md5 hashing.

It now works and returns a user.  The login works and can log in and out as different test users.

[JGadrow]

Did you remove the hashing only for the select query? Or for the insert as well? Perhaps you'd already created an md5 hash of the password earlier in your code?

Essentially, were you hashing an already hashed password (this will generate a different hash)?

[Emicarn]

Did you remove the hashing only for the select query? Or for the insert as well? Perhaps you'd already created an md5 hash of the password earlier in your code?

Essentially, were you hashing an already hashed password (this will generate a different hash)?

I removed on the insert and then on the log in.

That way there is a plain text password in the DB.   

when I add back the md5 hash, and created a new user, it failed.

[Nox]

Did you try to echo and compare the md5's?

Do you have sufficient column length? MD5 needs 32chars, if the limit is shorter, it gets cropped and therefore do not match

[Emicarn]

Did you try to echo and compare the md5's?

Do you have sufficient column length? MD5 needs 32chars, if the limit is shorter, it gets cropped and therefore do not match

This might be the problem.  They seem to match when I echo and compare.

I have the password field set to varchar(25)  Let me adjust that to a bigger field and see what happens.

This didn't cross my mind.........

[JGadrow]

I have the password field set to varchar(25)  Let me adjust that to a bigger field and see what happens.

Ah, there's the culprit! As a tip, you can declare this column as char(32) instead of varchar(32) since an md5 hash is always 32 characters long. Not much of an optimization, but it does save 1 byte or 2 per player since it's no longer necessary to store the length of the string.

[Chris]

I have the password field set to varchar(25)  Let me adjust that to a bigger field and see what happens.

Ah, there's the culprit! As a tip, you can declare this column as char(32) instead of varchar(32) since an md5 hash is always 32 characters long. Not much of an optimization, but it does save 1 byte or 2 per player since it's no longer necessary to store the length of the string.

It might be a big optimization if MyISAM + all other fields in the table are already fixed size (no varchar, text, blob), since fining a new row would be a simple multiplication of size*rownumber for MySQL engine.