[RESOLVED] SQL Query Correct?

JamieA

Hi Everybody,

First let me say I am very grateful to have found a place where I can get some help learning PHP! I am learning by myself out of books, and have no experience with other languages other than html and css.

I am creating a script (given in a book) that is supposed to take a username and password from a form, and check that it is in a mysql database, and then, if a match is found, echo a message.

I can't get it to work, and I think the place where it is failing is the sql query itself, so I am hoping that someone may see something from with the syntax. The query I am using is

$sql= "SELECT * FROM $table_name WHERE username = '$POST[username]' AND password = password('$POST[password]')";

I am using PHP 5.1.2, and mysql 4.1.15. Thanks so much for checking this out! 🙂

Best,
Jamie

Houdini

What is this for?

password('$_POST[password]')

JamieA

You know, I was wondering that myself! The book doesn't tell you , it just appears out of nowhere!

I assumed it was a function I hadn't heard of. It is kind of intimated that it is for the purpose of interpreting the password in its hash form. In any case, I tried not using it, and just using

'$_POST[password]'

and that didn't work either!

Jamie

Houdini

OK here is what the book was probably trying to do is encrypt the password you can use md4 or sha1 with md5 more often used so lets rewrite that query and see if it does not work, This code will encrypt the password and there is no reversing it.

$password= md5($_POST['password']); //here we will encrypt the password
$sql= "SELECT * FROM $table_name WHERE username = '{$_POST[username]}' AND password = '$password'"; 
echo $sql;//lets see what the query looks like to MySQL
$result =mysql_query($sql)
  or die("The query failed. MySQL said: ".mysql_error());

JamieA

Thanks for that, but it doesn't seem to work. Maybe I should post the whole script, maybe you will pick something else up......

if ((!$POST['username']) || (!$POST['password'])) {
header("Location: show_login.html");
exit;
}

$db_name = "PHP5";
$table_name = "auth_users";

$connection = @mysql_connect("---","---","-----") or die(mysql_error());
$db=@mysql_select_db($db_name,$connection) or die(mysql_error());

//$sql= "SELECT * FROM $table_name WHERE username = '$POST[username]' AND password = password('$POST[password]')";

$password= md5($POST['password']); //here we will encrypt the password
$sql= "SELECT * FROM $table_name WHERE username = '{$POST['username']}' AND password = '$password'";
echo $sql;//lets see what the query looks like to MySQL
$result =mysql_query($sql)
or die("The query failed. MySQL said: ".mysql_error());

//$result = @($sql, $connection) or die(mysql_error());
$num = mysql_num_rows($result);

if ($num != 0) {
$msg = "<p>Congratulations, you're authorized!</p>";
}
else {
echo "can't get you in, sorry.";
}
/else{
header("Location: show_login.html");
exit;
}/

?>
<html><head><title>Secret Area</title></head>
<body>
<? echo "$msg"; ?>

</body>
</html>

Houdini

Try this reformatted and some corrections to the code

<?php//use full tags for pertability and to ensure this code works on all servers
if ((!$_POST['username']) || (!$_POST['password'])) {
header("Location: show_login.html");
exit;
}
$db_name = "PHP5";
$table_name = "auth_users";
$connection = @mysql_connect("---","---","-----") or die(mysql_error());
$db=@mysql_select_db($db_name,$connection) or die(mysql_error());
//$sql= "SELECT * FROM $table_name WHERE username = '$_POST[username]' AND password = password('$_POST[password]')";
//$password= md5($_POST['password']); //here we will encrypt the password
$password=$_POST['password'];//create  short variables
$username=$_POST['username'];
$sql= "SELECT * FROM $table_name WHERE username = '$username' AND password = '$password'";
echo $sql;//lets see what the query looks like to MySQL
$result =mysql_query($sql)
or die("The query failed. MySQL said: ".mysql_error());
//$result = @mysql_query($sql, $connection) or die(mysql_error());
$num = mysql_num_rows($result);
if ($num != 0) {
$msg = "<p>Congratulations, you're authorized!</p>";
}
	else {
	echo "can't get you in, sorry.";
	}
/*else{
header("Location: show_login.html");
exit;
}*/
?>
<html><head><title>Secret Area</title></head>
<body>
<?php echo "$msg"; ?>
</body>
</html>

When posting code please use the PHP BB Code provided in these forums. Look at my signature to see how by clicking the link.

Drakla

