PHP and BindParam: Pointers are tricky

• Jorge Castro
• 11/08/2020 16:26:05

PHP, pointers are tricky.

Let's say the next code, where colint is a column of the type integer, and colstring is a column of type varchar.

$sql='insert into table(colint,colstring) values(:colint,:colstring)';
$stmt=$conn1->prepare($sql);
$params=['colint'=20
         ,'colstring'=>'hello'];
foreach ($param as $k => $v) {
    $stmt->bindParam($k, $v); // where $stmt is a PdoStatement
}

And it fails. ?

In this case, it tries to bind the value "hello" in the column "colint" instead of the value 20.

Why?

It is the definition of the method bindParam

public PDOStatement::bindParam ($parameter` , &$variable , $data_type = PDO::PARAM_STR, $length ) : bool

The second argument is referential (the symbol &). It means a pointer.

Pointers

If we do the next code

$a=20;
$b=$a;

Then we are storing the value 20 (the value of $a) in a space of memory called "$b". While

$a=20;
$b=&$a;

This is different. $b points to the same space of memory than $a. Both have the value 20 but it uses a single space of memory. Pointers are powerful but they are really tricky.

// without pointers
$a=20;
$b=$a;
$a=30;
echo $b; // 20

// with pointers
$a=20;
$b=$a;
$a=30;
echo $b; // 30 (because $a and $b uses the same space of memory)

Tracing the code

$params=['colint'=20,'colstring'=>'hello'];
foreach ($param as $k => $v) {
    $stmt->bindParam($k, $v); // where $stmt is a PdoStatement
}

The first loop, it values

// $k='colint' and $v=20
$stmt->bindParam($k, $v);

But we are not assigning the value 20 but it points to where is the variable $v.

// first loop $k='colint' and $v=20
$stmt->bindParam($k, POINTER TO --> $v);
// second loop $k='colstring' $v='hello'
$stmt->bindParam($k, POINTER TO --> $v);

So the second argument to both bindParam() are always the same $v (and PHP uses the same space of memory) and it is always the same space of memory

So, both first loop points to $v='hello' (the last value of $v)

Solution

$params=['colint'=20,'colstring'=>'hello'];
foreach ($param as $k => $v) {
    $stmt->bindParam($k, $param[$k]); 
}
// first loop
// $stmt->bindParam($k, POINTER TO --> $param[0]);
// second loop
// $stmt->bindParam($k, POINTER TO --> $param[1]);

---