Best way to INSERT many values in mysqli?

Question

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?

But will it really gonna be faster, if I put I loop on the execute? — Roman Holzner, Mar 01 '13 at 02:00

score 39 · Accepted Answer · edited Oct 31 '21 at 17:47

39

You should be able to greatly increase the speed by putting your inserts inside a transaction. You can also move your prepare and bind statements outside of your loop.

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

$mysqli->query("START TRANSACTION");
foreach ($array as $one) {
    $stmt->execute();
}
$stmt->close();
$mysqli->query("COMMIT");

I tested this code with 10,000 iterations on my web server.

Without transaction: 226 seconds. With transaction: 2 seconds. Or a two order of magnitude speed increase, at least for that test.

edited Oct 31 '21 at 17:47

Dharman

30,962
25
85
135

answered Mar 01 '13 at 02:47

Dan Metheus

1,418
9
16

1

a magic: `SET GLOBAL innodb_flush_log_at_trx_commit = 0;` then test w/o transaction again ;-) – Your Common Sense Mar 01 '13 at 13:03
@YourCommonSense That does reduce the time down to 2 seconds without a transaction, although based on the docs for that setting it seems like it shouldn't be identical to the speed of a transaction, is not the default, and may not be super safe. Am I reading that wrong (or is there another question that goes into this?) – Dan Metheus Mar 01 '13 at 13:39
@YourCommonSense Also thank you for that setting, for my purposes losing a second's worth of transactions isn't a big risk and it has sped up several things on my server since very few apps are explicitly using transactions it seems. – Dan Metheus Mar 01 '13 at 13:52
Don't forget to set it permanent then. this setting indeed [slows down writes with innodb](http://www.mysqlperformanceblog.com/2010/02/28/maximal-write-througput-in-mysql/). – Your Common Sense Mar 01 '13 at 14:34
@DanMetheus can you please add a version for multiple column? – Vixed Feb 07 '17 at 16:59

Explosion Pills · Answer 2 · 2014-01-15T19:45:31.983

6

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();

edited Jan 15 '14 at 19:45

answered Mar 01 '13 at 02:07

Explosion Pills

188,624
52
326
405

What about not using prepared statement, and insert the values in the SQL-Query directly, whitout binding and using mysql_real_escape_string? – Roman Holzner Mar 01 '13 at 02:09
I'm sorry^^ I'm just thinking about the insert speed, if the values are inserted at once compared to many executions. – Roman Holzner Mar 01 '13 at 02:12
@Copy Devil: the performance-related questions should be accompanied with measurements and requirements. What are the current performance requirements and what is the performance of the most straightforward (well designed and readable) solution (the one from this answer)? A note: "as fast as possible" isn't a requirement – zerkms Mar 01 '13 at 02:13
Any benefits you get from increased speed in doing one single query are completely irrelevant compared to the security benefit of doing parametrized queries. – Mike Mar 01 '13 at 02:13
1

I agree with @Mike. `mysql_real_escape_string` won't be as secure, and you specified security as a top concern. Nothing beats parameterizing the queries – Explosion Pills Mar 01 '13 at 02:15
1

There are **no security benefit** of doing parametrized queries. properly formatted query is as secure as prepared one. As long as you're adding **only strings** to the query, mysql_real_escape_string is okay. The **only** problem with this honest function is developers who are trying to use it to format values of different types, for wich it's completely useless – Your Common Sense Mar 01 '13 at 03:09
1

Your bind doesn't need to be in the foreach loop. – Dan Metheus Mar 02 '13 at 19:56
@DanMetheus how you figure? – Explosion Pills Mar 02 '13 at 20:00
2

It seemed counter-intuitive to me too, but see Example #3 on the PHP Prepared Statements section http://www.php.net/manual/en/mysqli.quickstart.prepared-statements.php of the PHP manual. Also I tested it out on my own web server and you can prepare the bind even before you actually initialize the variable it will be ultimately using. – Dan Metheus Mar 03 '13 at 00:51

score 2 · Answer 3 · answered Mar 01 '13 at 02:15

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);
}

answered Mar 01 '13 at 02:15

Mark Ormston

1,836
9
13

1

And how vulnerable is real_escape_string? I always used prepared statements so far. – Roman Holzner Mar 01 '13 at 02:16
Prepared statements with strings essentially do exactly the same thing as real_escape_string. The difference is that it is generally a lot more code and easier to make mistakes using real_escape_string. – Mark Ormston Mar 01 '13 at 02:16
So? I'm a little confused from the comments of the answers now. I'm not sure I accepted the right answer^^ Which of the two answers is faster? And is real_escape really as secure as prepared statements? – Roman Holzner Mar 01 '13 at 02:19
1

I can tell you that my method with the big INSERT as the only back and forth you do with the server will be significantly faster than doing 2000+ inserts separately. As for security, I am unaware of a properly done escaped string being less secure in any case than a prepare, it is just much easier to make mistakes. If anybody knows better, please feel free to comment! – Mark Ormston Mar 01 '13 at 02:21
1

You COULD also get the best of both worlds and use one single query as a prepared statement binding the values dynamically. – Mike Mar 01 '13 at 02:42
For speed (which seems to be an important part of the Question), such a "batch insert" can be upwards of 10 times as fast as inserting individual rows. – Rick James Nov 27 '21 at 16:20

score -2 · Answer 4 · answered May 09 '18 at 19:17

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.

Best way to INSERT many values in mysqli?

4 Answers4

Linked

Related