Nobody here seems to have said what the problem is. It looks like you can't log in to a site - why, your password doesn't match - why? You're checking for it with md5() or password() and maybe it wasn't put into the database with md5 or password when it was first made.

JamieA

Sorry about that, I will use the BB Code from now on.

It is still not working, I am getting only the echo of the query..

SELECT * FROM auth_users WHERE username = 'jamie' AND password = 'ohyes'can't get you in, sorry.

It is quite perplexing. If the code is correct, what are the other possible reasons why it is not working? I checked the form they tell you to use, and I notice it has an input field for the password with the type "password". Is that correct?

Here is the form....

<form method="post" action="do_authuser.php">
<p><strong>Username:</strong><br>
<p><input type="text" name="username" size="25" maxlength="25"></p> 
<p><strong>Password:</strong><br>
<input type="password" name="password" size="25" maxlength="25"></p>
<p><input type="submit" name="submit" value="Login"></p></form>

Houdini

Yes it is are you recieving any errors at all?

JamieA

No, no errors except that $msg is undefined, because the script didn't get that far I guess.

I am going to try some other queries on this table just to make sure I am connecting okay. I won't get to that till tomorrow, so thanks for all your help so far!

Best,
Jamie

Houdini

I rewroked and tested your code both the form and the process page and tested with a database and this script will work. After testing you may remove the echos that are just for information while developing, once satisfied remove those that are not needed. Properly written if the script authorizes the user you can use the header function to go to an entirely new page without using the HTML at the bottom which merely now appends the Congratulations you are authorized message to the bottom of all the testing echos.
html form

<html>
	<head>
		<title>
		Test Form Page
		</title>
	<head>
		<body>
			<form method="post" action="do_authuser.php"> 
				<p><strong>Username:</strong><br></p> 
				<p><input type="text" name="username" size="25" maxlength="25" /></p> 
				<p><strong>Password:</strong><br></p> 
				<p><input type="password" name="password" size="25" maxlength="25" /></p> 
				<p><input type="submit" name="submit" value="Login" /></p>
			</form>
		</body>
</html>

do_authuser.php

<?php
//use full tags for pertability and to ensure this code works on all servers
if(!$_POST['username'] || !$_POST['password']) {//the if statement was incorrect
header("Location: show_login.html");
exit;
}
//create a count for attempts that fail
$count=0;
//check the username and password variables before the database query
echo "The username is reported as: {$_POST['username']}<br />";
echo "The password is being reported as : {$_POST['password']}<br />";
$db_name = "PHP5";
$table_name = "auth_users";
//remove the @ which supresses errors while debugging the code add some debugging
$connection = mysql_connect("localhost","root") 
	or die("Was unable to connect to the database. MySQL said: ".mysql_error());
$db=mysql_select_db($db_name,$connection) 
	or die("Was not able to select the database, MySQL said: ".mysql_error());
//$password= md5($_POST['password']); //here we will encrypt the password
$password=$_POST['password'];//create  short variables
$username=$_POST['username'];
$sql= "SELECT * FROM $table_name WHERE username = '$username' AND password = '$password'";
echo $sql."<br />";//lets see what the query looks like to MySQL
$result =mysql_query($sql)
or die("The query failed. MySQL said: ".mysql_error());
//$result = @mysql_query($sql, $connection) or die(mysql_error());
$num = mysql_num_rows($result);
//Lets see what $num is, is is what we expect?
echo "There are $num rows from this query";
if ($num == 1) {
//there is a match in the database so Create the message with $msg
$msg = "<p>Congratulations, you're authorized!</p>";
//or uncomment the below line to send the user to a new page for authorized users
//with An even better message and full page of stuff for members only.
//header("Location: members.php");
}
//the username and password were not both found or correct
    else {
    	if($count<= 3){
		echo "Can't get you in, sorry.";
		$count++;//increase attempt count 
		}
		else{
       	die("You have failed 3 times to enter the proper information. Press your back button to try again");
    	}
	}
?>
<html>
<head>
<title>Secret Area</title>
</head>
<body>
<?php echo "$msg"; ?>
</body>
</html>

Drakla

Make sure you set your [man]error_reporting[/man] to E_ALL at the top of the script while debugging. It's no good having a patient that won't tell you what's wrong with them.

Levatio

OK, going back to your original post there are a few problems.

Providing your original query looks like this:

$sql= "SELECT * FROM $table_name WHERE username = '$_POST[username]' AND password = password('$_POST[password]')";

