====== 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 [[http://www.trirand.net/forum/default.aspx?g=posts&m=3313#post3313|here]], detailed [[http://pecl.php.net/bugs/bug.php?id=7560|here]]). It can make certain requests to fail with the following error:
<code>Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY000]: General error: 25 bind or column index out of range' in ...</code>

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.

<code php>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;
}</code>

Example:

<code php>
$myquery = 'insert into mytable (name,phone) values (:name,:phone)';
$myparams = array( 'name'=>'John', 'phone'=>'12345');
print sqlite_computequery($myquery,$myparams);</code>
which displays:
<code>insert into mytable (name,phone) values ("John","12345")</code>

So instead of doing:

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

Do:

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

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

~~DISCUSSION:closed~~