PDO更新准备好的语句不起作用 [英] PDO update prepared statement not working

问题描述

pdo-bindparam-into-one-statement

我对PDO非常陌生，我想我迷路了. 我想要的是使用相同的变量进行插入和更新.

I'm very new to PDO and I think I'm lost.. What I wanted to was using same variables for insert and update both..

 function pdoSet($fields, &$values, $source = array()){
    $set = '';
    $values = array();
    if(!$source) $source = &$_POST;
    foreach($fields as $field){
        if(isset($source[$field])){
            $set .= " $field =:$field, ";
            $values[$field] = $source[$field];
        }
    }
    return substr($set, 0, -2);
}

$fields = array(
    'name'
    , 'part'
    , 'tel'
    , 'email'
    , 'title'
    , 'contents'
);

if(!$idx){
    $fields[] = 'reg_date';
    $values[] = 'now()';
    $st = $pdo -> prepare("insert into qna_board set ".pdoSet($fields, $values));
}else{
    $st = $pdo -> prepare("update qna_board set ".pdoSet($fields, $values)." where idx = :idx");
    $st ->bindParam(':idx', $idx);
}

$st->execute($values);

已成功插入，但未成功更新. 当我使用$ idx而不是:idx时，它起作用了. 你能告诉我问题是什么吗?

It was successful for insert, but not for update. When I used $idx instead of :idx it worked.. Could you tell me what the problem is?

推荐答案

您可以将bind参数或传递给execute.您不能一次执行全部操作，当您将任何绑定参数传递给execute时，PDO都会丢弃任何绑定参数.因此，您的idx在执行查询时不受约束.最简单的解决方法:

You can either bind parameters or pass them to execute. You cannot do both at once, PDO will discard any bound parameters when you pass any to execute. So your idx isn't bound when executing the query. Easiest fix:

$st->execute($values + compact('idx'));

通过接受来自$_POST BTW的原始字段名称，您可以进行良好的旧SQL注入.

You're opening yourself up to good old SQL injection by accepting raw field names from $_POST BTW.

另外，顺便说一句:

join(', ', array_map(
    function ($field) { return "`$field` = :$field"; },
    $fields
))

比.= '.., '和substr聪明.

这篇关于PDO更新准备好的语句不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！