mysql_num_rows warning

[jyan]

Hi!

I'm doing a small website with some mysql and php, and have difficulties with doing a login site. I have the form sending the username and password, and this site should check them.

Here's the code:

[php]

<?php

session_start();

if(!($connection = mysql_connect("host","user","pass")))
die("Can't connect to database.");
if(!(mysql_select_db("db", $connection)))
die("ERROR ".mysql_errno($connection)."-". mysql_error($connection));

if(isset($_POST["username"]) AND isset($_POST["password"])) {
$uname = $_POST["username"];
$pword = $_POST["password"];

$loginquery = "SELECT * FROM Users WHERE Username = '{$uname}' AND Password = '{$pword}' ORDER BY UserID";
if(!$loginresult = mysql_query($loginquery, $connection))
$error = "No connection.";

if (mysql_num_rows($loginresult) != 1){
$error .= "Username / password error!";
}
else {
$datas = mysql_fetch_assoc($loginresult);
$uid2 = $datas["UserID"];
$uname2 = $datas["Username"];
$pword2 = $datas["Userpassword"];
$artist2 = $datas["Artist"];
$moderator2 = $datas["Moderator"];

$_SESSION["userid"] = $uid2;
$_SESSION["username"] = $uname2;
$_SESSION["password"] = $pword2;
$_SESSION["artist"] = $artist2;
$_SESSION["moderator"] = $moderator2;
}

}
?>
[/php]
This php page is included in every page on the site. Some variables may be named badly, ignore them. Just help me, please?

[rinoa04]

[php]

$loginquery = "SELECT * FROM Users WHERE Username = '{$uname}' AND Password = '{$pword}' ORDER BY UserID";
if(!$loginresult = mysql_query($loginquery, $connection))
$error = "No connection.";

if (mysql_num_rows($loginresult) != 1){
$error .= "Username / password error!";
}
[/php]

You can try to modify the coding above as below. See whether it works or not.

[php]
$loginquery = mysql_query("SELECT * FROM Users WHERE Username = '$uname' AND Password = '$pword' ORDER BY UserID");

if (!$loginquery){$error="Query problem";}

if (mysql_num_rows($loginquery) != 1){
$error .= "Username / password error!";
}
[/php]

[jyan]

No, didn't help. Thanks for trying though.

[jyan]

Still not working. Does anyone have any idea what the problem is? I really should get this done quite soon and thought it would be easy, since I've done a similar site previously and had no problems... Pretty please?

[jyan]

FIXED IT! :mrgreen:

Or actually didn't fix it, just did something else. Here's the code if someone's wondering.

Before:

[php]
$loginquery = "SELECT * FROM Users WHERE Username = '{$uname}' AND Password = '{$pword}' ORDER BY UserID";
if(!$loginresult = mysql_query($loginquery, $connection))
$error = "No connection.";

if (mysql_num_rows($loginresult) != 1){
$error .= "Username / password error!";
}
else {
$datas = mysql_fetch_assoc($loginresult);
$uid2 = $datas["UserID"];
$uname2 = $datas["Username"];
$pword2 = $datas["Userpassword"];
$artist2 = $datas["Artist"];
$moderator2 = $datas["Moderator"];

$_SESSION["userid"] = $uid2;
$_SESSION["username"] = $uname2;
$_SESSION["password"] = $pword2;
$_SESSION["artist"] = $artist2;
$_SESSION["moderator"] = $moderator2;
}
[/php]
After:
[php]
$loginquery = "SELECT * FROM Users ORDER BY UserID";
$loginresult = mysql_query($loginquery);

while($row = mysql_fetch_assoc($loginresult)){
if($row[Username]==$uname && $row[Userpassword]==$pword){
$_SESSION["userid"] = $row[UserID];
$_SESSION["username"] = $row[Username];
$_SESSION["password"] = $row[Userpassword];
$_SESSION["artist"] = $row[Artist];
$_SESSION["moderator"] = $row[Moderator];
}
}
[/php]

[sunflowerz]

Instead of:
[php]
$loginquery = "SELECT * FROM Users WHERE Username = '{$uname}' AND Password = '{$pword}' ORDER BY UserID";

if(!$loginresult = mysql_query($loginquery, $connection))
$error = "No connection.";

if (mysql_num_rows($loginresult) != 1){
$error .= "Username / password error!";
}[/php]

Try this:

