How to replace all occurencies of char with elements from array?

1
votes

I have following string:

$sql = sql_prepare("UPDATE `test` SET `item1` = ?, `item2` = ?, `item3` = ?", array(50, 55, 60));

I need to create "sql_prepare" function that will match all occurencies of '?' char and replace with elements from array, so final sql will be:

UPDATE `test` SET `item1` = 50, `item2` = 55, `item3` = 60

How can I do that?

2
phpmysqlreplace
Why don you use real prepated statements instead? php.net/manual/en/mysqli.prepare.php – KingCrunch
@KingCrunch because they are slow and inconvenient to use and insufficient to protect from an injection? – Your Common Sense
@donkeyKong: Why reinvent the wheel, when there's plenty of database classes both built in (mysqli, PDO) and third party (mdb2, zend_db, etc)? – GordonM
@Col: Sharapnel: Can you back that statement up with some facts please? (because if there are flaws in PHP's prepared statements and the amount of protection they offer against SQL injection I'd genuinely like to know) – GordonM
Maybe I found a solution: function sql_prepare($sql, $params) { $sql = preg_replace('/\?/e', 'escape(array_shift($params))', $sql); return $sql; } – donkeykong

2 Answers

0
votes

Why try to fake a prepared statement with the legacy mysql_* API, when both mysqli and PDO implement real prepared statements?

PDO example:

if ($prepped = $pdo -> prepare ('UPDATE `test` SET `item1` = ?, `item2` = ?, `item3` = ?'))
{
    $res = $prepped -> execute (array (50, 55 ,60));
}
-1
votes

for the such a simple replacement you can use sprintf format:

$sql = vsprintf("UPDATE `test` SET `item1` = %d, `item2` = %d, `item3` = %d", 
                 array(50, 55, 60));

however, for the real life usage, it is better to make placeholders of different types

here is the code from my db class

private function prepareQuery($args)
{
    $raw = $query = array_shift($args);
    preg_match_all('~(\?[a-z?])~',$query,$m,PREG_OFFSET_CAPTURE);
    $pholders = $m[1];
    $count = 0;
    foreach ($pholders as $i => $p)
    {
        if ($p[0] != '??')
        {
             $count++;
        }
    }
    if ( $count != count($args) )
    {
        throw new E_DB_MySQL_parser("Number of args (".count($args).") doesn't match number of placeholders ($count) in [$raw]");
    }
    $shift  = 0;
    $qmarks = 0;
    foreach ($pholders as $i => $p)
    {
        $pholder = $p[0];
        $offset  = $p[1] + $shift;
        if ($pholder != '??')
        {
            $value   = $args[$i-$qmarks];
        }
        switch ($pholder)
        {
            case '?n':
                $value = $this->escapeIdent($value);
                break;
            case '?s':
                $value = $this->escapeString($value);
                break;
            case '?i':
                $value = $this->escapeInt($value);
                break;
            case '?a':
                $value = $this->createIN($value);
                break;
            case '?u':
                $value = $this->createSET($value);
                break;
            case '??':
                $value = '?';
                $qmarks++;
                break;
            default:
                throw new E_DB_MySQL_parser("Unknown placeholder type ($pholder) in [$raw]");
        }
        $query = substr_replace($query,$value,$offset,2);
        $shift+= strlen($value) - strlen($pholder);
    }
    $this->lastquery = $query;
    return $query;
}

I have to admit that escaping ? marks solution is not hat elegant but that's what I've got so far.