[Resolved] Checking Usernames Against Passwords with MySQL

shatteredroses

I'm very new to PHP and MySQL, I've read one of these books for absolute beginners and I'm currently trying to code a character database for a chat-based game.
So if you hear from me a lot, that's why.

Now, I have created the database Test in MySQL, this contains the table login. The table has four fields:
id - int(11) - PRI
username - varchar(20)
password - varchar(10)
st - enum('TRUE', 'FALSE')
Where ST is a special kind of user with administrative privaliges.

I've then added two records to the database:
- id=0 , username=storyteller , password=st , st=TRUE
- id=1 , username=player , password=player , st=FALSE

Then I made a HTML form for the user to enter their username and password, and a PHP form which will then check that username and password against the records stored on the database.
It's this PHP form that I'm having trouble with as the book I learnt from gave me little to no information on retrieving singluar variables from a database without printing the whole lot out.
So far, this is what I have:

<HTML>
	<HEAD>
		<TITLE>Test Login</TITLE>
	</HEAD>
	<BODY>

<?

//pulls the username and password from the previous form
$user = $_REQUEST["user"];
$pass = $_REQUEST["pass"];

//makes the connection to the database
$conn = mysql_connect("localhost", "hollowd_roses", "*******");
mysql_select_db("hollowd_Test", $conn);

//creates a username query
$sql = "SELECT * FROM Login where $user == username";
$result = mysql_query($sql);

//HERE IS WHERE I'M STUCK, AS I'M NOT SURE HOW TO PULL THE PASSWORD RELATING TO THAT SPECIFIC USER FROM THE DATABASE

//checks password against database
if ($pass == $password){
	PRINT "You are Logged In";
	} else {
	PRINT "Password not recognised";
} // end if

?>

</BODY>
</HTML>

I've highlighted the area I'm struggling with, with that big ass comment in caps.
Currenly if I run it as it is, I get 'Password not recognised' no matter what I type in, that's probably because I have no code in there to pull the password variable from the database 😃
If you need any more info from me then I'm happy to explain as far as I can.
Thanks in advance for your help.

laserlight

Try something along the lines of:

SELECT * FROM login WHERE username='$user' AND password='$pass'

shatteredroses

