Codeigniter4命名预处理语句报500错：变量数与参数数不匹配

问题表现 #

使用CodeIgniter 4开发时，?占位符格式的预处理语句可正常执行，改用命名绑定方式传参时抛出500错误，错误提示：

The number of variables must match the number of parameters in the prepared statement

错误原因 #

调用预处理结果的execute()方法传参时，命名绑定的数组键名不需要携带前导冒号:。原代码传入的参数数组所有键都带了:前缀，框架无法匹配SQL中定义的占位符，判定参数与占位符数量不匹配抛出错误。

修复代码 #

移除传入execute()的参数数组所有键名的前导冒号即可，修正后代码：

$db = \Config\Database::connect();
$pQuery = $db->prepare(static function ($db){
    $sql = 'INSERT INTO tbl_posts (content, author_id, display_image, video, posted_anonymously, foreign_image, foreign_title, foreign_url, foreign_description, date_created) VALUES (:content, :author_id, :display_image, :video, :posted_anonymously, :foreign_image, :foreign_title, :foreign_url, :foreign_description, :date_created)';
        return (new Query($db))->setQuery($sql);
});

$author_id = session()->get('my_id');
$date_created = date("Y-m-d H:i:s");
$result = $pQuery->execute([
    "content" => $post['content'],
    "author_id" => $author_id,
    "display_image" => $post['display_image'],
    "video" => $post['video'],
    "posted_anonymously" => $post['posted_anonymously'],
    "foreign_image" => $post['foreign_image'],
    "foreign_title" => $post['foreign_title'],
    "foreign_url" => $post['foreign_url'],
    "foreign_description" => $post['foreign_description'],
    "date_created" => $date_created
]);

注意点 #

• SQL语句内定义命名占位符时必须保留:前缀，仅传参时的数组键不需要加该前缀
• 非预处理复用场景下，直接调用查询对象的execute()方法时支持带:前缀的键名，预处理场景统一使用无冒号键名即可避免匹配错误

内容的提问来源于stack exchange，提问作者Seighth Hellsing