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
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

So, before I sit back and talk about all of the actual methods on the MagicDB object, 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.