php - 具有k/v对和未知数的PDO？

我有一个关于PDO与数据库对话的问题，
我熟悉的例子是：

$data = array('Cathy', '9 Dark and Twisty Road', 'Cardiff');

$STH = $DBH->("INSERT INTO folks (name, addr, city) values (?, ?, ?);
$STH->execute($data);

但是，如果我们有一个k/v对，它会是一样的吗？阿拉尔

$data = array('one'=>'Cathy', 'two'=>'9 Dark and Twisty Road', 'three'=>'Cardiff');

$STH = $DBH->("INSERT INTO folks (?, ?, ?) values (?, ?, ?);
$STH->execute($data);

如果我们的价值无法确定呢？

$data = array(range(0, rand(1,99));

$STH = $DBH->("INSERT INTO folks (/* how would you put stuff here? */) values (/* how would you put stuff here? */);
$STH->execute($data);

这让我更加困惑……
有人能告诉我上面两个如何处理k/v对和未知计数吗？
非常感谢

最佳答案

准备好的语句只处理文本，不处理标识符。因此，您需要用填充（并正确转义）的标识符构造sql语句。
不过，正确地转义文本是很棘手的。pdo不提供文字转义的方法，mysql的文字转义方法（使用`）与其他数据库和ansi-sql标准完全不同。See this question for more detail and for workarounds
如果我们简化了转义标识符的问题，您可以使用这样的解决方案：

// assuming mysql semantics
function escape_sql_identifier($ident) {
    if (preg_match('/[\x00`\\]/', $ident)) {
        throw UnexpectedValueException("SQL identifier cannot have backticks, nulls, or backslashes: {$ident}");
    }
    return '`'.$ident.'`';
}

// returns a prepared statement and the positional parameter values
function prepareinsert(PDO $pdo, $table, $assoc) {
    $params = array_values($assoc);
    $literals = array_map('escape_sql_identifier', array_keys($assoc));
    $sqltmpl = "INSERT INTO %s (%s) VALUES (%s)";
    $sql = sprintf($sqltmpl, escape_sql_identifier($table), implode(',',$literals), implode(',', array_fill(0,count($literals),'?'));
    return array($pdo->prepare($sql), $params);
}

function prefixkeys($arr) {
    $prefixed = array();
    for ($arr as $k=>$v) {
        $prefixed[':'.$k] = $v;
    }
    return $prefixed;
}

// returns a prepared statement with named parameters
// this is less safe because the parameter names (keys) may be vulnerable to sql injection
// In both circumstances make sure you do not trust column names given through user input!
function prepareinsert_named(PDO $pdo, $table, $assoc) {
    $params = prefixkeys($assoc);
    $literals = array_map('escape_sql_identifier', array_keys($assoc));
    $sqltmpl = "INSERT INTO %s (%s) VALUES (%s)";
    $sql = sprintf($sqltmpl, escape_sql_identifier($table), implode(',',$literals), implode(', ', array_keys($params)));
    return array($pdo->prepare($sql), $params);
}