prepare() vs query() mysqli

Question

I am trying to understand the difference between mysqli's query() and prepare(). I have code like below, and I would like to get the same results from both. However the prepare() does not work as expected. why?

  // this works

  if ($query = $this->db->query("SELECT html FROM static_pages WHERE page = 'cities'")) {

     $result = $query->fetch_row();
     echo $result[0];

  $query->close();

  }

  //this does not work
  //result is empty

     $cities = 'cities';

     $stmt = $this->db->prepare("SELECT html FROM static_pages WHERE page = ?");
     $stmt -> bind_param("s", $cities);
     $stmt->execute();
     $stmt->bind_result($result);
     $stmt->fetch();
     echo $result;
     $stmt->close();

     $this->db->close();

these are my server configs as requested:

OS

Vista 64bit / PHP Version 5.2.9

mysqli

MysqlI Support enabled

Client API library version 5.0.51a

Client API header version 5.0.51a

MYSQLI_SOCKET /tmp/mysql.sock

Directive Local Value Master Value

mysqli.default_host no value no value

mysqli.default_port 3306 3306

mysqli.default_pw no value no value

mysqli.default_socket no value no value

mysqli.default_user no value no value

mysqli.max_links Unlimited Unlimited

mysqli.reconnect Off Off

Answer 1

Should be echo $result; not echo $results;

Answer 2

Can you try $stmt->store_result(); between $stmt->execute(); and $stmt->bind_result($result); ?

Answer 3

Any of the mysqli_* functions can fail. In this case the return value is false and the error/errno properties of the mysqli or mysqli_stmt object contains more information about the error. The script has to test each and every return value and react appropriately on error condition (e.g. it doesn't make sense to prepare the statement if the connection failed).

<?php
$foo = new Foo;
$foo->init();
$foo->bar();
class Foo {
  public function bar() {
    $cities = 'cities';  
    $stmt = $this->db->prepare("SELECT html FROM soTest WHERE page = ?");
    if ( !$stmt ) {
      echo "prepare failed\n";
      echo "error: ", $this->db->error, "\n";
      return;
    }

    $rc = $stmt->bind_param("s", $cities);
    if ( !$rc ) {
      echo "bind_param failed\n";
      echo "error: ", $stmt->error, "\n";
      return;
    }

    $rc=$stmt->execute();
    if ( !$rc ) {
      echo "execute failed\n";
      echo "error: ", $stmt->error, "\n";
      return;
    }

    $rc = $stmt->bind_result($result);
    if ( !$rc ) {
      echo "bind_result failed\n";
      echo "error: ", $stmt->error, "\n";
      return;
    }

    $rc = $stmt->fetch();
    if ( !$rc ) {
      echo "no such record\n";
    }
    else {
      echo 'result: ', $result, "\n";
    }
    $stmt->close();
  }

  public function init() {
    $this->db = new mysqli('localhost', 'localonly', 'localonly', 'test');
    if ($this->db->connect_error) {
      die('connection failed: ' . $this->db->connect_error);
    }
    $rc = $this->db->query('CREATE TEMPORARY TABLE
      soTest (id int auto_increment, html varchar(16), page varchar(16),primary key(id))'
    );
    if ( !$rc ) {  die('error: '.$this->db->error); }
    $rc = $this->db->query("INSERT INTO soTest (html,page) VALUES ('htmlFoo','foo'),('htmlCities','cities')");
    if ( !$rc ) {  die('error: '.$this->db->error); }
  }
}

Keep CWE-209: Information Exposure Through an Error Message in mind. Printing the actual error message in my example script is only for testing. And you might use a slightly more sophisticated error handling than just die().