'Best way to INSERT many values in mysqli?

I'm looking for a SQL-injection-secure technique to insert a lot of rows (ca. 2000) at once with PHP and MySQLi.
I have an array with all the values that have to be include. Currently I'm doing that:

<?php
$array = array("array", "with", "about", "2000", "values");

foreach ($array as $one) 
{
    $query = "INSERT INTO table (link) VALUES ( ?)";
    $stmt = $mysqli->prepare($query);
    $stmt ->bind_param("s", $one);
    $stmt->execute();
    $stmt->close();
}
?>

I tried call_user_func_array(), but it caused a stack overflow.

What is a faster method to do this (like inserting them all at once?), but still secure against SQL injections (like a prepared statement) and stack overflows?

phpmysqlmysqlilinuxvmwarekali-linux


Solution 1:[1]

Trying this again, I don't see why your original code won't work with minor modifications:

$query = "INSERT INTO table (link) VALUES (?)";
$stmt = $mysqli->prepare($query);
$stmt->bind_param("s", $one);

foreach ($array as $one) {
    $stmt->execute();
}
$stmt->close();

Solution 2:[2]

Yes, you can build a single big query manually, with something like:

$query = "";
foreach ($array as $curvalue) {
  if ($query)
    $query .= ",";
  $query .= "('" . $mysqli->real_escape_string($curvalue) . "')";
}
if ($query) {
  $query = "INSERT INTO table (link) VALUES " . $query;
  $mysqli->query($query);
}

Solution 3:[3]

You should first convert your array into a string. Given that it is an array of strings (not a two-dimentional array), you can use the implode function.

Please be aware that each value should be enclosed into parenthesis and properly escaped to ensure a correct INSERT statement and to avoid the risk of an SQL injection. For proper escaping you can use the quote method of the PDOConnection -- assuming you're connecting to MySQL through PDO. To perform this operation on every entry of your array, you can use array_map.

After escaping each value and imploding them into a single string, you need to put them into the INSERT statement. This can be done with sprintf.

Example:

<?php
$connection = new PDO(/*...*/);
$connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$dataToBeSaved = [
    'some',
    'data',
    'with "quotes"',
    'and statements\'); DROP DATABASE facebook_main; --'
];


$connection->query(
    sprintf(
        'INSERT INTO table (link) VALUES %s',
        implode(',',
            // for each entry of the array
            array_map(function($entry) use ($connection) { 
                // escape it and wrap it in parenthesis
                return sprintf('(%s)', $connection->quote($entry));
            }, $dataToBeSaved)
        )
    )
);

Note: depending on the amount of records you're willing to insert into the database, you may want to split them into several INSERT statements.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1
Solution 2 Mark Ormston
Solution 3 G. Kashtanov

Related Questions