MySQLi vs PDO Prepared Statements

I want to learn how to use Prepared Statements and have a few questions before I really jump in feet first. I was hoping the crew here could help. I am pretty well versed in PHP 5.0 / OOP…

  1. Should I be using PDO or the MySQLi Extension? Which is better for medium scale e-commerce and social network type projects, taking into account the type and quantity of queries that are performed on those sites?

  2. If MySQLi, how do I get that running on my server, it seems to be turned off by default? I am on mediaTemple, so a basic dedicated virtual UNIX server. I have no idea how to turn it on / install it.

  3. I noticed that Prepared Statements only allow placeholders ‘?’ for values, not for things like table_name and LIMIT, ORDER BY etc… when using Prepared Statements can i still pass these values in like so?

SELECT * FROM $table WHERE color = ? AND size = ? LIMIT $limit;

or will that not work? Is there another way to do that, thinking along the lines of a general CRUD type class I can re-use from project to project.

  1. What is a Stored Procedure in regards to Prepared Statements?

Thanks for any help, I really want to dig deep into Prepared Statements for security reasons.

It won’t work. You see prepared statements work like this…
The client sends a partial query to the SQL server, this query has to be completely minus the data so it can be pre-processed. The client then sends the data, either once or multiple times, the server then executes the pre-processed query with the data.

  1. Neither is better than the other. They are just two different implementations of the same MySQL features. Use whichever fits your style best. I prefer PDO.

  2. Depends. Could be as simple as adding extension=mysqli.so to your php.ini configuration. Or maybe you don’t have the module at all, at which point how you install it depends on your OS (what specific Linux distribution)

  3. Yes. The purpose of the placeholder is to let you bind values for columns, not piece together pieces of SQL like a string. You should be giving MySQL a valid SQL query, with only literal values missing, when you ask it to prepare that query (which is basically asking it to come up with an execution plan for running that query once the values are sent in).

  4. These are not related. Stored procedures are code. You can write entire programs directly against the database, store them and call them later with a query.

Okay, so as to #3, how do I use prepared statements in a more general dbClass where I don’t know the table or LIMIT I am going to use ahead of time? How does that make this code re-usable if I can’t pass these value into it?

Right now I have a dbClass that take a few values like:

public function selectData($table,$where,$sort,$limit) {

// select statement here 

}

How do I go about using a prepared statement in a class like that where the table is unknown until an object is created?

You have to put additional programming layer on top.

Care to elaborate? I know how to get the data into the Class but how do I get into the prepared statement?

You write code that sits on top of PDO or MySQLi that creates your query (minus the data) then sends it on to either extension.


$sql = new SqlStatement;
$sql->select( 'tableOne', 'tableTwo' )
    ->from( 'superTable' )
    ->where( 'tableThree', '=', ':input' )
    ->limit( 10 );

$sql->bind( ':input', $input );

$res = $sql->execute(); # Sent the query and data to PDO or MySQLi here.

new SqlStatement is creating the new db connection with PDO, correct?

For this rudimentary example, yes. It is just building the SQL statement before passing it on to PDO.

I guess I just don’t get how to bring in a dynamic table name, order by or limit when you can’t use placeholders for those.

Here’s a basic Prepared Statement, right?


<?php

$dbtype = "mysql";
$dbhost = "localhost";
$dbname = "test";
$dbuser = "test";
$dbpass = "password";
	 
$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);
	 
$state = "California";

$sql = "SELECT governor, population FROM states WHERE state = ?";
$q = $conn->prepare($sql);
$q->execute(array($state));
 	 
$q->setFetchMode(PDO::FETCH_BOTH);	 

while($r = $q->fetch()){
	  echo $r['governor'] . " " . $r['population'];
	}
	
?> 

That’s great. But say I want to have this in a dbClass that i can re-use for various different queries site-wide. Maybe I want to use it for ‘SELECT capital, president FROM countries WHERE country = ?’

How do I use Prepared Statements (or can’t I?) to effectively do this:

‘SELECT $field1, $field2 FROM $table WHERE $field3 = ?’

Are Prepared Statements not good for this kind of re-usable CRUD type class?

