MySQL Query With PHP

PPowerHouseK

Hello,

I am trying to set a user login that would check a database for existing user and password data. Here is the two scripts I have that should do that, but I keep getting an error no matter what I do.

<html>
<head>
<title>MySQL Login Test</title>
</head>
<body>

</body>
<form action="newlogin.php" method="post">
	<p>Name:&nbsp;&nbsp;<input type="txt" value="" name="username" /></p><br />
		<p>pass:&nbsp;&nbsp;<input type="txt" value="" name="password" /></p><br />
    <p><input type="submit" value="Process &rarr;"></p>
</form>
</html>

<?php


if(!$username || !$password)
	{
		header("Location:newlogin.html");
		exit();
	}

$user = "user";
$pass = "pass";
$conn=@mysql_connect("localhost", $user, $pass);
if($conn)
	{
		//echo "Database Connection Successful <br>";
		mysql_select_db("weblogin", $conn);

	$sql = "select * from 'members' where 'users'=\"$username\" and 'passwords'=\"$password\"";

	$rs = mysql_query($sql,$conn);

	$match = mysql_numrows($rs);

	if($match!=0)
		{
			header("Location:home.php");

		}

	mysql_close($conn);	
	echo "command: $sql <br>";
	echo "rs: $rs";
	echo "rs1 $rs1";

}
else
	{
		echo "I die man, i die...<br>";
	}


echo "$db_list";	


?>

Here is the output:

Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\newlogin.php on line 22
command: select * from 'members' where 'users'="asfdafd" and 'passwords'="dfasdfasdf" 
rs: rs1

Any help would be much appreciated. Thanks in advance!

bradgrafelman

Few things:

