PDO Bind parameters without having to list :var in query

Question

I'm creating an abstraction for my Database class.

One method is used for calling procedures, it takes in the proc name and a variable array.

The issue is that typically with PDO you have to list the variables in the query (EXEC procName :var1, :var2, :var3) then you can bind variables to those.

I want to be able to bind variables in an array, in order, without having to list the names of the vars, or the amount of variables in the query as shown in parenthesis above.

Here is how it is done typically:

$query = $database->prepare("EXEC procName :var1, :var2"); //I must list the amount and names of vars
$query->execute(array(":var1" => $var1, ":var2" => $var2);

Here is how I want it done :

$query = $database->prepare("EXEC procName"); //I don't have to list the vars here, which will allow for a dynamic amount to be passed in (very reusable)
$query->execute($arrayOfVars); //Binding an array of arguments to the proc

However, that code above will not work. Is there a way to do this?

How do you expect for code to work? Where should values from `$arrayOfVars` be added in a query? — u_mulder, Jul 19 '17 at 12:59
@u_mulder In the order that they are in. The first variable in the array would be bound as the first parameter in the procedure. — Wyatt, Jul 19 '17 at 13:01
It is possible but you must watch for SQL injections. See my article [An SQL injection against which prepared statements won't help](https://phpdelusions.net/pdo/sql_injection_example) — Your Common Sense, Jul 19 '17 at 13:01
@YourCommonSense I didn't know injections were possible with stored procedures. — Wyatt, Jul 19 '17 at 13:02

score 0 · Answer 1 · answered Jul 19 '17 at 13:19

For example I have function getCountry

mysql> call getCountry('ua');
+----+------+
| id | name |
+----+------+
|  3 | ua   |
+----+------+

And I use next code

$pdo = new PDO('mysql:host=localhost;port=3306;dbname=test', 'root');
$s = $pdo->prepare('CALL getCountry(:p1)');
$params = [':p1' => 'ua'];
$s->execute($params);
$result = $s->fetchAll(PDO::FETCH_ASSOC);
var_export($result);

which provides for me next result:

array (
  0 => 
  array (
    'id' => '3',
    'name' => 'ua',
  ),
)

I hope you can do the same...

PDO Bind parameters without having to list :var in query

1 Answers1