mysql 预处理语句 性能,mysql – 用于快速质量插入的预处理语句

简而言之

在Perl中是否有办法使用预准备语句(以防止SQL注入)在不到2分钟内将一百万条记录插入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://');

# 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=;host=localhost',

'','',

{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表中？

解决方法:

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

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

我认为你的“毫无准备的声明”(不是真正的术语)方法更快,因为你一次只批量加载5000条记录而不是一条一条,而不是因为它不是一个准备好的语句.

尝试使用5000这样的方法构建一个准备好的语句：

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

然后一次构建一个包含5000个单词的列表,并用它执行准备好的语句.您将不得不处理最后一组(大概)少于5000个单词,并在最后一批中使用第二个动态生成的准备语句来处理相应数量的单词.

您还可以查看LOAD DATA INFILE以进行批量加载.

标签：mysql,perl

来源： https://codeday.me/bug/20190622/1261955.html