When using Superglobals (e.g. $POST, $GET, $SERVER, etc.) inside a double qutoed string (as above) you must surround it with curly braces { and }. Also, the 'password' before ('$POST... should be in capitals because it is an SQL function. So now your query will look like:

$sql= "SELECT * FROM $table_name WHERE username = '{$_POST[username]}' AND password = PASSWORD('{$_POST[password]}')";

However there is still a problem with it. When reffering to something in a Superglobal (e.g. password in $_POST[password]) you must surround it with single quotation marks (unless it is a numbered array - which superglobals tend not to be). So the correct version of the query is:

$sql = "SELECT * FROM $table_name WHERE username = '{$_POST['username']}' AND password = PASSWORD('{$_POST['password']}')";

If the result of the query is true then the data submitted by the user is correct.

Furthermore, if you're picky like me selecting an entire row from a table is unnessecary when you don't need all of it. Just refer to one column if checking a username and password is all you need. On a site with high volume traffic the unnessecary memory usage builds up. Finally:

$sql = "SELECT username FROM $table_name WHERE username = '{$_POST['username']}' AND password = PASSWORD('{$_POST['password']}')";

JamieA

I am sorry to report that none of the suggested remedies work. I get this

The username is reported as: jamie
The password is being reported as : ohyes
SELECT * FROM auth_users WHERE username = 'jamie' AND password = 'ohyes'
There are 0 rows from this queryCan't get you in, sorry.
Notice: Undefined variable: msg in /usr/www/amy321/web/PHP_Study/PHP5/db/do_authuser.php on line 53

I tried using a simple query, one where the values were supplied directly and not taken for the superglobal array, and that did work. I used.....

 SELECT f_name, l_name FROM auth_users WHERE username = "geri"

and it worked. So, the problem is in receiving the values from the form, I guess.

I really appreciate everyones help, in any case. Thanks very much, and I have learned some things in any case. Maybe the answer will turn up sometime in the future.

Best,
Jamie

Houdini

Do a phpinfo()

<?php
phpinfo();
?>

and see what register_globals says. If they are on then you will need to access your $_POST variables differently. Also with phpinfo() you will see what version of PHP you are running.

Apparently from what was echoed you are passing the variables correctly but the username and password that you are entering into the query are not being found or do not match. What happens if you SELECT * FROM tablename with no WHERE clause, if that name and password do not show up then that is where the problem is because the query is returning zero rows meaning that there is no row that meets the criteria of the query. I made up a database and the code works using known values for the code and the query, so check your database and make sure the tablenames and all are correct for the query.

JamieA

Okay, I did that.

My register_globals, IS on. So, what does that mean in terms of how I access the $_POST variables? Maybe that is my problem.

I have checked the table, and my names are correct. (I was hoping I made a mistake!).

Jamie

Houdini

With register globals on then there are security issues, but if it is on your own PC then that is not to big a deal but if it is on the web then it is a problem. your post variables would just be the field name without the $_POST['fieldname']
Try this make a simple form and call it anything you want.

<html>
<body>
<form action='testglobals.php' method = 'post'>
Enter a name: <input type = 'text' name = 'user'><br />
Enter an email: <input type ='text' name = 'email'><br />
Enter an address: <input type = 'text' name='address'><br />
City: <input type = 'text' name = 'city'><br />
State: <input type='text' name='state'><br />
<input type = 'submit' name = 'check' value = 'go'>
</form>
</body>
</html>

testglogals.php

echo $user."<br />";
echo $email."<br />":
echo $address."<br />";
echo $city."<br />";
echo $state."<br />";
echo "All the post variables are above <hr />";
echo "$HTTP_POST_VARS['user']";
echo "$HTTP_POST_VARS['email']";
echo "$HTTP_POST_VARS['address']";
echo "$HTTP_POST_VARS['city']";
echo "$HTTP_POST_VARS['state']";
echo "All the \$HTTP_POST_VARS for above.";

Run that script and see what you get with the POST variables.

JamieA

I am getting this error:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING in /usr/www/amy321/web/testglobals.php on line 16

for this line............

echo "$HTTP_POST_VARS['user']";

Jamie

tsinka

Hi,

modify the $HTTP_POST_VARS lines so that they look like e.g.

echo $HTTP_POST_VARS['user'];

echo "{$HTTP_POST_VARS['user']}";

remove the double quotes
or
enclose the array variables by curly braces

I'd prefer the first one.

Thomas

Houdini

Sorry my fault, since I have never used that $HTTP_POST_VARS['var'] , and should have treated it the same as $_POST['var']