Since you never define the variables $username or $password anywhere, they should not exist and cause an 'undefined variable' error. Since you are not getting this, you most likely have register_globals enabled. Note that you should immediately disable register_globals and never write code that relies on it being enabled. It's disabled by default, and for some good reasons - see [man]security.globals[/man] for some of them.
Also note that user-supplied data should never be placed directly into a SQL query, else your code will be vulnerable to SQL injection attacks and/or just plain SQL errors. Instead, you must first sanitize it with a function such as [man]mysql_real_escape_string/man (for string data).
Another "feature" of PHP that, IMHO, you should never use is the '@' error suppressor. This does nothing but eliminate any helpful debugging data that PHP could be offering. If you're using it to suppress errors in a production environment, note that display_errors shouldn't be enabled in such an environment anyway - that's what the log_errors directive is to be used for.
In your SQL query, you've got a couple of problems. For one, anything surrounded by single quotes is treated as a string. In other words, you're trying to SELECT everything from the string 'members', not the table members. Identifiers should not be surrounded with quotes (unless ANSI_QUOTES mode is enabled on MySQL, but this isn't very common) - instead, you should either use a) nothing at all, or b) backticks (as I just did in the previous sentence).

Likewise, double quotes don't always identify a string; as such, you should always use single quotes as the delimiters for strings in your SQL queries.
Try to avoid using 'SELECT *' - instead, only SELECT columns from which you actually need data. In your case, since you don't actually use data from any column, you could just do a 'SELECT 1' (e.g. SELECT some simple, fixed value just for the purpose of testing whether the WHERE condition matched a row or not) - that way MySQL doesn't have to bother returning a bunch of data that you just throw away.
You should be checking to see if [man]mysql_query/man returned boolean FALSE (indicating an error) just out of good practice. Plus, if it does return FALSE, you can add debug code that outputs (or logs, depending upon which environment we're talking about) the SQL query as well as the error message returned by the MySQL server (e.g. via [man]mysql_error/man).

PPowerHouseK

How would I define $username and $password from a separate document if not by registering globally?

bradgrafelman

They aren't from "a separate document"... if you want data that is POST'ed to the current PHP script, then you'd use the $_POST array.

See [man]variables.external[/man] for more information.

PPowerHouseK

Okay I think I have improved it a good amount based off your advice. Also it has helped me trace the problem to my sql syntax. I am quite new to MySQL so I am sure the syntax could use some adjustment. Here is my current code and error:
newlogin.php

<?php

$username=$_REQUEST['username'];
$password=$_REQUEST['password'];
if(!$username || !$password)
	{
		echo "No username or pass was entered";
		//header("Location:newlogin.html");
		exit();
	}

$user = "user";
$pass = "pass";
$conn=mysql_connect("localhost", $user, $pass);
if($conn)
	{
		//echo "Database Connection Successful <br>";
		mysql_select_db("weblogin", $conn);

	$sql = 'SELECT 1 FROM members WHERE users=$username AND passwords=$password LIMIT 0, 30 ';

	$rs = mysql_query($sql,$conn);
	If($rs==FALSE)
		{
			echo"Error query failed";
		}
	else
		{
			echo"Query Successful";
		}

	$match = mysql_numrows($rs);

	if($match!=0)
		{
			header("Location:home.php");

		}

	mysql_close($conn);	
	echo "command: $sql <br>";
	echo "rs: $rs";
	echo "rs1 $rs1";
	echo $_REQUEST['username'];
	echo $username;

}
else
	{
		echo "I die man, i die...<br>";
	}


echo "$db_list";	


?>
<html>
<head>
<title>MySQL Test</title>
</head>
<body>

</body>

</html>

Error query failed
Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\newlogin.php on line 32
command: SELECT 1 FROM members WHERE users=$username AND passwords=$password LIMIT 0, 30 
rs: rs1 BoogerManBoogerMan

bradgrafelman

Couple new problems:

In PHP, strings delimited by single quotes are not evaluated for variable interpolation; in other words, '$var' is a dollar sign followed by the letters 'var' whereas "$var" is the value of the variable named $var.

Notice how your error message includes the literal variable names instead of their values?
You're now missing the quotes around the string values in your SQL query. If you're comparing something to a string value, that string must be enclosed in quotes (single quotes, to be precise) in the SQL query - just like they do in PHP.

Also, why do you have a 'LIMIT 0, 30' clause on your query? Surely the 'users' column has a UNIQUE or PRIMARY key on it, meaning that only 0 or 1 rows would ever match the WHERE clause. In that case, there's no need to add a LIMIT clause... but even if you did want to add it for clarity, it should be 'LIMIT 1' because anything else doesn't make sense.

PPowerHouseK

Hmm I dont quite know what you mean, this is the closest I could get:

$sql =(SELECT 1 FROM 'members' WHERE usernames="$username" AND passwords="$password");

How should that look?

Right now it throws the error:

Parse error: syntax error, unexpected T_LNUMBER in C:\xampp\htdocs\newlogin.php on line 20

Thanks again!

bradgrafelman

Er.. why'd you get rid of the quotes around the SQL query string? The SQL query itself is just a regular string - no different than any other string in PHP. As such, you have to have quotes around it.

As I said above, the variables $username and $password are string values that you're trying to use in comparisons inside the SQL query. In a SQL query, all string values must be quoted (just like in PHP), except for compatibility you should use single quotes in SQL queries - not double quotes (which can sometimes be used to delimit identifiers instead of the backticks).

PPowerHouseK

Okay I think Im closer now, this is what I have:

$sql =("SELECT 1 FROM members WHERE usernames=\"$username\" AND passwords=\"$password\"");
	$rs = mysql_query($sql,$conn);
$match = mysql_numrows($rs);

and this is the error:

Warning: mysql_numrows() expects parameter 1 to be resource, boolean given

bradgrafelman

You still have the double quotes around the string values in the query... see my post above;

bradgrafelman wrote:
... you should use single quotes in SQL queries - not double quotes (which can sometimes be used to delimit identifiers instead of the backticks).

PPowerHouseK

So you are saying to do this:

$sql =("SELECT 1 FROM members WHERE usernames='$username' AND passwords='$password'");

instead of:

$sql =("SELECT 1 FROM members WHERE usernames=\"$username\" AND passwords=\"$password\"");

and definately not:

$sql =("SELECT 1 FROM members WHERE usernames="$username" AND passwords="$password"");

I apologize if my confusion is annoying... but I am trying!

bradgrafelman

Well definitely not the last one, since that contains PHP syntax errors. And yes, the first version looks correct, assuming that $username and $password contain sanitized data (something you still haven't addressed yet!).

PPowerHouseK

Alright so now I have this from that link you posted. I think that gives me sanitized variables. But I am still getting this error as well.

$query = sprintf("SELECT 1 FROM members WHERE usernames='$username' AND passwords='$password'",
            mysql_real_escape_string($username),
            mysql_real_escape_string($password));
		$rs = mysql_query($query,$conn);
		If($rs==FALSE)
			{
				echo"Error query failed!";
			}
		else
			{
				echo"Query Successful!";
			}

	$match = mysql_numrows($rs);

	if($match!=0)
		{
			header("Location:home.php");

		}

	mysql_close($conn);	
	echo "command: $query <br>";
	echo "rs: $rs <br>";

Error query failed!
Warning: mysql_numrows() expects parameter 1 to be resource, boolean given in C:\xampp\htdocs\newlogin.php on line 48
command: SELECT 1 FROM members WHERE usernames='BoogerMan' AND passwords='chigger'

So it seems like my query still fails.. Is it my syntax?

bradgrafelman

About using [man]sprintf/man: the second, third, ... n'th parameters are used to specify which values will replace the second, third, ... n'th placeholders inside the format string (the first parameter). For example, to specify a placeholder for a string, you'd use %s at the appropriate place inside the format string.

You, however, are not using these placeholders and are still inserting the variables directly into the SQL query. In other words, sprintf() isn't actually doing anything and you still have the equivalent string as you did before.

PPowerHouseK wrote:
So it seems like my query still fails.. Is it my syntax?

What does the MySQL error message say?

PPowerHouseK

Hmm I edited it to this but I seem to get the same error:

[code=php]$query = sprintf("SELECT 1 FROM members WHERE usernames='%s' AND passwords='%s",
        mysql_real_escape_string($username),
        mysql_real_escape_string($password));
	$rs = mysql_query($query,$conn);
	If($rs==FALSE)
		{
			echo"Error query failed!";
		}
	else
		{
			echo"Query Successful!";
		}

[/code]

Error query failed!
Warning: mysql_numrows() expects parameter 1 to be resource, boolean given

How would I see the mysql errors other than the ones i see now?

bradgrafelman

You haven't shown us any MySQL errors yet - just PHP error messages. Refer back to my first post:

bradgrafelman;10977394 wrote:
You should be checking to see if [man]mysql_query/man returned boolean FALSE (indicating an error) just out of good practice. Plus, if it does return FALSE, you can add debug code that outputs ... the error message returned by the MySQL server (e.g. via [man]mysql_error/man).

Also note that on the manual page for [man]mysql_query/man, both coding examples show how to output such information when debugging a failing query.