Outils pour utilisateurs

Outils du site


php:pdo_sqlite_named_parameters_bug_workaround

PDO SQLite named parameters bug workaround

The PDO SQLite back-end driver of php has a nasty bug with named parameters which has not been corrected since 2006, and nobody seems to care (bug mentioned here, detailed here). It can make certain requests to fail with the following error:

Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 25 bind or column index out of range' in ...

There are only two workaround this bug: Use positional parameters (?) instead of named parameters (:param) or use the following workaround I wrote. This is neither brilliant nor perfect, but it does the job. It simply replaces the named parameters with their value.

function sqlite_computequery($query,$params)
{
    $matches = array();
    preg_match_all ('/\:\w+/',$query,$matches,PREG_OFFSET_CAPTURE); // Return the offsets of patterns found.
    $newquery='';
    $from=0; // Current offset
    foreach($matches[0] as $item)
    {
        $param=substr($item[0],1);   // Name of parameter
        $offset=$item[1];            // Offset in query
        if (isset($params[$param]))
            { $value = str_replace('"','""',$params[$param]); } // Value to replace with.
        else
            { $value=''; }
        $value='"'.$value.'"';
        $newquery=$newquery.substr($query,$from,$offset-$from).$value;
        $from = $offset+strlen($param)+1;
    }
    $newquery=$newquery.substr($query,$from);
    return $newquery;
}

Example:

$myquery = 'insert into mytable (name,phone) values (:name,:phone)';
$myparams = array( 'name'=>'John', 'phone'=>'12345');
print sqlite_computequery($myquery,$myparams);

which displays:

insert into mytable (name,phone) values ("John","12345")

So instead of doing:

$myquery = 'insert into mytable (name,phone) values (:name,:phone)';
$myparams = array( 'name'=>'John', 'phone'=>'12345');
$q = $db->prepare($myquery);
$q->execute($myparams);

Do:

$myquery = 'insert into mytable (name,phone) values (:name,:phone)';
$myparams = array( 'name'=>'John', 'phone'=>'12345');
$q = $db->prepare(sqlite_computequery($myquery,$myparams));
$q->execute();

This function will probably have to be tweaked to handle numerical values and such, but it works.

Discussion

qwerty, 2013/03/05 19:06

Bonjour, Existe il une classe hérité de PDO intégrant cette fonction permettant d'utiliser MYSQL ou SQLITE sans devoir à chaque fois doubler les requêtes ?

php/pdo_sqlite_named_parameters_bug_workaround.txt · Dernière modification: 2014/07/12 13:26 (modification externe)