Page 1 of 1

mysql_num_row issue

Posted: Wed Feb 02, 2011 2:18 pm
by Gimpy

Code: Select all

<?php 
session_start();

$username = "";
$pword = "";
$db = "";
$con = mysql_connect(localhost, $username, $pword);

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

mysql_select_db($db, $con) or die("Unable to select database");
$loggedin = mysql_query("SELECT * FROM users WHERE `user` = '$user', `pass` = '$pass', `auth` = '1'");
//$rows = mysql_num_rows($loggedin);
if (mysql_num_rows($loggedin) == 1)  {
//Set session
$_SESSION['user'] = $user;
header('Location: site.php');
}
else {
header('Location: index.php');
}	
?>
[text]Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in dologin.php on line 15[/text]

Ive tried a few things and cant get around the mysql_num_rows for some reason. Also, is there a way to call for a header at the top and choose which header I want to use? or would that require a different code?

Re: mysql_num_row issue

Posted: Wed Feb 02, 2011 2:29 pm
by John Cartwright
It is because you have an error in your SQL, and a valid resource is not being returned.

Code: Select all

$loggedin = mysql_query("SELECT * FROM users WHERE `user` = '$user', `pass` = '$pass', `auth` = '1'");
to

Code: Select all

//good practice to check if query failed or not

$sql = "
   SELECT * 
   FROM users 
   WHERE `user` = '". mysql_real_escape_string($user) ."' 
       AND `pass` = '". mysql_real_escape_string($pass) ."' 
       AND `auth` = '1'
";
$loggedin = mysql_query($sql) or die(mysql_error());
Notice my usage of mysql_real_escape_string()? This is absolutely critical you escape all user input, otherwise they can manipulate the query with SQL injection.

But the reason your query failed is because you are delimiting your WHERE clause with commas, when it should be logical operators, i.e.,

Code: Select all

SELECT * FROM users WHERE `user` = '$user' AND `pass` = '$pass' AND `auth` = '1'

Re: mysql_num_row issue

Posted: Wed Feb 02, 2011 8:58 pm
by Gimpy
Okay, so why does ...

Code: Select all

$sql = mysql_query("SELECT * FROM users WHERE user = '" . mysql_real_escape_string($_POST['user']) . "' and (pass = '" . mysql_real_escape_string(md5($_POST['pass'])) . "' and auth = '1'");
Give me this error?
[text]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 '' at line 1
[/text]

stats on the server are..
Apache version 2.2.16
PHP version 5.2.16
MySQL version 5.1.47-community-log

Re: mysql_num_row issue

Posted: Thu Feb 03, 2011 1:46 am
by tutigan
Gimpy wrote:Okay, so why does ...

Code: Select all

$sql = mysql_query("SELECT * FROM users WHERE user = '" . mysql_real_escape_string($_POST['user']) . "' and (pass = '" . mysql_real_escape_string(md5($_POST['pass'])) . "' and auth = '1'");
Give me this error?
[text]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 '' at line 1
[/text]
you will want to remove the bracket from in front of "pass", and put the md5 before the mysql_real_escape_string so that you get the MD5 of the escaped string, not the escaped version of the md5 version of the string!

Code: Select all

$sql = mysql_query("SELECT * FROM users WHERE user='" . md5(mysql_real_escape_string($_POST['user'])) . "' AND pass='" . mysql_real_escape_string($_POST['pass']) . "' AND auth='1'");
you could even try:

Code: Select all

$user = mysql_real_escape_string("$_POST[user]");
$pass = md5(mysql_real_escape_string("$_POST[pass]"));
$sql = mysql_query("SELECT * FROM users WHERE user='$user' AND pass='$pass' AND auth='1'");

Re: mysql_num_row issue

Posted: Thu Feb 03, 2011 9:47 am
by John Cartwright
If you are md5'ing the string, you do not need to escape it (although it is good practice). The same applies for integers + floats.

Re: mysql_num_row issue

Posted: Thu Feb 03, 2011 10:26 pm
by Gimpy

Code: Select all

<?php

// Inialize session
session_start();

// Include database connection settings
include('config.inc.php');

$user = mysql_real_escape_string("$_POST[user]");
$pass = md5("$_POST[pass]");

// Retrieve username and password from database according to user's input
mysql_select_db($dbname, $con) or die("Unable to select database");
$sql = mysql_query("SELECT * FROM users WHERE `user` ='$user' AND `pass`='$pass' AND `auth` = 'a'");
$loggedin = $sql;

// Check username and password match
if (mysql_num_rows($loggedin) == 1) {

// Set username session variable
$_SESSION['user'] = $user;

// Jump to secured page
header('Location: site.php');
}
else {

// Jump to login page
header('Location: index.php');
}
?>
alright, now the page acts like it does something but doesnt jump to the site and stays at the index page when I know the values are correct
any other ideas?
I echo the $sql and I get Resouce id #3

Re: mysql_num_row issue

Posted: Thu Feb 03, 2011 11:44 pm
by gooney0
I'd narrow it down a bit. By adding some:

print "DEBUG";

lines. This will help make sure where you are in your code.

Example:

if (mysql_num_rows($loggedin) == 1) {
//Set session
print "DEBUG login ok";
header('Location: site.php');
} else {
print "DEBUG no login";
header('Location: index.php');
}


My guess is you're not actually getting 1 row from your query.