You will understand how important this method is when you want to bind a variable number of arguments as an array to your prepared statements with MySQLi. That is what happened with me 🙂 Yeah, unfortunately it is not possible to use bind_param in MySQLi with an array. You can’t bind an array 🙁 I encountered this problem while I was trying to cover for some security holes in my new website. There I had a dynamically created sql string which was being passed to the server. I know I know… That is very dangerous, but at that time all I wanted to do was to quickly develop the functionality. Anyways, I thought to myself, best way to avoid SQL injecting attacks was to use parameterized statements instead. They are very handy and quite easy to implement, but it is another story when your input parameters are dynamic.
The thing with the parameterized statements is that they are first sent to the database server and compiled, and the parameters are later bound to the compiled statement. This way nobody can trick the database server and inject any malicious SQL string into your queries. They can input them of course (if you don’t have a client-side mechanism to prevent that, which can always be worked around anyway) but they will be totally harmless because they will never be treated as a part of the actual query to be executed on the database server.
Since I am relatively new to PHP, I tried a couple of methods before finally succeeding with call_user_func_array.
You are reading this post, so I assume you know bind_param method and won’t bother myself trying to explain it.
Method 1 (failed):
I created an array which held the type string and parameter values inside, and the I provided this array as an argument to bind_param like so:
Of course, that lead me to this beautiful error message: Wrong parameter count for mysqli_stmt::bind_param…
Method 2 (failed):
First I built the type string (you know.. the first parameter of bind_param) in a foreach loop. Then in a for loop I created a string for the values to be bound like val1,val2,val3,..
Then I called the bind_param function like so:
Of course, it didn’t work and displayed me this beautiful error message: Number of elements in type definition string doesn’t match number of bind variables…
I am currently using PHP 5.5.12. Actually, I was just hoping maybe bind_param implementation was somehow modified (may be provided with a couple of overloads) to accept these kinds of inputs (arrays or concatenated strings of parameters). That is why I tried method 1 and 2 to quickly see if they worked but, as expected, they did not 🙂
Method 3 (succeeded):
This is were call_user_func_array comes into play. This method provides you with a way to supply your methods with arrays as parameters. This is exactly what you need if you are trying to bind dynamic parameters (arrays) to your prepared statements.
First create a type string (the first parameter). You can do this in a loop by string concatenation. Name it $paramTypes.
Then create an array of parameter values to be bound. You can probably do this in the same loop. Call this $valuesArray.
Now you need to combine the $paramTypes string and elements inside $valuesArray in a single array say $inputArray. Important: You need to pass these to the third array ($inputArray) by reference because with call_user_func_array, array params must be passed by reference. Check out the code below:
$inputArray = &$paramTypes;
$j = count($valuesArray);
$inputArray = &$valuesArray[$i];
You realized the “&” character? That is what we do for passing by reference. Now we can bind this $inputArray to our statement ($stmt) using call_user_func_array as below:
call_user_func_array(array($stmt, 'bind_param'), $inputArray);
Here array($stmt, ‘bind_param’) says “provide $inputArray array as an argument to $stmt->bind_param method”.
Now you can execute your statement.
Maybe I should have used PDO…?
Save the polar bears.