Theory of prepared statements with MySQLI

[j.smith1981]

I am just finding it confusing the following statements in MySQLi regarding what they actually mean as suich.

 

Say I had the following commands using the MySQLi class:

<?php
$query = $my_mysqli->prepare("INSERT INTO zend_dwoo_books_author (author, book) VALUES (?, ?)");

$query->bind_param('ss', $author, $book);

?>

 

What does the basically prepare and the bind_param actually do?

 

I mean I know it saves the query into memory but what happens to the prepare statement after it's done the bind_param() function?

[Jumpy09]

Prepared statements were intended for when you need to repeat queries, such as inserting a list of cities in a database or pulling out a list of items based on different parameters.  Through serendipity I assume, the fact of sending the Query and the Parameters separately also provided a work around to prevent SQL Injection.

 

From what I figure the Prepared Statement ( The Query ) is sent first and then the parameters are sent.  Since the query went first, MySQL now knows what to do with the Parameters.

 

So you do :

<?php
$books = array ( "Blah1" , "Blah2" , "Blah3" ) ;
$authors = array ( "Dr. Blah" , "Dr. Blaaaah" , "Dr. Bllllaaaahhhhh" ) ;
$parameters = array ( ) ;

foreach ( $books as $b )
{
    $parameters [ $b ] [ "Book" ] = $books [ $b ] ;
    $parameters [ $b ] [ "Author" ] = $authors [ $b ] ;
}

$query = $my_mysqli->prepare("INSERT INTO zend_dwoo_books_author (author, book) VALUES (?, ?)");

foreach ( $parameters as $p )
{
    $query->bind_param('ss', $parameters [ $p ] [ "Author" ] , $parameters [ $p ] [ "Book" ] );
    $query->execute();
}

?>

 

I'm not sure how much in optimization it would do for such a small amount of loops, but when you get to thousands of loops you can save some overhead time using prepared statements.  Of course if you use multiple-rows insert syntax you are only sending 1 Insert Query so it does run much quicker.

 

Since the Author and the Book are sent separately from the statement itself any single quotes or any other special characters won't have an effect on the query itself.

 

In a Top Secret world think about multiple Carriers being sent out with different pieces to the bigger puzzle.  If a single Carrier was to become compromised it wouldn't have any effect on the Grand Scheme.

 

Thinking about this in a Job Environment, the prepared statement would be your Boss telling you to do something a certain way.  Now that you know which way to do it, you don't have to be told to do it again and anytime it comes up you know exactly how to do it without instruction.  The functionality was included in MySQL 4.1 and Above, but the old MySQL Extension didn't cover Prepared Statements, so the MySQLi Extension was created to allow it.

 

I do believe the fact it prevented SQL Injection was a nice bonus because of what it was originally implemented to do, but some people typically suggest to always use Prepared Statements for both Optimization and Security.  I think Optimization and Security can be equally obtained in normal statements as well as prepared statements, but then it just comes down to personal preference.

 

If I am wrong in my understanding, someone can correct me if needed.  I do believe that I have the basic concept, but I'm not quite 100% sure how to explain .. so sorry for my analogies if they don't quite make sense.  Now I am not arguing which extension is best, both have their ups and downs.

 

[j.smith1981]

I don't know if this is the correct place to ask this but concerning MySQLi on it's own, I am trying to follow through this tutorial here: http://devzone.zend.com/article/687-Using-extmysqli-Part-II---Extending-mysqli

 

The part I am trying to complete (but failing at this very moment in time) is: 'Really Making an Exception'

 

I have written this code (some of it done by me some of it done by on the tutorial):

 

<?php

class Connect_Exception extends Exception {

}

class Query_Exception extends Exception {

}

function add_single_quotes($arg) {
  return "'" . addcslashes($arg, "'\\") . "'"; // adds slashes if you put in a slash?
}

class My_MySQLi extends mysqli {

  function __construct() { // start of constructor method
  
    $args = func_get_args();
    eval("parent::__construct(" . join(',', array_map('add_single_quotes', $args)) . ");");

if(mysqli_connect_error()) { // throw an exception to the connect void function, if an error occured in connecting to server!
      throw new Connect_Exception(mysqli_connect_error(), mysqli_connect_errno());
}

  } // end of constructor method
  
  function query($sql) {
  
    $result = parent::query($sql);

if(mysqli_error($this)) {
      throw new Query_Exception(mysqli_error($this), mysqli_errno($this));
}
return $result;
  }
}

try {
  
  $my = new example_mysqli('localhost', 'user', 'mypassword', 'test');
  $result = $my->query("SELECT NOW()");
  var_dump($result->fetch_row());
  
} catch(Connect_Exception $error) {
  
  echo "<p>An error occued, look for the debug message:</p>";
  echo "<pre>";
  print_r($error);
  echo "</pre>";
  
} catch (Query_Exception $error) {

  echo "<p>An error occued, look for the debug message:</p>";
  echo "<pre>";
  print_r($error);
  echo "</pre>";
  
} catch (Exception $error) {
  
  echo "<p>An error occued, look for the debug message:</p>";
  echo "<pre>";
  print_r($error);
  echo "</pre>";
  
}

 

I keep getting this error in the site apache web server logs:

PHP  1. {main}()

 

The rest of course is just the file but it says 0 on the call to main (which is a C type language meaning it is not even executing), have I missed something out or just made a complete mess of this?

 

It does not show th full code so I have been over this about 25 times and still can't work out what I have done.

 

Any helps much appreciated, if someone feels like they should move this post please do so I don't mind.

 

I look forward to any replies and help,

Jeremy.

 

PS Thank you for going through the last one so literally with me, makes 100% logical sense really!