Recursion in prepared statements

Question

I use PDO and prepare all my statements primarily for security reasons. However, I have part of my code that executes the same statement with different parameters many times, and I thought that this would be where the prepared statements really get prepared. But they really break the code ...

The basic logic of the code is this.

function someFunction($something) { global $pdo; $array = array(); static $handle = null; if (!$handle) { $handle = $pdo->prepare("A STATEMENT WITH :a_param"); } $handle->bindValue(":a_param", $something); if ($handle->execute()) { while ($row = $handle->fetch()) { $array[] = someFunction($row['blah']); } } return $array; } 

It looked good to me, but it lacked a lot of lines. In the end, I realized that the operator descriptor was changed (executed with another parameter), which means that the call to retrieve in the while loop will work only once, then the function will call itself again, and the result set will be changed.

So, I am wondering what is the best way to use prepared PDO instructions in a recursive way.

One way might be to use fetchAll (), but the manual contains a substantial overhead. The thing is to make it more effective.

Another thing I can do is not to repeat the static descriptor, but instead do a new one every time. I believe that since the query string is the same, internally, the MySQL driver will use the prepared statement anyway, so there is only a small overhead for creating a new descriptor for each recursive call. Personally, I think it wins.

Or is there a way to rewrite this?

Answer 1

You cannot insert operator descriptors: you need to close a previously opened descriptor before opening another in one session.

In fact, PDO does this if automatically when a new training is released.

When you call a function recursively:

• Start descriptor highlighted (1)
• The first record is retrieved from (1)
• The function is called recursively. The value (1) is on the recursion stack.
• New descriptor highlighted (2) , invalid (1)
• The first record is retrieved from (2)
• Function returns
• You are trying to get the next entry (1) and fail because it is invalid

Thus, MySQL does not support recursion on its side, and that means you will need to do this on the PHP side using fetchAll .

Answer 2

The real problem is that $handle is static. Static variables are problematic for recursion, when the state should be preserved by a recursive call, and not just for prepared statements. In this case, the recursive call makes a new request, discarding the previous state. PDO::fetchAll really the only option if you want to get one prepared request.

Depending on what the operator is, you can rewrite it to immediately return all the results, and then build a tree.

Answer 3

If you use the same variables (due to pdo bindValue), each time value is the same as the first. So this will be FAIL:

 foreach ($bind_params as $key => $value) { $stmt->bindParam(":$key", $value); } 

result:

 $key[0] = $value[0]; $key[1] = $value[0]; $key[2] = $value[0]; $key[3] = $value[0]; 

So you want to do an ugly trick, then:

  $i = 0; foreach ($bind_params as $key => $value) { $i++; $$i = $value; $stmt->bindParam(":$key", $$i); } 

result:

 $key[0] = $value[0]; $key[1] = $value[1]; $key[2] = $value[2]; $key[3] = $value[3];