www.webdeveloper.com
Results 1 to 7 of 7

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

  1. #1
    Join Date
    Oct 2007
    Posts
    126

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

    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:

    PHP Code:
    $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

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,148
    You might want to check the result of the execute:
    PHP Code:
    $result mysqli_stmt_execute($stmt);
    if(
    $result == false)
    {
       exit(
    "Query failed: " mysqli_stmt_error($stmt));

    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Oct 2007
    Posts
    126
    Quote Originally Posted by NogDog View Post
    You might want to check the result of the execute:
    PHP Code:
    $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:

    PHP Code:
    $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????


  4. #4
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,148
    Are you sure there's a match in the database for the value you're using for $user ?

    You could try changing this:
    PHP Code:
      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:
    PHP Code:
    $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.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  5. #5
    Join Date
    Oct 2007
    Posts
    126
    Quote Originally Posted by NogDog View Post
    Are you sure there's a match in the database for the value you're using for $user ?

    You could try changing this:
    PHP Code:
      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:
    PHP Code:
    $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:
    PHP Code:
    $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.
    Last edited by cannon303; 11-14-2008 at 06:59 AM.

  6. #6
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,148
    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 Code:
    <?php
    ini_set
    ('display_errors'1);
    error_reporting(E_ALL);
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  7. #7
    Join Date
    Nov 2012
    Posts
    1
    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!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles