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

Btw, you shouldn't catch errors to report them. Instead, let PHP to handle it. In other words, just get rid of try catch. — Your Common Sense, Apr 14 '16 at 11:20

score 2 · Accepted Answer · answered Apr 14 '16 at 11:16

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.

Funny, I thought that's what I said. – Buttle Butkus Apr 14 '16 at 21:12 — Buttle Butkus, Apr 14 '16 at 21:12

score 1 · Answer 2 · edited May 23 '17 at 12:31

1

As long as those values are being handled by PDO parameterized queries then it will escape any attempted injections.

https://stackoverflow.com/a/134138

If you aren't using PDO, then you need to make sure all data is correctly escaped, which is a lot of work.

To see what query was actually executed by the MySQL server, you can follow these instructions to set up the general log: https://stackoverflow.com/a/2413308

edited May 23 '17 at 12:31

Community

1
1

answered Apr 14 '16 at 10:51

Buttle Butkus

9,206
13
79
120

This doesn't answer the question asked. – Your Common Sense Apr 14 '16 at 11:10
I thought it did. You are using a parameterized query so you should be fine. But if you want to see what happened with your query you can look at the second link I included. – Buttle Butkus Apr 14 '16 at 11:20

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

2 Answers2