DyDL - Part 3: Dynamic Binding with mysqli in PHP
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:
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 $types , array &$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.
Next up we talk about the API and how the MagicDB functions actually work.