Authentication Syntax Error?

I_Mojo_Jojo

I have written an authentication script from VTC's tutorial disk on PHP.

However, there is something wrong with it.

The basic concept is it queries a db user table for two values, user and password. It uses mysql_num_rows to verify the number of occurances of a match.

I did some experiments to see if its connecting to the db and the table. It is connecting to the database. I modified the script to display the table of users to confirm it has the table, and it did.

Its simply always showing mysql_num_rows as value 0.

<? session_start() ?>
<HTML>
<HEAD>
<TITLE>Roster Management Login</TITLE>
</HEAD>

<BODY>
<H2>Roster Management Login</H2>
<?
$links="<A HREF='main.php'>Click here to proceed to the main Roster Management page.</A><BR><BR>";
$links.="<A HREF='logout.php'>Click here to log out.</A>";
if ($user && $pass) {
if ($logged_in_user==$user) {
echo $user.", you are already logged in.<BR><BR>";
echo $links;
exit;
}
$db=mysql_connect("localhost");
mysql_select_db("database", $db) or die ("<H3>database not existent</H3>");
$result=mysql_query("SELECT * FROM users WHERE user = '".$user."' AND password = PASSWORD('".$pass."')");
if (!$result) {
echo "Cannot Retrieve that Data.";
exit;
}
if (mysql_num_rows($result) > 0) {
$logged_in_user=$user;
session_register("logged_in_user");
echo "Welcome, ".$logged_in_user.".<BR><BR>";
echo $links;
exit;
} else {
echo "Invalid login. Please try again. <BR><BR>";
echo mysql_num_rows($result);
echo "<BR>";
echo $user;
echo "<BR>";
echo $pass;
echo "<BR>";
}
} else if ($user || $pass) {
echo "Please fill in both fields.<BR><BR>";
}
?>
<FORM METHOD=POST ACTION="login.php">

Username:
<INPUT NAME="user" TYPE=TEXT MAXLENGTH=20 SIZE=20>
<BR>
Password:
<INPUT NAME="pass" TYPE=PASSWORD MAXLENGTH=20 SIZE=20>
<BR>
<INPUT TYPE=SUBMIT VALUE="Login">
</FORM>
</BODY>
</HTML>

bretticus

PASSWORD and USER are reserved words in MySQL. After quickly looking through the script, it seemed okay except I see you have password and user as column names. You might try quoting them with backticks (funny I just recommended the same thing to my last post.)

Therefore, your query becomes instead...

$result=mysql_query("SELECT * FROM users WHERE user = '".$user."' AND password = PASSWORD('".$pass."')");

I would recommend you change the column names either way to avoid problems in the future.

toplay

I'm surprised that you're not seeing your "Cannot Retrieve that Data." message because you're using some MySQL reserved words for table and column names which should return an SQL error.

The reserved words are user and password. Notice you're even using the MySQL PASSWORD() function which is the same name as your column name! I would change your column names to something else to avoid further confusion, or place the column names and table name in backtick marks. Example:

$result=mysql_query("SELECT * FROM users WHERE user = '$user' AND password = PASSWORD('$pass')")

hth.

EDIT:

bretticus beat me to it again! 🙂

bretticus

Looks like we're right on each other heels Toplay 😃 Good work!

I_Mojo_Jojo

Thank you both!

I have been struggling with this for hours and I simply could not find a syntax or other error.

I_Mojo_Jojo

I changed the column names, and it still wont show the num as anything but zero

But I cant rule out the possibility of a syntax error.

I will have to proof what I have.

toplay

Well, you could have the user name or password wrong entered wrong and/or in the database. I assume you used the SQL PASSWORD() function when you first inserted the password in the database? Display the query with all the values plugged in to see what it looks like (and try it in phpmyadmin or mysql command prompt too).

I would not use SQL PASSWORD() myself, but rather use mcrypt or hash it with sha1() or md5().

FYI - note that session_register() requires that register_globals be on. Keep that setting off and use $_SESSION instead.

EDIT:

FYI: If you put your code in this forum's PHP tags, then it will make the code easier to see (and members will be more inclined to help you).

I_Mojo_Jojo

Ok, I confirmed that global variables are on.

I changed the variable names, but I still sees num rows as 0.

I dont see the error.

<? session_start() ?> 
<HTML>
<HEAD>
 <TITLE>Roster Management Login</TITLE>
</HEAD>

<BODY>
<H2>Roster Management Login</H2>
<?
	$links="<A HREF='x_management_main.php'>Click here to proceed to the main Roster Management page.</A><BR><BR>";
	$links.="<A HREF='x_management_logout.php'>Click here to log out.</A>";
	if ($fuser && $fpass) {
		if ($logged_in_user==$fuser) {
			echo $fuser.", you are already logged in.<BR><BR>";
			echo $links;
			exit;
		}
		$db=mysql_connect("xxxxxxxxx", "xxxxxxxxx", "xxxxxxxxx");
		mysql_select_db("xxxxxxxxx", $db) or die ("<H3>database not existent</H3>");
		$result=mysql_query("SELECT * FROM users WHERE userslo = '".$fuser."' AND passwordslo = PASSWORD('".$fpass."')");
		if (!$result) {
			echo "Cannot Retrieve that Data.";
			exit;
		}
		if (mysql_num_rows($result) > 0) { 
			$logged_in_user=$fuser;
			session_register("logged_in_user");
			echo "Welcome, ".$logged_in_user.".<BR><BR>";
			echo $links;
			exit;
		} else {
			echo "Invalid login.  Please try again. <BR><BR>";
			echo mysql_num_rows($result);
			echo "<BR>";
			echo $fuser;
			echo "<BR>";
			echo $fpass;
			echo "<BR>";
		}
	} else if ($fuser || $fpass) {
		echo "Please fill in both fields.<BR><BR>";
	}	
