时间戳

首页 > 解决方案 > 用于快速批量插入的准备好的语句

mysql - 用于快速批量插入的准备好的语句

问题描述

简而言之

Perl 中有没有办法使用准备好的语句（防止 SQL 注入）在不到 2 分钟的时间内将 100 万条记录插入 MySQL 表？

详细地

有一个在线资源 ( Wikimedia )，我想从中下载一个文件 ( dewiktionary-latest-all-titles-in-ns0.gz )，其中包含近 100 万篇文章标题（每篇文章都是对德语单词的描述维基词典）。我想每周检查一次这个列表，然后对新的或删除的标题做出反应。为此，我想每周自动下载一次此列表并将其插入数据库。

虽然我相信维基媒体，但你永远不应该太相信来自互联网的任何东西。因此，为了防止 SQL 注入和其他安全问题，我总是在 Perl 中使用准备好的语句，请确保 SQL 解释器没有机会将内容解释为代码。

通常我会这样做：

程序 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);

注意这一行（第 27 行）： 

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

SQL 命令行中有一个问号作为占位符。在下一行准备好这个 SQL 命令行（即创建一个准备好的语句），并在循环中执行这个语句，这意味着每次将一个新值（$word）插入到表中，而无需执行此值的任何机会，因为 SQL 解释器看不到此值。因此，无论攻击者向我下载的文件中写入什么内容，它都不会导致代码注入。

但是：
这很慢。下载在几秒钟内完成，但插入循环运行了四个多小时。

有一个更快的解决方案，它是这样的：

程序 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);

（这是简化的，因为 SQL 语句会变得太长而无法被 MySQL 接受。您需要将其分成大约 5000 个值的部分并执行它们。但这对于我在这里谈论的问题并不重要。 )

这运行得非常快。所有值（新表中几乎 100 万行）在不到 2 分钟的时间内插入，这快了 100 倍以上。

如您所见，我创建了一个大语句，但没有占位符。我将值直接写入 SQL 命令。我只需要转义将被解释为转义字符的反斜杠和将被解释为字符串结尾的单引号。

但是其余的值仍然不受保护并且对 SQL 解释器可见。潜在的攻击者可能会找到一种将 SQL 代码插入将要执行的值的方法。这可能会损坏我的数据库，甚至可能授予攻击者超级用户权限。（代码注入引起的权限提升）

所以，这是我的问题：

有没有办法使用程序 1 中的准备好的语句，即使是像程序 2 中那样动态生成的语句？

或者是否有另一种可能快速、安全地将大量数据插入 MySQL 表？

标签： mysqlperl

解决方案

您的斜体小注释实际上非常相关：

（这是简化的，因为 SQL 语句会变得太长而无法被 MySQL 接受。您需要将其分成大约 5000 个值的部分并执行它们。但这对于我在这里谈论的问题并不重要。 )

我认为您的“未准备好的声明”（不是真正的术语）方法更快，因为您一次批量加载 5000 条记录而不是一条一条的，而不是因为它不是准备好的声明。

尝试使用 5000 构建一个准备好的语句，?如下所示：

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

然后一次构建一个包含 5000 个单词的列表，并用它执行你准备好的语句。您必须使用最后一批中适当数量的单词的第二个动态生成的准备语句来处理最后一组（大概）少于 5000 个单词。

您还可以查看LOAD DATA INFILE批量加载。

推荐阅读