prepared statement for fast mass insert

user_1302716 0
Viewed 558

In a nutshell

Is there a way in Perl to use prepared statements (to prevent SQL injection) to insert 1 million records within less than 2 minutes into a MySQL-table?

In detail

There is an online resource (Wikimedia) from which I want to download a file (dewiktionary-latest-all-titles-in-ns0.gz) that contains almost 1 million titles of articles (each article is a descriptions of a German word in Wiktionary). I want to check this list once a week and then react on new or deleted titles. To do this, I want to automatically download this list once a week and insert it into a database.

Although I trust Wikimedia, you never should trust anything coming from internet too much. So, to prevent SQL injection and other security issues, I always use prepared statements in Perl, do make sure, that the SQL interpreter has no chance to interpret content as code.

Normally I would do this this way:

program 1 

#!/usr/bin/perl -w

use strict;
use warnings;
use LWP::UserAgent;
use DBI;

# DOWNLOAD FROM INTERNET =========================
# create User-Agent:
my $ua = LWP::UserAgent->new;
# read content from Internet
my $response = $ua->get('https://<rest_of_URL>');
# decode content
my $content = $response->decoded_content;

#turn into a list
my @list = split(/\n/,$content);

# STORE IN DATABASE ==============================
# connect with database (create DataBase-Handle):
my $dbh = DBI->connect(
    'DBI:mysql:database=<name_of_DB>;host=localhost',
    '<user>','<password>',
    {mysql_enable_utf8mb4 => 1}
);
# SQL statement
my $SQL = 'INSERT INTO `mytable`(`word`) VALUES(?)';
# prepare statement (create Statement Handle)
my $SH = $dbh->prepare($SQL);
#execute in a loop
foreach my $word (@list) {
    $SH->execute($word);
}
# disconnect from database
$dbh->disconnect;
# end of program
exit(0);

Pay attention to this line (line 27): 

my $SQL = 'INSERT INTO `mytable`(`word`) VALUES(?)';

There is a question-mark as placeholder in the SQL command line. In the next line this SQL command line is prepared (i.e. an prepared statement is created), and in the loop this statement is executed, which means, that each time a new value ($word) will be inserted into the table, without having any chance to execute this value, because the SQL-interpreter doesn't see this value. So, whatever an attacker might write into the file I downloaded, it never will cause a code injection.

But:
This is very slow. The download is done within a few seconds, but the insert-loop is running for more than four hours.

There is a faster solution, it goes like this:

program 2 

# The code above the SQL-Statement is exactly
# the same as in the 1st program
#-------------------------------------------------
# SQL statement
my $SQL = 'INSERT INTO `mytable`(`word`) VALUES ';  # <== NO '?'!
# attach values in a loop
# initiate comma with empty string
my $comma = '';
foreach my $word (@list) {
    # escape escapecharacter
    $word =~ s/\\/\\\\/g;
    # escape quotes
    $word =~ s/'/\\'/g;
    # put the value in quotes and then in brackets, add the comma
    # and then append it to the SQL command string
    $SQL .= $comma."('".$word."')";
    # comma must be a comma
    $comma = ',';
}
# Now prepare this mega-statement
my $SH = $dbh->prepare($SQL);
# and execute it without any parameter
$SH->execute();
# disconnect from database
$dbh->disconnect;
# end of program
exit(0);

(This is simplified, since the SQL statement would become too long to be accepted by MySQL. You need to split it up in sections of about 5000 values and execute them. But this is not important for the problem I'm talking about here.)

This runs very fast. All values (almost 1 million rows in the new table) are inserted within less than 2 minutes, this is more than 100 times faster.

As you can see, I create one big statement, but without placeholders. I write the values directly into the SQL command. I just needed to escape backslashes that would be interpreted as escape characters and single quotation marks that would be interpreted as the end of a string.

But the rest of the values remains unprotected and is visible to the SQL interpreter. A potential attacker might find a way to insert SQL code into the values that will be executed. This can damage my database, or it even might grant the attacker superuser rights. (privilege escalation induced by code injection)

So, here is my question:

Is there a way to use prepared statements like in program 1 even for statements that are dynamically generated like in program 2?

Or is there another possibility to fast and secure insert big amounts of data into a MySQL table?

perl mysql
2 Answers

Your little note in italics is actually quite relevant:

(This is simplified, since the SQL statement would become too long to be accepted by MySQL. You need to split it up in sections of about 5000 values and execute them. But this is not important for the problem I'm talking about here.)

I think your "unprepared statement" (not a real term) approach is faster because you're bulk-loading 5000 records at a time rather than one-by-one, not because it's not a prepared statement.

Try building a prepared statement with 5000 ?s like this:

my $SQL = 'INSERT INTO `mytable`(`word`) VALUES ' . '(?),'x4999 . '(?)';

Then build a list of 5000 words at a time and execute your prepared statement with that. You'll have to deal with the last set of (presumably) less than 5000 words with a second dynamically-generated prepared statement of the appropriate number of words in the last batch.

You could also look into LOAD DATA INFILE for bulk loading.

user_7251630

(This answer is written be the author of the question.)

e.dan brought me to the right idea with his answer, so thank you, e.dan!

Here is the fast solution that uses prepared statements:

# The code above the SQL-Statement is exactly
# the same as in the 1st program in the question
#-------------------------------------------------
# SQL statement
my $SQL = 'INSERT INTO `mytable`(`word`) VALUES ';
# Counter
my $cnt   = 0;
# initiate comma with empty string
my $comma = '';
# An array to store the parameters (This array does the trick!)
my @param = ();
# loop through all words
foreach my $word (@list) {
    # (no escaping needed)
    # attach a question mark in brackets to the query string
    $SQL .= $comma."(?)";
    # and push the value into the parameter-array
    push(@param,$word);
    # next time it must be a comma
    $comma = ',';
    # increment the counter
    $cnt++;
    # limit reached?
    if ($cnt >= 5000) {
        # Yes, limit reached
        # prepare the string with 5000 question marks
        my $SH = $dbh->prepare($SQL);
        # hand over a list of 5000 values and execute the prepared statement
        # (for Perl a comma separated list and an array are equal
        # if used as parameter for a function call)
        $SH->execute(@param);
        # Reset the variables
        $SQL = 'INSERT INTO `mytable`(`word`) VALUES ';
        $cnt = 0;
        $comma = '';
        @param = ();
    }
}
# is there something left at the end?
if ($comma ne '') {
    # Yes, there is something left at the end
    # prepare the string with many (but less than 5000) question marks
    my $SH = $dbh->prepare($SQL);
    # hand over the list of values and execute the prepared statement
    $SH->execute(@param);
}
# disconnect from database
$dbh->disconnect;
# end of program
exit(0);

The trick is, that, when you call a function or a method in Perl, you can hand over the parameters as scalars that are separated by commas:

object->method($scalar1, $scalar2, $scalar3);

But you can also hand over an array:

my $@array = ($scalar1, $scalar2, $scalar3);
object->method(@array);

And so, you can use an array to hand over a variable amount of parameters, and you also easily can hand over 5000 (or even more) parameters.

btw:
This version was even faster than version 2 from my question.

user_13027160
Related