Issue with db2_bind_param passing in string parameters

Question

I'm currently having an issue with running a query that requires passing in string parameters. Normally I would circumvent this by placing said strings directly into the query itself but I want to start applying proper security protocols and best practices by sanitizing inputs.

Here is the query:

$newAddr->query = "insert into sampleSchema.SampleCustomerAddresses (customer_number, address_id, addr_line1, addr_line2, city, state, zipcode)
values (?,?,?,?,?,?,?)";

My parameters are being passed in like this:

$newAddr->parameters = [$CUSTOMER, $addrId, $addrL1, $addrL2, $city, $state, $zip];
$newAddr->performDBCall();

The code that handles the parameter binding is this:

if (count($this->parameters) != 0){
        foreach ($this->parameters as $param) {
            db2_bind_param($dbStatement, $i, 'param', DB2_PARAM_IN);
            $i++;
        }
    }

Attempting to run this query from the application yields a character in cast argument not valid error.

The question is, how do I pass string parameters via db2_bind_param? Or must I do separate sanitizing then add the parameters directly into the query?

Answer 1

After some investigation, I ended up finding the root cause. In my original parameters loop, I had used a foreach loop and assigning the $this->parameters to $param. What I did not know is that the binding of the parameters actually occurred right before execution. Therefore I had something like this:

db2_bind_param($dbStatement, 1, "param", DB2_PARAM_IN);
db2_bind_param($dbStatement, 2, "param", DB2_PARAM_IN);
db2_bind_param($dbStatement, 3, "param", DB2_PARAM_IN);
db2_bind_param($dbStatement, 4, "param", DB2_PARAM_IN);

I had expected it to be bound right away then the reference would be clear to use for the next binding.

Therefore, I replaced that section with the following:

$numOfParams = count($this->parameters);
    if ($numOfParams != 0){
        for ($i = 0; $i < $numOfParams; $i++){
            $param = 'param'.$i;
            ${$param} = $this->parameters[$i];
            $k = $i + 1;
            db2_bind_param($dbStatement, $k, "param{$i}", DB2_PARAM_IN);
        }
    }

This allowed me to create temporary variable references with their respective string names for the life of the function. This ended up resolving the other issue with all parameters being duplicates of the final bound value which I discovered a while after experimenting with this problem.

Hopefully this may be of help to someone else in the future with a similar problem.