I changed the SELECT function as you suggested, but was still getting 'Password not recognised'
I then changed the } else { PRINT "Password not recognised"; to } else { die(mysql_error());

So the central part of the code now reads:

//creates a username query
$sql = "SELECT * FROM 'login' WHERE 'username'='$user' AND 'password'='$pass'";
$result = mysql_query($sql);

//checks password against database
if ($pass == $password){
	PRINT "You are Logged In";
	} else {
	die(mysql_error());
} // end if

I am now getting the following error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near ''login' WHERE 'username'='storyteller' AND 'password'='st'' at

That's it . . . It doesn't say where it's at or anything, but it probably is talking to the database because the error message contains the login data.
Unless it's taken it from the HTML form that is.
Either way I'm still stuck . . .

gufmn

Look at laserlights query again. Those are not single quotes around the collum names, they're back ticks (`). I personally have never used these but I'm hoping someone will tell us the advantage to doing so 😉.

I would normally just do it like:

$sql = "SELECT * FROM login WHERE username ='$user' AND password ='$pass'";

laserlight

I'm hoping someone will tell us the advantage to doing so

Consider what's written in the MySQL Manual:
http://dev.mysql.com/doc/mysql/en/Legal_names.html

An identifier may be quoted or unquoted. If an identifier is a reserved word or contains special characters, you must quote it whenever you refer to it.

The identifier quote character is the backtick (``'):

In this case, the "password" identifier may come into conflict with the in-built PASSWORD() function, so it is better to escape it just in case.

shatteredroses

Thanks again for all your help on this.
Currently, my PHP looks like this:

<?

//pulls the username and password from the previous form
$user = $_REQUEST["user"];
$pass = $_REQUEST["pass"];

//makes the connection to the database
$conn = mysql_connect("localhost", "hollowd_roses", "*******");
mysql_select_db("hollowd_Test", $conn);

//creates a username query
$sql = "SELECT * FROM `login` WHERE `username`='$user' AND `password`='$pass'";
$result = mysql_query($sql);

//checks password against database
if ($pass == $password){
	PRINT "You are Logged In";
	} else {
	die(mysql_error());
} // end if

?>

Now when I run that I get an entirely blank page. Nothing on it at all.
The source code for the resulting page looks like this:

<HTML>
	<HEAD>
		<TITLE>Test Login</TITLE>
	</HEAD>
	<BODY>

EDIT - I've found an ErrorLog relating to the program, so hopefully this should help all you clever people who know what things like this mean

This is the error I'm getting:

[01-Oct-2004 13:43:10] PHP Warning: Unknown(): Unable to load dynamic library './ixed.lin.4.3.9.pxp' - ./ixed.lin.4.3.9.pxp: cannot open shared object file: No such file or directory in Unknown on line 0

SpanKie

You will need to get the results from the query next to be able to do anything with it.

Try this ....

<?

//pulls the username and password from the previous form
$user = $_REQUEST["user"];
$pass = $_REQUEST["pass"];

//makes the connection to the database
$conn = mysql_connect("localhost", "hollowd_roses", "bronzedoor78");
mysql_select_db("hollowd_Test", $conn);

//creates a username query
$verify = mysql_query("SELECT username,password FROM `login` WHERE `username`='$user' AND `password`='$pass'");
$password = mysql_result($verify, 2);

//checks password against database
if ($pass == $password){
    PRINT "You are Logged In";
    } else {
	PRINT "Not Logged In";
} // end if

?>

SpanKie

Forgot to include ... you may want to wrap a conditional statement around the mysql_query code, this way if there are no results, it won't error:

//creates a username query
$verify = mysql_query("SELECT username,password FROM `login` WHERE `username`='$user' AND `password`='$pass'");
if($verify) {
$password = mysql_result($verify, 2);
} else {
  PRINT "NO SUCH USER";
}

shatteredroses

Another post, another error . . .
I've changed the coding to what you suggested, and now this is the error I'm getting

Warning: mysql_result(): Unable to jump to row 2 on MySQL result index 2 in /home/hollowd/public_html/tests/check.php on line 19

And I thought this was a stupid question, easily solved . . .

SpanKie

hmmm.... change to

$password = mysql_fetch_row($verify, 0, 2);

gufmn

Originally posted by laserlight
Consider what's written in the MySQL Manual:
http://dev.mysql.com/doc/mysql/en/Legal_names.html

In this case, the "password" identifier may come into conflict with the in-built PASSWORD() function, so it is better to escape it just in case.

Straight up droppin' t3h knoweledge.

laserlight++

shatteredroses

Now I get this error message:

Warning: Wrong parameter count for mysql_fetch_row() in /home/hollowd/public_html/tests/check.php on line 19

And this is Row 19:

$password = mysql_fetch_row($verify, 0, 2);

gufmn

Do this:

$row = mysql_fetch_array($verify);
$password = $row['password']; //assuming "password" is the name of the collum. I'm feeling lazy and don't feel like looking through the posts .

I'm sure we'll prolly hear that you should be using mysql_fetch_row() but this should work.

shatteredroses

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/hollowd/public_html/tests/check.php on line 18

//Line 18 -------> 
$row = mysql_fetch_array($verify);

ARG! What is wrong with me? Why do I keep getting these "supplied argument is not a valid MySQL result resource" errors?
sniffs

gufmn

Ok, that's the problem with trying to use mysql_fetch_array.

Change it back to mysql_fetch_row and look here
The first site example is pretty much exactly what you're trying to do. If this doesn't work, your problem must be somewhere else.

shatteredroses

<?

//pulls the username and password from the previous form 
$user = $_REQUEST["user"]; 
$pass = $_REQUEST["pass"]; 

//makes the connection to the database 
$conn = mysql_connect("localhost", "hollowd_roses", "********"); 
mysql_select_db("hollowd_Test", $conn); 

//creates a username query 
$result = mysql_query("SELECT username, password FROM login WHERE username = '$user'");
if (!$result) {
   echo 'Could not run query: ' . mysql_error();
   exit;
}
$row = mysql_fetch_row($result);


//checks password against database 
if ($pass == $password){ 
    PRINT "You are Logged In"; 
    } else { 
    PRINT "Not Logged In"; 
} // end if 

?>

That is currently all the code I'm running, and now I'm not getting any more errors, but I am still getting 'Not Logged In' no matter what I type.

gufmn

change:

$user = $_REQUEST["user"]; 
$pass = $_REQUEST["pass"];

to:

$user = $_POST["user"]; 
$pass = $_POST["pass"];

then define $password before comparing it to $pass

$row = mysql_fetch_row($result);
$password = $row[1];

//checks password against database
if ($pass == $password){ 
    PRINT "You are Logged In"; 
    } else { 
    PRINT "Not Logged In"; 
} // end if

This is assuming "password" is the second collum in your db. If it's is first, use $row[0].
If it's the 3rd row, use $row[2] etc.

And btw, you might want to edit your post and remove the password from your db connection 🙂

shatteredroses

I'm really sorry if it's just that I haven't integrated your coding properly, and for taking up so much of your time. Thanks so much for taking the time to explain it all to me so carefully. It's greatly appreciated.
So far this is what I have:

<?

//pulls the username and password from the previous form 
$user = $_POST["user"];  

$pass = $_POST["pass"];

//makes the connection to the database 
$conn = mysql_connect("localhost", "hollowd_roses", "*********"); 
mysql_select_db("hollowd_Test", $conn); 

//creates a username query 
$result = mysql_query("SELECT username, password FROM login WHERE username = '$user'");
if (!$result) {
   echo 'Could not run query: ' . mysql_error();
   exit;
}

$row = mysql_fetch_row($result); 
$password = $row[2]; 

//checks password against database 
if ($pass == $password){  

    PRINT "You are Logged In";  

    } else {  

    PRINT "Not Logged In";  

} // end if

?>

Despite this I'm still getting 'Not Logged In' returned no matter what. And now I'm getting to the stage where I'm so tired that I can't even understand what it all does anymore. Hehehehehe. So after this I'm going to catch some sleep and come back to it tomorrow.

I also thought it may help to show the structure of my MqSQL database here, just in case I've managed to mess anything up there too, so I've attached a screenshot of it.

Thanks again for all the help you're giving me.
I'm sure I'll get there in the end.

gufmn

I'm really sorry if it's just that I haven't integrated your coding properly, and for taking up so much of your time. Thanks so much for taking the time to explain it all to me so carefully. It's greatly appreciated.

Heh, don't sweat it. I don't mind helping people out if I can and as long as their nice about it (so far you are 😉)

Try echoing your password vars to see what values are being returned.

echo $pass;
echo $password;

Whenever you wake up that is 🙂

shatteredroses

I'm still here . . .
Last post then bed, I swear . . . It's 2AM here . . .
Right, I think I see what you were doing with this. If I'm guessing right then you got me to echo the variables to see whether they're being retrieved or not.
If that is what you intended, then my result was that the $pass variable inputted on the LoginTest.php page that feeds this check.php page, was displayed, but the $password variable that we're trying to retrieve from the database was not.
I know this because I typed 'pineapple' into the password field on LoginTest.php and the output from check.php was 'Pineapple' and not 'Player' or 'ST'.
That aside, it's still not working, like I say, it's not retrieving the variables from the database for some reason.
Here's where I added the echo code, incidentally:

$row = mysql_fetch_row($result); 
$password = $row[2]; 

echo $pass; 
echo $password; 

//checks password against database 
if ($pass == $password){  

    PRINT "You are Logged In";  

    } else {  

    PRINT "Not Logged In";  

} // end if

Just in case that's important.
When I log in with the UserName: Player and Password: Player, I get the following output: playerNot Logged In

Thanks for your continued help, it's really appreaciated.
And I'm really going to bed this time.
I mean it.
😃