Can anyone help me with this simple PHP/MySQLI connection script?

[cannon303]

Hi I was using a mysql connection to my database and all worked fine, however I've been following a book "Pro PHP Security" by Apress and have decided to follow their chapter on connecting with mysqli instead as it provides added security against sql injection. So I've copied their script and changed the values etc:

$user = "DSAFDSG";
$connection = mysqli_connect('localhost', 'username', 'password','database');

if (!$connection) exit('Could not connect: ' . mysqli_connect_error());
$stmt = mysqli_prepare ($connection, "SELECT user FROM user_table WHERE user = ?");
 
 if ($stmt){
  mysqli_stmt_bind_param($stmt, "s",$user);
  mysqli_stmt_execute($stmt);
  mysqli_stmt_bind_result($stmt,$dbuser);
  if (mysqli_stmt_fetch($stmt)){
      print "Found a result $user = $dbuser";
    }else{
      print 'Sorry no records found';
    }
    
    mysqli_stmt_close ($stmt);
}    
mysqli_close ($connection); 

The problem that i'm having is that i get "Sorry no records found" when it should successfully pull up a record.

I know it is connecting to the database as it doesn't throw up the "Could not connect.." error and I have tried forcing an error to test this and doesn't seem to be any problem there.

My hosting says there is nothing wrong with mysqli support on their server but will not help me further as to whether anything is wrong with this script. Can anyone see if there is an error in it?


Thanks

[NogDog]

You might want to check the result of the execute:

$result = mysqli_stmt_execute($stmt);
if($result == false)
{
   exit("Query failed: " . mysqli_stmt_error($stmt));
} 

[cannon303]

You might want to check the result of the execute:

$result = mysqli_stmt_execute($stmt);
if($result == false)
{
   exit("Query failed: " . mysqli_stmt_error($stmt));
} 

Well that's interesting, i might have put your script in the wrong place as it still doesn't throw up an error. Here is my altered script:

$user = "DSAFDSG";
$connection = mysqli_connect('localhost', 'username', 'password','database');

if (!$connection) exit('Could not connect: ' . mysqli_connect_error());

$stmt = mysqli_prepare ($connection, "SELECT user_name FROM db_user WHERE user_name = ?");
 
 if ($stmt){
  mysqli_stmt_bind_param($stmt, "s",$user);
  $result = mysqli_stmt_execute($stmt);
  if($result == false)
{
   exit("Query failed: " . mysqli_stmt_error($stmt));
} 
  mysqli_stmt_bind_result($stmt,$dbuser);
  if (mysqli_stmt_fetch($stmt)){
      print "Found a result $user = $dbuser";
    }else{
      print 'Sorry no records found';
    }
    
    mysqli_stmt_close ($stmt);
}    
mysqli_close ($connection); 

I don't get "Query failed...", just still get "Sorry no records found."

I read in my book (and i don't know if i've got this right) that although I may have php5+ and mysql4.1+ installed, I should have the old mysql extension commented out for the newer mysqli extension as the old extension only has patchy support for mysqli. I wonder if this might be the case? I'm on shared hosting and can't get to do that easily and besides if i comment it out will some of my older mysql commends stop working????

[NogDog]

Are you sure there's a match in the database for the value you're using for $user ?

You could try changing this:

  if (mysqli_stmt_fetch($stmt)){
      print "Found a result $user = $dbuser";
    }else{
      print 'Sorry no records found';
    } 

To the following, in order to see whether it thinks there was an error or just no matches:

$result = mysqli_stmt_fetch($stmt))
if($result === true)
{
   print "Found a result $user = $dbuser";
}
elseif($result === null)
{
   print 'Sorry no records found that matched for user ' . $user;
}
elseif($result === false)
{
   print 'The fetch operation returned an error: ' . mysql_stmt_error($stmt);
}
else
{
   die('Uh-oh! Things are REALLY fubar!');
} 

Note the use of the "===" ("is identical") operator in the comparisions.

[cannon303]

Are you sure there's a match in the database for the value you're using for $user ?

You could try changing this:

  if (mysqli_stmt_fetch($stmt)){
      print "Found a result $user = $dbuser";
    }else{
      print 'Sorry no records found';
    } 

To the following, in order to see whether it thinks there was an error or just no matches:

$result = mysqli_stmt_fetch($stmt))
if($result === true)
{
   print "Found a result $user = $dbuser";
}
elseif($result === null)
{
   print 'Sorry no records found that matched for user ' . $user;
}
elseif($result === false)
{
   print 'The fetch operation returned an error: ' . mysql_stmt_error($stmt);
}
else
{
   die('Uh-oh! Things are REALLY fubar!');
} 

Note the use of the "===" ("is identical") operator in the comparisions.

result = "Sorry no records found that matched for user DSAFDSG" and yes there is a record of DSAFDSG in the db. When i change the "?" in:

$stmt = mysqli_prepare ($connection, "SELECT user FROM user_table WHERE user = ?"); 
//to
$stmt = mysqli_prepare ($connection, "SELECT user FROM user_table WHERE user = $user"); 

It finds the record, but obviously bypasses some of the security.

I think that mysqli support isn't set up correctly on the server and my host believes the error is with my scripting and wont look into it.

[NogDog]

I'm not seeing anything obviously wrong. The only other suggestion I have is to make sure PHP is telling you anything it knows that is wrong by adding the following to the start of the script:

<?php
ini_set('display_errors', 1);
error_reporting(E_ALL);

[RayvenUK]

Did anyone ever solve this?
I have exactly the same issue.
I've outputted the status of every function, double checked the parameters, etc.
All of the functions return TRUE until I get to the mysqli_stmt_fetch() function which returns FALSE. The data is there in the database, and if I use "WHERE field".$param instead of "WHERE field=?" then I get the result, but this is unsafe code and I don't want to go down that route. All error() functions are returning that there are no errors.
This is driving me insane!