?>
 <FORM METHOD=POST ACTION="x_management_login2.php">  

 Username:
 <INPUT NAME="fuser" TYPE=TEXT MAXLENGTH=20 SIZE=20>
 <BR>
 Password:
 <INPUT NAME="fpass" TYPE=PASSWORD MAXLENGTH=20 SIZE=20>
 <BR>
 <INPUT TYPE=SUBMIT VALUE="Login">
 </FORM>
</BODY>
</HTML>

toplay

Read my previous post again and you didn't answer my question. Also, did you try it in phpmyadmin first and what's the result?

You still don't have backtick marks around users.

When you say "I still sees num rows as 0", do you mean that you see the "Invalid login. Please try again." message?

I_Mojo_Jojo

Sorry, I did try the back ticks, it didnt work. So then I changed the database rows and variable names.

I did not think the table name users was a problem, but I will try that.

No, the output of the script is "Invalid login"

It echos the num as "0".

Then echos the user name.

Then it echos the password.

Then it lists the form boxes to try again.

And yes, I used PASSWORD to enter the password into mysql. When I display it on the server or echo it on a php script, it give a long number.

toplay

The password should not be a number, but should show letters (A-F) and digits (0-9). The password may need more characters than what the actual limit you allow in the form. So, make sure the "passwordslo" column is defined as VARCHAR and not as a number type.

If the above doesn't fix it, then since MySQL can't find the row with the username and password, then it's because of what I said before - the username and/or password is wrong in the table (or you're entering it wrong in the form).

I_Mojo_Jojo

Yes, the password on the database is letter and numbers. Its mostly numbers.

To make things simpler for debugging this, I added a 2 letter user with the same 2 letters as the password.

However, it still doesnt work.

I tried adding back ticks to the table name in the query: user

That didnt work.

toplay

Start putting displays (echo/print) at strategic places within your code to help yourself debug this.

Display the password from the table, and do a separate select with the password entered. Example:

$pswd_sql = "SELECT PASSWORD('$fpass')";

Then see if they display the same thing!

Try it in phpmyadmin too!

Do yourself a favor and turn off register_globals and use $_POST instead.

I_Mojo_Jojo

Well, I took out the PASSWORD attribute from the script and the table.

And now it works. I know, Im stubborn.

However, something interesting occurred after that.

The PHP pages it links to, x_management_main.php and x_management_logout.php are both totally blank. There is no error, there is just nothing on the page.

I know that logout is logging out the user, from using the login page (it knows when Im logged in and when it says I am, I go to logout and it allows me to login again).

Now here is the really strange part (to me). If I hit view source, the source shows all the tags for a header and a simple message and two links. But none of that displays. Even the page header title doesnt show.

Here are the PHP for the main page:

<? session_start() ?>
<HTML>
<HEAD>
 <TITLE>Main Roster Management</HTML>
</HEAD>

<BODY>
 <H2>Main Roster Management</H2>
 <BR>
 <?
	if (!$logged_in_user) {
		echo "<H3>Whoops!</H3><BR>";
		echo "Your not logged in.  No shirt, no shoes...   It's like that.<BR>";
		echo "<A HREF='x_management_login.php'>Please click here to login.</A>";
		exit;
	}
	echo "Welcome, ".$logged_in_user.".";
 ?>
This is the main page. Exciting, isn't it.
<BR>
<BR>
<A HREF="x_management_logout.php">Click here to log out.</A>
</BODY>
</HTML>

Here are the PHP for the logout page:

<? session_start() ?>
<HTML>
<HEAD>
 <TITLE>Roster Management Logout</HTML>
</HEAD>

<BODY>
<H2>Roster Management Logout</H2>
<?
	if($logged_in_user) {
		session_unregister("logged_in_user");
		echo "You are now logged out.";
	} else {
		echo "You haven't even logged in yet.";
	}
?>
<BR>
<BR>
<A HREF="x_management_login.php">Click here to log in.</A>
</BODY>
</HTML>

Both yields a page that is totally blank. However, this is the "view source" of the pages:

Main:

<HTML>
<HEAD>
 <TITLE>Main Roster Management</HTML>
</HEAD>

<BODY>
 <H2>Main Roster Management</H2>
 <BR>
 Welcome, jk.This is the main page. Exciting, isn't it.
<BR>
<BR>
<A HREF="x_management_logout.php">Click here to log out.</A>
</BODY>
</HTML>

Logout:

<HTML>
<HEAD>
 <TITLE>Roster Management Logout</HTML>
</HEAD>

<BODY>
<H2>Roster Management Logout</H2>
You are now logged out.<BR>
<BR>
<A HREF="x_management_login.php">Click here to log in.</A>
</BODY>
</HTML>

I am thinking its something to do with the session?