Deprecated: Assigning the return value of new by reference is deprecated in /home/devmorgan/devmorgan.com/blog/wp-settings.php on line 520

Deprecated: Assigning the return value of new by reference is deprecated in /home/devmorgan/devmorgan.com/blog/wp-settings.php on line 535

Deprecated: Assigning the return value of new by reference is deprecated in /home/devmorgan/devmorgan.com/blog/wp-settings.php on line 542

Deprecated: Assigning the return value of new by reference is deprecated in /home/devmorgan/devmorgan.com/blog/wp-settings.php on line 578

Deprecated: Function set_magic_quotes_runtime() is deprecated in /home/devmorgan/devmorgan.com/blog/wp-settings.php on line 18

Strict Standards: Declaration of Walker_Page::start_lvl() should be compatible with Walker::start_lvl(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/classes.php on line 1199

Strict Standards: Declaration of Walker_Page::end_lvl() should be compatible with Walker::end_lvl(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/classes.php on line 1199

Strict Standards: Declaration of Walker_Page::start_el() should be compatible with Walker::start_el(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/classes.php on line 1199

Strict Standards: Declaration of Walker_Page::end_el() should be compatible with Walker::end_el(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/classes.php on line 1199

Strict Standards: Declaration of Walker_PageDropdown::start_el() should be compatible with Walker::start_el(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/classes.php on line 1244

Strict Standards: Declaration of Walker_Category::start_lvl() should be compatible with Walker::start_lvl(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/classes.php on line 1391

Strict Standards: Declaration of Walker_Category::end_lvl() should be compatible with Walker::end_lvl(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/classes.php on line 1391

Strict Standards: Declaration of Walker_Category::start_el() should be compatible with Walker::start_el(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/classes.php on line 1391

Strict Standards: Declaration of Walker_Category::end_el() should be compatible with Walker::end_el(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/classes.php on line 1391

Strict Standards: Declaration of Walker_CategoryDropdown::start_el() should be compatible with Walker::start_el(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/classes.php on line 1442

Strict Standards: Redefining already defined constructor for class wpdb in /home/devmorgan/devmorgan.com/blog/wp-includes/wp-db.php on line 306

Strict Standards: Redefining already defined constructor for class WP_Object_Cache in /home/devmorgan/devmorgan.com/blog/wp-includes/cache.php on line 431

Strict Standards: Declaration of Walker_Comment::start_lvl() should be compatible with Walker::start_lvl(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/comment-template.php on line 0

Strict Standards: Declaration of Walker_Comment::end_lvl() should be compatible with Walker::end_lvl(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/comment-template.php on line 0

Strict Standards: Declaration of Walker_Comment::start_el() should be compatible with Walker::start_el(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/comment-template.php on line 0

Strict Standards: Declaration of Walker_Comment::end_el() should be compatible with Walker::end_el(&$output) in /home/devmorgan/devmorgan.com/blog/wp-includes/comment-template.php on line 0

Strict Standards: Redefining already defined constructor for class WP_Dependencies in /home/devmorgan/devmorgan.com/blog/wp-includes/class.wp-dependencies.php on line 31

Strict Standards: Redefining already defined constructor for class WP_Http in /home/devmorgan/devmorgan.com/blog/wp-includes/http.php on line 61
devmorgan.com » DyDL - Part 3: Dynamic Binding with mysqli in PHP
Home > coding, data, technology > DyDL - Part 3: Dynamic Binding with mysqli in PHP

DyDL - Part 3: Dynamic Binding with mysqli in PHP

March 27th, 2009

del.icio.us Reddit Slashdot Digg Facebook StumbleUpon

MagicDB is the codename for the Dynamic Data Layer that I built to help me with my much larger project, viagra 100mg visit this site my fabled “Capstone” for my Masters of Science Degree. First a couple technical details:

  • MagicDB currently runs on PHP 5.x.
  • MagicDB currently only interfaces with MySQL.
  • MagicDB is built using an Object-Oriented paradigm.
  • MagicDB uses PHP’s mysqli engine.

Understanding the technical bits will explain why some decisions have to be made. PHP is used pragmatically for myself, generic since that is the language I used to build my capstone with. It’s free. That helps. MySQL is also free, more about and one of the most common companions with PHP. The mysqli engine is used primarily because it provides the support for prepared statements. Using a prepared statement allows us to curb SQL injection attacks, which addresses one of our main concerns with security.

MagicDB has only a couple rules that need to be followed.

  1. Data Objects (PHP) must follow specific naming requirements.
  2. Data Objects (PHP) and Data Tables (MySQL) must follow specific structural requirements.

Naming Requirements are actually pretty simple. All data objects used with MagicDB need to follow the pattern [Prefix]TableName. The prefix can be whatever the developer wants. For my capstone project (codename: TWiG), I used the prefix “TWiGdb_”. This means for a table in my database named “Entry”, I have a corresponding data objected with the class name of TWiGdb_Entry.

Structural Requirements are a little more in-depth. Originally, the structures required to be identical. This meant that for every column in the database, the data object should have a column for that as well. I improved upon this by adding a column to every database table (but not the data object) called “exist”. This column is a flag to show if the row was removed from the database, it has a default value of true. This allows us to “remove” data, without deleting it (although functionality is built to actually DELETE data), in case we require that data later.

Another structural requirement is that the first column in the data object MUST be the primary key for the table. In the table, the first column must be the primary key as well. This allows us to issue UPDATE commands easily. As a convenience, a constructor with all the attributes as parameters is also provided.

The final requirement is that each attribute of the data object MUST be using the same exact name as the corresponding column in the table.

For example the table User would be described like this:
The corresponding data object would look like this:
One we can build the structure of the database, and the corresponding data objects, we can begin to discuss how the DyDL works. MagicDB itself really has a simple interface to use.

In the next few posts we’ll discuss each of these methods, and talk about some of the private methods that power the implementation.
So, treatment before I sit back and talk about all of the actual methods on the MagicDB object, emergency we need to talk about a problem that is specific to PHP and the mysqli extension.

Now, the basic idea I had was to build something that could generate prepared statements dynamically. This will (if done correctly) help protect against SQL injection, and ideally help speed up a few frequently run queries (as long as the SQL structure is always consistent). We need to select mysqli because it has the mysqli_stmt::prepare ( string $query ) function.

Ok, so, now we come to the binding parts of the process… and here is where it gets sticky. We are presented with these two methods:

mysqli_stmt::bind_param ( string $types , mixed &$var1 [, mixed &$... ] )
mysqli_stmt::bind_result
( mixed &$var1 [, mixed &$... ] )

This presents us with a problem. Notice how both methods (which are required for prepared statements) allow for multiple parameters. This is handy — wait.. really.. it’s never handy. I really don’t know why they designed it this way. In fact.. Damn those PHP people. OK.. so maybe I do know why,  but it gets in to reference/value discussion.. so.. lets move on and just figure out a way to fix it.

In an ideal world, for being dynamic, the functions should be designed something more like this:

mysqli_stmt::bind_param ( string $typesarray &$params )
mysqli_stmt::bind_result
array &$results )

Instantly this becomes a lot better. Since the parameters/results are stored as an array, they can be dynamically generated and of any length.  To address this concern, we need to tackle both functions a little bit differently. First off, the bind_param function:

I wrote a simple private function that is included in MagicDB that will accept a mysqli_stmt object, and a list of parameters. From it, the function will call the bind_param method with the proper values and return the statement object.

Now, lets discuss the major concern with this code. The data types of the parameter array that is passed in are used for truth. So, as a developer you need to be careful. This will not cause any security risks, but it may reject your parameter if the wrong data type is used. This is a known concern, but one that I am not worried about since I know to pass an int when I need an int. The beauty of this code is in the last couple lines. I use the php variable-variable-name functionality to generate a dynamic variable reference. I call each variable $bind#, and then assign the parameter to it. I then can use the & symbol to add the variable by reference to the array. Using  the PHP function call_user_func_array, we can get around the multiple parameter problem by simple passing an object reference, a method name, and an array of parameters.

Now for the binding of the result. This code isn’t stored in a function, although it could be. It’s stored in my “get” method on the MagicDB object. Some variables are out of scope, but here is the snip.

So here, without getting into too much detail about how the get() function works, we want to return an object of the type that $object is.  The object should have all the values populated with the results of the database. We bind the variables in the same manner that we bound the parameters in the previous function.  Where it gets fancy is in the use of reflection. We get the type of the object, and pass it into what is called a ReflectionClass.

This will generate a generic class of the type we specify. We must then instantiate the object by calling newInstanceArgs() method. This allows us to pass an array with the parameters for the constructor. This is why the constructor is required in all of the data objects. We do this for each iteration of the fetch, and return the array of objects.

Sexy eh?

Next up we talk about the API and how the MagicDB functions actually work.

dave coding, data, technology

  1. drensky
    April 1st, 2009 at 19:46 | #1

    Wait.. no April Fool’s Day hoaxes this year from you Dave?!?

  2. June 12th, 2009 at 03:22 | #2

    I really love this article. When will Part 4 be out?

  3. March 19th, 2010 at 14:37 | #3

    I am reading my field names and data from a csv file, and my code inserts this into a mysql db. I need the prepared statement to do the insert. I have 155 fields. Many of the data values are null. In the case of null values, will this code assign the types correctly?

    Are prepared statements worth the trouble?

    I dont think so, i have only seen ugly work arounds to get about this parameter and type passing issues. Code can fail if we have null values and types are assigned based on these, making it completely unreliable.

    given this one is forced to use regular statements rather than prepared statements. In this case the question becomes, how does one minimize the threat of sql injection attacks without using prepared statements.

    Any ideas?

    or suggestions will be much appreciated….

  4. Paul
    May 7th, 2010 at 06:34 | #4

    I am personally having trouble getting this to work. I have managed to get bind params to work, but the bind results just will not give me what I need. If I run this it works:

    mysqli_bind_result($stmt, $page_id, $page_name);

    while(mysqli_stmt_fetch($stmt))
    {
    echo $page_name;
    }

    but when I try it dynamically I have no luck:

    $rows[] = &$page_id;
    $rows[] = &$page_name;
    call_user_func_array(array($stmt, ‘bind_result’), $rows);

    while(mysqli_stmt_fetch($stmt))
    {
    echo $page_name;
    }

    I would apprciate any help to resolve this.

    Thanks Paul.

  1. No trackbacks yet.