Jump to content

MySQL Erroneously Returning Empty Result Sets


XenoPhyre

Recommended Posts

 

 

MySQL is incorrectly returning empty result sets in response to SELECT queries from

a login script, which is seen below. MySQL Server Version I use is 5.0.41-community-nt

 

if($row['Username'] != $username)
{
echo "
<table border='0' style='-moz-opacity:1.00;filter:alpha(opacity=100);font:12px Arial'>
<tr><td>
<b><font color='red'>Username not found. Please try again.</font></b></td></tr>
</table><br><br>
";
$_SESSION['signedin'] = 0;
}

if($row['Username'] == $username)
{

if($row['PasswordHash'] != $passwordhash)
{
echo "
<table border='0' style='-moz-opacity:1.00;filter:alpha(opacity=100);font:12px Arial'>
<tr><td>
<b><font color='red'>Password is invalid. Please try again.</font></b></td></tr>
</table>";
$_SESSION['signedin'] = 0;
}

if($row['PasswordHash'] == $passwordhash)
{
$_SESSION['signedin'] = 1;
$_SESSION['username'] = $row['Username'];
$_SESSION['reputation'] = $row['TotalThumbsUp'] / ($row['TotalThumbsUp'] + $row['TotalThumbsDown']);
$_SESSION['friends'] = explode("#",$row['FriendsList']);



}
}
}

 

To test the script, I used the signup script to enter a record. Username: XenoPhyre, Password: ************.

 

When using the login script, MySQL returns no result, therefore the script will return a

"Username not found" error back to the user.

 

->MySQL Server Version: 5.0.41-community-nt

 

->When diagnosing the variables:

  Testing the variable $row with mysql_num_rows() returns 0.

  Testing the variable $_POST['username'] returns the correct data ("XenoPhyre").

 

->The same problem occurs when trying to manually bring up the record via PhpMyAdmin

  using an SQL Query on the database:

 

   SELECT * FROM vm_userdb WHERE Username = 'XenoPhyre'

  MySQL still returns empty result set.

 

->Dropping the database then rebuilding it still does not fix the problem. If it may be of any

  help, this was the SQL query used to build the database:

 

$sql = "CREATE DATABASE PassportSvc";
$sql2 = "CREATE TABLE vm_userdb(
Username varchar(30),
PasswordHash varchar(30),
EMailAddress text,
UniqueID varchar(30),
YOB varchar(,
AccountCreationDate varchar(17),
UserLevel bigint(9),
AvatarContent mediumblob,
OriginalMachine text,
AuthorizedMachines text,
Games text,
Medals text,
TotalThumbsUp bigint(9),
TotalThumbsDown bigint(9),
FirstName text,
LastName text,
Gender int(1),
AKA text,
PersonalDescription text,
Location text,
ContactInformation text,
Notebook text,
DownloadSalvage text,
SecretQuestion varchar(150),
SecretAnswer text,
PaymentOptionData text,
StoreCredit bigint(9),
AdminRemarks text,
FreezeReason text,
BanReason text,
FriendsList text)";

 

I reckon this might be a MySQL bug...

 

Link to comment
Share on other sites

Sorry, I think the first code block in my post contains the wrong info. Here's the

correct info:

 

if($_POST['action'] == "login")
{
$sqlquery = "SELECT * FROM vm_userdb WHERE Username ='" . $_POST['username'] . "'";
$result = mysql_query($sqlquery);
$row = mysql_fetch_assoc($result);
echo mysql_num_rows($result);
echo $_POST['username'];
echo $result;
echo $row;

if($row['Username'] != $username)
{
echo "
<table border='0' style='-moz-opacity:1.00;filter:alpha(opacity=100);font:12px Arial'>
<tr><td>
<b><font color='red'>Username not found. Please try again.</font></b></td></tr>
</table><br><br>
";
$_SESSION['signedin'] = 0;
}

if($row['Username'] == $username)
{

if($row['PasswordHash'] != $passwordhash)
{
echo "
<table border='0' style='-moz-opacity:1.00;filter:alpha(opacity=100);font:12px Arial'>
<tr><td>
<b><font color='red'>Password is invalid. Please try again.</font></b></td></tr>
</table>";
$_SESSION['signedin'] = 0;
}

if($row['PasswordHash'] == $passwordhash)
{
$_SESSION['signedin'] = 1;
$_SESSION['username'] = $row['Username'];
$_SESSION['reputation'] = $row['TotalThumbsUp'] / ($row['TotalThumbsUp'] + $row['TotalThumbsDown']);
$_SESSION['friends'] = explode("#",$row['FriendsList']);



}
}
}

 

 

Link to comment
Share on other sites

It would be great if that were the case, but unfortunately it isn't. A matching record is right there

and its as if MySQL is ignoring it completely. Heck, I even have a screenshot to prove it.

 

 

http://img292.imageshack.us/img292/3272/problempc9.png

 

 

I am connected to the correct database, and there should be records that match the request.

From the screenshot it will seem that SELECT queries without a WHERE will work perfectly

 

SELECT * FROM `vm_userdb`

 

While the problem in question will occur if you are looking for something specific using WHERE

 

SELECT * FROM `vm_userdb` WHERE Username = 'XenoPhyre'

 

As seen here:

http://img150.imageshack.us/img150/7492/problem2wi3.png

 

See? I'm not crazy.  ::)

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.