mysql query in php always returning false

Question

I am simply trying to call a table and check if the user inputted username maches any in the table, however I am always geting the 'your new here' response. If I put single quotes around $UserName then it will always return true instead.

<html>
<head>
<title>Welcome</title>
</head>
 <body>
 <h1>DunGen</h1>
 <?php 
  $email = $_POST["email"];
  $name = $_POST["name"];

  require_once('config.inc.php');
  $mysqli = new mysqli($database_host, $database_user, $database_pass, $database_name);
  if($mysqli -> connect_error) {
    die('Connect Error ('.$mysqli -> connect_errno.') '.$mysqli -> connect_error);
  }

if($result = $mysqli -> query("SELECT * FROM UserDetails WHERE UserName=$name"))
{
  echo "Welcome back ",$name, "<br>";
}
else
{
echo "Hello ",$name, " you're new here ain't yah? <br>";
$sql = "INSERT INTO UserDetails (UserName, UserEmail) VALUES ('$name','$email')'";
}

if (filter_var($email, FILTER_VALIDATE_EMAIL))
{
    echo " Email adress: ", $email;
}
  else
{
    echo "Invalid Email";
}
  echo "<br>";
 //copied from w3 schools
$conn->close();
?>

</body>
<html>

fixed edit: following the advice given I replaced the if statement with these two lines:

  $result = mysqli_query($mysqli,"SELECT * FROM UserDetails WHERE   UserName='$name'");
 if($result ->num_rows == 0)

simply put I was evaluating weather I had correct SQL, not weather or not it was returning correctly.

Well a username would generally be a string, and string literals in SQL statements should be quoted — Mark Baker
– Mark Baker, Commented Dec 1, 2015 at 15:31
But it's 2015 now, not 2005.... you should be using prepared statements/bind variables to stop anybody from abusing yoru site with SQL injection hacks — Mark Baker
– Mark Baker, Commented Dec 1, 2015 at 15:32
I would reverse the logic. If the INSERT fails, then you know the user exists, and you already know their username. — Strawberry
– Strawberry, Commented Dec 1, 2015 at 15:33
Have you checked the values being sent to this page via the POST request? — Mark Baker
– Mark Baker, Commented Dec 1, 2015 at 15:35
So if $name is a string, why are you not quoting it in the SQL query? If I enter Mark Baker in the name field of your form and submit, SELECT * FROM UserDetails WHERE UserName='Mark Baker' would be valid, SELECT * FROM UserDetails WHERE UserName=Mark Baker would be invalid — Mark Baker
– Mark Baker, Commented Dec 1, 2015 at 15:38

Marc B · Accepted Answer · 2015-12-01 15:39:30Z

2

You are vulnerable to sql injection attacks.
Your query is invalid:

[..snip..]* FROM UserDetails WHERE UserName=$name"))
                                            ^^^^^^

You're generating the literal query

SELECT ... WHERE UserName=Fred

and since your table is HIGHLY unlikely to have a field named Fred, you're getting a query syntax error. And since your error handling is incorrect, you never see the error message that MySQL is trying to show you.

A query which return no rows is NOT an error condition. It's a perfectly valid query, and a perfectly valid result set, which just happens to contain no rows.

Your logic should be

$sql = "....";
$result = mysqli_query($con, $sql) or die(mysqli_error($con));
if($result->num_rows == 0) {
   ... user does not exist ...
} else {
   ... user does exist ...
}

answered Dec 1, 2015 at 15:39

Marc B

362k44 gold badges433 silver badges508 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Benjamin Sealy-Minto Over a year ago

thank you very much, I understand it better and it works, much appriciated

Alex · Accepted Answer · 2015-12-01 15:49:08Z

1

You should definitely use prepared statement functions.

But your current current issue is you missing any kind of fetch function.

So you check :

if($result = $mysqli -> query("SELECT * FROM UserDetails WHERE UserName=$name"))

But it will return true just if your statement is correct and can be executed by mysql. The positive result does not mean you did find a user.

To check that you should fetch the result. So you can:

if($result = $mysqli -> query("SELECT * FROM UserDetails WHERE UserName=$name"))
{
   if ($row = $result->fetch_array(MYSQLI_ASSOC)) {
     echo "Welcome back ",$row['UserName'], "<br>";
   } else {
     echo "Wrong credentials";
   }
}

UPDATE here is how you can rewrite your code using prepared statement practice:

$stmt = $mysqli->prepare('SELECT UserName FROM UserDetails WHERE UserName= ?');
$stmt->bind_param('s', $name);
if($stmt->execute())
{
   $stmt->bind_result($sqlName);
   if ($stmt->fetch()) {
     echo "Welcome back ",$sqlName, "<br>";
   } else {
     echo "Wrong credentials";
   }
}

edited Dec 1, 2015 at 15:49

answered Dec 1, 2015 at 15:40

Alex

17.3k1 gold badge31 silver badges52 bronze badges

2 Comments

Sean Over a year ago

So an unquoted string in the query is not an issue?

Alex Over a year ago

@Sean I will extend my answer. but yes the main problem described in OP when user get always true or always false related to the value he did try to check. but not the bad practices he was following.

Collectives™ on Stack Overflow

mysql query in php always returning false

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related