PHP, PDO, MySQL - Multiple INSERT vulnerable to injection?

Question

In my application (PHP) I am going to request ~3000 rows from an API and insert these in a MySQL table using the PDO driver.

Although the data to be inserted isn't user input, it is out of my hands how the data is handed to me.

Because it is important that the ~3000 inserts go as fast as possible I want to use a multiple insert like so (?,?,?),(?,?,?),....

I was wondering whether doing a multiple insert has effect on the vulnerability for a MySQL injection? Because I 'build' the query using PHP code.

My 'test' code is:

<?php

    class DBCon {
        private static $instance = null;
        private $db;

        private function __construct() {
            $this->db = new PDO('mysql:host=localhost;dbname=test;charset=utf8mb4', 'root', '');
            $this->db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
            $this->db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
        }

        public static function getDB() {
            if (self::$instance === null) {
                self::$instance = new self();
            }
            return self::$instance->db;
        }
    }

    function createItems($array) {
        $sql = 'INSERT INTO `table`(`text`, `int`, `bool`) VALUES ';

        $insertArray = array();
        foreach ($array as $arrayItem) {
            $sql .= '(';
            foreach ($arrayItem as $arrayItemItem) {
                array_push($insertArray, $arrayItemItem);
                $sql .= '?,';
            }
            $sql = rtrim($sql, ',');
            $sql .= '),';
        }
        $sql = rtrim($sql, ',');

        var_dump($sql);
        var_dump($insertArray);

        try {
            $query = DBCon::getDB()->prepare($sql);
            $query->execute($insertArray);
        } catch (PDOException $e) {
            echo '<br/><br/>query failure';
        }
    }

    $array = array(array('a piece of text',123,0),array('a piece of text',123,0));

    createItems($array);

$sql contains:

index.php:36:string 'INSERT INTO `table`(`text`, `int`, `bool`) VALUES (?,?,?),(?,?,?)' (length=65)

$insertArray contains:

index.php:37:
array (size=6)
  0 => string 'a piece of text' (length=15)
  1 => int 123
  2 => int 0
  3 => string 'a piece of text' (length=15)
  4 => int 123
  5 => int 0

Answer 1

I was wondering whether doing a multiple insert has effect on the vulnerability for a MySQL injection? Because I 'build' the query using PHP code.

Well, in the real life we cannot avoid manual query building, thus it's all right to create a query or two dynamically. The only rule you have to follow in this case is all query parts have to be hardcoded in your script.

As long as it's followed, no injection will be ever possible.

In your case all query parts are hardcoded, thus this code is safe.