Actually that’s a bit inaccurate. They may be using the same underlying libraries or they may not, depending on how your installation is configured/compiled. Per default they do, however, use mysqlclient, both of them. The other alternative to mysqlclient is mysqlnd, which I wouldn’t recommend to use.

I would say that PDO is more of a standard than mysqli is, so I would suggest that you use this.

That’s what I have been learning all day but I just can’t wrap my head around how to make it re-usable for any query I need to send to it, at least retrieve (SELECT) queries for now.

I seem to have made it work by simply passing variables into the query string, but what’s the point of Prepared Statements if I’m going to have those vulnerable vars coming into the query? I can use mysql_real_escape_string and check var type but Prepared Statements are supposed to do all of that for you, correct?

Is there a cleaner, more secure way to do this?


<?php

$dbtype = "mysql";
$dbhost = "localhost";
$dbname = "test";
$dbuser = "test";
$dbpass = "password";

$conn = new PDO("mysql:host=$dbhost;dbname=$dbname",$dbuser,$dbpass);

$field1 = "governor";
$field2 = "population";
$state = "California";
$table = "states";

$sql = "SELECT $field1, $field2 FROM $table WHERE state = ?";

$q = $conn->prepare($sql);

$q->execute(array($state));

$q->setFetchMode(PDO::FETCH_BOTH);     

while($r = $q->fetch()){

      // this is echo'd in the view so I can hardcode the values 

      echo $r['governor'] . " " . $r['population'];

    }   

?>

Let me be the one that gives you the definitive answer: yes, you can do that, but no, that’s not what prepared statements are made for. Once you get how prepared statements work, you can see why this is. When you prepare a statement, that statement is sent to the database, and the database will then store that statement. It will replace all the placeholders with actual values by the time that you call execute( ). Therefore, the table- and fieldnames must be known before the execute call and you can not use placeholders to dynamically insert the table- or fieldnames in a statement that has already been prepared.

Nevertheless, there is no stopping you from doing simple variable substitution: by writing your query like ‘SELECT ’ . $fieldname . ’ FROM ’ . $tablename’ before preparing the statement, you’ll prepare the statement with the tablename and fieldnames already in place, and that’s perfectly possible.

You’ll have to consider the fact that dynamically inserting the field- and tablenames might be a security risk though: not having security in place might just enable an end-user to view data he’s not supposed to. I think your best bet might just be having a whitelist of table- and fieldnames the dynamic query may contain. On the other hand, if you’re assembling the query only from inside your application with hard-coded string (no user-input), you should have nothing to worry about.

I guess my table name would only come from inside my code. passed into the class, and I am more concerned with the ORDER BY and LIMIT clauses etc., so I can re-use the class, know what I mean? Say I have a link that says paginate the results by either 25, 50 or 100 results per page that the user can use, I’d want to use the same query, not 3 different ones with different LIMIT’s.

It doesn’t seem to work, probably because LIMIT doesn’t have an = sign in it?

Use a variable… LIMIT $number

And make sure that $number is an integer yourself

Yeah, that’s what I was thinking, but since ORDER BY may not be an integer, and mysql_real_escape_string doesn’t stop all injections, it seems like if you could bind it things would be a bit mre secure, that’s kind of why I am learning prepared statements. Plus, everytime I post on a PHP fprum with SQL everyone jumps my ass about not using them LOL. Can I bind the ORDER BY and LIMIT clauses?

Also, right now I am passing the WHERE clause to my Class like this:

$fields = “*”;
$table = ‘posts’;
$where = "post_cats = " . $cat_id . " AND " . $post_date > 1267423200;
$orderby = ‘post_time DESC’;
$limit = $offset . ', ’ . $rowsperpage;
$getPosts = new DBQuery();
$getPosts->setQuery($fields,$table,$where,$orderby,$limit);
$posts = $getPosts->getQuery();

But I know it is sloppy, is there a better way to do this with an array() know that the where clause may have more than one AND, so it would have to loop? I’m not worried about the other clauses getting passed in an array, they are simple, but there WHERE clause could have one to several AND’s, how do I loop thru an array like that in a query string and echo out the array elements?

it would be like:

SELECT $fields FROM $table WHERE (loop thru array elements here);