[php]
$loginquery = mysql_query("SELECT * FROM Users WHERE Username = '".$uname."' AND Password = '".$pword."' ORDER BY UserID");

if (!$loginquery){$error="Query problem";}

if (mysql_num_rows($loginquery) != 1){
$error .= "Username / password error!";
}[/php]

Or, change the !=1 to <1 as there shouldn't be more than two usernames that are the same.

The other way is to go through phpMyAdmin and paste it into SQL Query. It (usually) tells you the why it won't work, or at least where it thinks the error is.

Thanks for putting up your work around. :)

[itanium7000]

...and why using AND operator, not use && operator?

[jyan]

...and why using AND operator, not use && operator?

Is there a difference?

[Puckdropper]

Yes, && is evaluated first.

From: http://us2.php.net/manual/en/language.operators.php
09-Jun-2004 05:58
of course this should be clear, but i think it has to be mentioned espacially:

AND is not the same like &&

for example:

<?php $a && $b || $c; ?>
is not the same like
<?php $a AND $b || $c; ?>

the first thing is
(a and b) or c

the second
a and (b or c)

'cause || has got a higher priority than and, but less than &&

of course, using always [ && and || ] or [ AND and OR ] would be okay, but than you should at least respect the following:

<?php $a = $b && $c; ?>
<?php $a = $b AND $c; ?>

the first code will set $a to the result of the comparison $b with $c, both have to be true, while the second code line will set $a like $b and THAN - after that - compare the success of this with the value of $c

maybe usefull for some tricky coding and helpfull to prevent bugs :D

greetz, Warhog

The next comment also points out that relying on order of operations is a good way to produce bugs and errors.

[sn4rf3r]

Just a quick thought, you are not filtering any input from the login form. If I happened to put my user name as (delete * from users where 1), all of your user information would dissappear.

I suggest that you run your input data through at least htmlentities, this would convert the perens and astrix to their ascii equivilent.

Also, just semantics, you could put your data into an array and then store that array in the $_SESSION

if (isset($_POST['username']) && !empty($_POST['password'])) {
   $user = array(
      'uname' = htmlentities($_POST['username']),
      'pswd' = htmlentities($_POST['password'])
);
$sql = "SELECT * FROM users where username = '".$user['uname']."' AND password = '".$user['pswd]."'";
$loginquery = mysql_query($sql,$connection);
//row count conditional goes here

while ($row = mysql_fetch_assoc($loginquery)) {
if($row[Username]==$uname && $row[Userpassword]==$pword){
   $user['userid'] = $row['Userid'];
   $user['artist'] = $row['Artist'];
   $user['moderator'] = $row['moderator'];

$_SESSION['user'] = $user;
 }
}

Just one of many ways to do this.

[rawveg]

Personally, I would use something rather more robust than htmlentities. There are php functions specifically for protecting you against XSS attacks, so rather than:-

if (isset($_POST['username']) && !empty($_POST['password'])) {
   $user = array(
      'uname' = htmlentities($_POST['username']),
      'pswd' = htmlentities($_POST['password'])
);
$sql = "SELECT * FROM users where username = '".$user['uname']."' AND password = '".$user['pswd]."'";

Use something like this:-

function xssProtect($var) {
    $retVal = '';
    if (function_exists("mysql_real_escape_string")) {
        $retVal = mysql_real_escape_string($var);
    } else {
        $retVal = mysql_escape_string($var);
    }

    return $retVal;
}

if (    
    (isset($_POST['username'] && $_POST['username'] != "") &&
    (isset($_POST['password']) && $_POST['password'] != "")
   ) {
    $user = xssProtect($_POST['username']);
    $password = xssProtect($_POST['password']);
    $sql = "SELECT * FROM users WHERE username = '$user' AND password = '$password'";

Finally, you should really avoid the use of SQL Reserved words as the names of columns in your tables. PASSWORD is an SQL reserved word, and some versions of MySQL will simply croak if you have a column name like this. If you absolutely *must* have he column called password, then you should refer to it in all SQL using backticks, so your SQL would read:-

$sql = "SELECT * FROM users WHERE username = '$user' AND `password` = '$password'";

Hope this helps

Tim

[sn4rf3r]

@rawveg: agreed, thats why I said "at least htmlentites", they will also need to remember to unescape the data coming from the db or escape the incoming data in order to validate the user. All in all, great advice =]