PDO : How to execute a dynamic array of values?

Question

I am trying to execute a dynamic array of values with PDO, here is my PHP code:

protected function insertData($data, $table) {
    $db = $this->getDB();

    $columns = '';
    $params  = '';
    $values  = '';

    // Retrieves columns, their respective value and params.
    $i = 0;
    foreach($data as $col => $val) {
        $i++;
        $columns .= $i != count($data) ? "`$col`, " : "`$col`";
        $params  .= $i != count($data) ? "?, "      : "?";
        $values  .= $i != count($data) ? "'$val', " : "'$val'";
    }

    $query     = "INSERT INTO $table ($columns) VALUES ($params)";
    $prepQuery = $db->prepare($query);
    return $prepQuery->execute([$values]);
}

When I execute this code, I get this error : "Warning: PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in".

I know the issue is that $prepQuery->execute([$values]) actually does $prepQuery->execute(["'val1', 'val2', 'val3'..."]) instead of what I wanna do which is $prepQuery->execute(['val1', 'val2', 'val3'...]) but I don't know how to fix it...

How can I execute a dynamic array of values with PDO? The method is generic.

Thanks in advance, I hope I was clear enough. :/

The wrong algorithm aside, your code is prone to **SQL injection**! — Your Common Sense, Mar 01 '20 at 18:24
@YourCommonSense why does it prone to SQL injection? Btw the insertData() method can only be called if the data is OK (it is verified in another method in my controller). But I have to admit I don't have much knowledge in security and SQL injections so yeah Do you have a better algorithm? Thanks everyone tho — raitxningu, Mar 01 '20 at 18:34
Obviously, because you are adding untreated variables to the SQL query. Just like *any* SQL injection works. — Your Common Sense, Mar 01 '20 at 18:36
@YourCommonSense Oh of course I do it in other files and the SQL query is the final thing that is executed if everything went well before — raitxningu, Mar 01 '20 at 18:39
@iJamesPHP2 lmao thank you indeed that was so obvious I feel so stupid now thank you so much — raitxningu, Mar 01 '20 at 18:41
Other files have nothing to do with this code. It is like to put a bulletproof door to your house and keep all windows with wooden shades. — Your Common Sense, Mar 01 '20 at 18:44
I understand where you're coming from but the thing is I don't see how there could be an SQL injection since no SQL can be executed unless the $data array contains correct values (verified with is_string(), is_numeric(), strlen(), preg_match()... before the method insertData() is called) — raitxningu, Mar 01 '20 at 18:53
I think your common sense means these lines `$values .= $i != count($data) ? "'$val', " : "'$val'"; }` and where you insert values into query. — , Mar 01 '20 at 19:13
@raitxningu are you kidding? What on Earth does is string or strlen to do with SQL injections? And again, you cannot guarantee that all this useless stuff is indeed called. It is not mandatory, like a prepared statement, but entirely on the skill, temper, or mood if the user. And therefore, some day will leak — Your Common Sense, Mar 01 '20 at 22:39

PDO : How to execute a dynamic array of values?

0 Answers0