SQLite3Stmt: add an optional array parameter to execute() method, like in PDOStatement

[bohwaz]

This change adds a new array parameter to $stmt->execute() to be able to quickly pass parameters values, without having to use bindValue method.

This is to improve the usability of the SQLite3 extension API and motivate people to use parameterized values for security reasons.

For convenience, the array is treated as if it starts at index 1 instead of index zero.

Usage is the same as in PDO:

$db = new SQLite3(':memory:');
$st = $db->prepare('SELECT ?;');
$r = $st->execute(['test']);
var_dump($r->fetchArray());

BC-break: none.

[cmb69]

I think this makes sense, but I wonder whether there should be a way to specify the types of the binding (SQLITE3_INTEGER etc.) Maybe nested arrays? Likely more an issue for other databases which are strictly typed.

[bohwaz]

I think this makes sense, but I wonder whether there should be a way to specify the types of the binding (SQLITE3_INTEGER etc.) Maybe nested arrays? Likely more an issue for other databases which are strictly typed.

This seems quite unintuitive, it's better to use bindValue for that. Also, the SQLite3 extension is already deducing the SQLite type from the PHP type :)

So [42, null, 'string'] will be treated as INTEGER, NULL, and TEXT :)

So you can just cast a string to an int for example if you want to have it stored as INTEGER.

[PiotrZ5]

Can we mimic how execute_query works in mysqli or sqlsrv_query in sqlite3 world to achieve one-liner sql-injection safety?
https://www.php.net/manual/en/mysqli.execute-query.php
https://www.php.net/manual/en/function.sqlsrv-query.php

$db = new SQLite3(':memory:');
$st = $db->execute_query('SELECT ?,?,?',['a','b','c']);

or even extend query() and exec() functions with parameters array

$st = $db->query('SELECT ?,?,?',['a','b','c']);
$st = $db->exec('UPDATE MyTable set col1=?, col2=? where col3=?',['a','b','c']);

[PiotrZ5]

I think this makes sense, but I wonder whether there should be a way to specify the types of the binding (SQLITE3_INTEGER etc.) Maybe nested arrays? Likely more an issue for other databases which are strictly typed.

You can define your table using strict mode to achieve type safety so type hints are not needed anymore.

[kocsismate]

it would be useful to have tests for zero (when the array is empty) and multiple parameters too in order to test the if and the for