SQL num_row returns 0 even a row exist

Question

Here is my php script.. first it insert a row of data to the database. Then it takes that row again (in the same script). But now it returns 0 rows. However the row which was entered is exist in the database.

And also there are two rows which was created when I created the database. if I write a query to get that old rows, It returns values without problem. And also serverID and UserID work well.

$conn = new mysqli($sever, $uname, $pass, $dbname);
if ($conn->connect_error) {
  die("Internal Server Error!");
}

//add new row
$un = $_POST['uname'] ;
$ue =  $_POST['uemail'];
$up = $_POST['upass'];
$sql = "INSERT INTO TBuser (username, email, password, serverID) 
  VALUES('" . $un . "',' " . $ue . "',' " . $up . "', '1')";
$conn->query($sql);

if ($conn->query($sql) === FALSE) {
  die("Internal Server Error! Ex000105 0 <br>" .
                        mysqli_error($conn) . '<br>'. $sql);
}

$sql = "SELECT * FROM TBuser WHERE email LIKE '" . $ue . "'";

$results = $conn->query($sql);
if ($results->num_rows == 0) {
  die("Internal Server Error! Ex000105 1". 
  mysqli_error($conn) . '<br>'.       sql . '<br>' 
  . mysqli_error  ($conn) . '<br>'. $results->num_rows );     

 }

And here is my table. First two rows are created by another php script when the database create. Third row is created by above script.

mysqli_error($conn) did not return any error message.

Array ( [userID] => 1 [username] => namindu [email] => namindu@live.com [password] => oh!mygod [serverID] => 1 ) 
Array ( [userID] => 2 [username] => kamal [email] => namindus8@live.com [password] => oh!mygod [serverID] => 1 ) 
Array ( [userID] => 56 [username] => Namindu [email] => nn@h.com [password] => k [serverID] => 1 )

Thanks for helping.

It is very important that you _**never** store plaintext passwords._ Have a look at [`password_hash()`](http://docs.php.net/manual/en/function.password-hash.php) and [`password_verify()`](http://docs.php.net/manual/en/function.password-verify.php) instead. Also, please use [prepared statements](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) to avoid [SQL injection](http://docs.php.net/manual/en/function.password-verify.php). — ChrisGPT was on strike, Mar 30 '18 at 01:46
You have an extra space in your insert value(s). And no wildcards `%` in the search means "no matchie matchie". But you could have avoid all that and other issues by using prepared statements.... — ArtisticPhoenix, Mar 30 '18 at 01:51
Thank you for telling me that password storing method. Really I didn't know about that @Chris — Namindu Ranatunga, Apr 01 '18 at 10:46

ArtisticPhoenix · Answer 1 · 2018-04-01T10:51:31.090

Do you see it?

$sql = "INSERT INTO TBuser (username, email, password, serverID)VALUES('" . $un . "',' " . $ue . "',' " . $up . "', '1')";

How about now?

 ' " . $ue . "'

Space{email} and then no wildcards in your search LIKE '" . $ue . "'. You do however have some SQLInjection vulnerabilities.

But just to be clear:

   " $ue" != "$ue"   /// ie. email = ' name@example.com';

If you had done LIKE '%".$ue."' you wouldn't have noticed it all. You also have another one here ' ".$up."' for the password, pretty sure that will create a few issues too. But you could fix all this by using "Prepared Statements", because with prepared statements you just put ? in for mysqli, and no quotes....

P.S. it's ok if you didn't see it. Took me a few minutes before I went "ah ha"

SQL num_row returns 0 even a row exist

1 Answers1