I am using Codeigniter 4 and when using the "?" placeholder for the prepared statements, everything works perfectly fine. But I am preferring to use the named bindings as there are too many entry, but it throws out an error code 500: "The number of variables must match the number of parameters in the prepared statement". Please help.

$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]);

CodePudding user response：

TL;DR CI4's prepared statements only support positional placeholders. This is due to the way in which the execute() method accepts parameters; it expects a variable-length argument list and not an associative array.

use CodeIgniter\Database\Query;

$pQuery = $db->prepare(static function ($db) {
    $sql = 'INSERT INTO tbl_posts (...) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)';

    return (new Query($db))->setQuery($sql);
});

// The order of arguments must match the placeholder order
$result = $pQuery->execute(
    $post['content'],
    $author_id,
    $post['display_image'],
    $post['video'],
    $post['posted_anonymously'],
    $post['foreign_image'],
    $post['foreign_title'],
    $post['foreign_url'],
    $post['foreign_description'],
    $date_created
);

A probably worse alternative

The API supports a much simpler query format with named bindings that relies on escaping values instead of binding them. However, unlike PDO named bindings, each name in the query MUST be surrounded by colons.

$sql = 'INSERT ... VALUES (:content:, :author_id:, ...)';
$db->query($sql, [
    'content'   => $post['content'],
    'author_id' => $author_id,
    // etc
]);

Opinion time

This approach is sub-optimal and tries to scare its potential users away with some optimisation warnings that are surely premature.

Instead, take heed of the warnings from security professionals. From the OWASP SQL Injection Prevention Cheat Sheet...

Defense Option 4: Escaping All User-Supplied Input

This technique should only be used as a last resort, when none of the above are feasible. Input validation is probably a better choice as this methodology is frail compared to other defenses and we cannot guarantee it will prevent all SQL Injection in all situations.

CodePudding user response：

First of all, thank you to the guys who answered. I came up with a solution in case anybody wants to use named bindings for prepared statements in Codeigniter 4 specifically version 4.1.9 (also tested on v4.2).

I used PDO prepared statements instead of CI4 Prepared Query.

declare the use of PDO on the top of your model just after the namespace

use PDO;

then connect to your database

$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "db_name";
$conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);

then do your query

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $conn->prepare('INSERT INTO tbl_name (column1, column2, column3) VALUES (:value1, :value2, :value3)');

$stmt->bindParam(':value1', $value1);
$stmt->bindParam(':value2', $value2);
$stmt->bindParam(':value3', $value3);

$value1 = 'value of value1';
$value2 = 'value of value2';
$value3 = 'value of value3';

$results = $stmt->execute();