一言以蔽之
perl中有没有一种方法可以使用准备好的语句(防止sql注入)在不到2分钟的时间内将100万条记录插入mysql表中?
详细
有一个在线资源(wikimedia),我想从中下载一个文件(dewiktionary-latest-all-titles-in-ns0.gz),其中包含将近100万篇文章(每篇文章都是wiktionary中一个德语单词的描述)。我想每周检查一次这个列表,然后对新的或删除的标题做出React。为此,我想每周自动下载一次此列表并将其插入数据库。
虽然我相信维基媒体,但你永远不应该太相信任何来自互联网的东西。因此,为了防止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命令行(即创建一个prepared语句),在循环中执行这个语句,这意味着每次都会有一个新值($word)插入表中,而没有机会执行这个值,因为sql解释器看不到这个值。因此,无论攻击者将什么内容写入我下载的文件,它都不会导致代码注入。
但是:
这很慢。下载在几秒钟内完成,但是insert循环运行了四个多小时。
有一个更快的解决方案,它是这样的:
程序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表?
2条答案
按热度按时间pgvzfuti1#
(这个答案是由问题的作者写的。)
e、 丹给了我正确的答案,所以谢谢你,伊丹!
下面是使用准备好的语句的快速解决方案:
诀窍在于,在perl中调用函数或方法时,可以将参数作为标量进行传递,标量之间用逗号分隔:
但你也可以交出一个数组:
因此,您可以使用数组来传递数量可变的参数,还可以轻松地传递5000个(甚至更多)参数。
顺便说一句:
从我的问题来看,这个版本甚至比版本2还要快。
2jcobegt2#
你用斜体字写的小纸条其实很贴切:
(这被简化了,因为sql语句太长,mysql无法接受。您需要将它分成大约5000个值的部分并执行它们。但这对于我在这里讨论的问题并不重要。)
我认为你的“无准备声明”(不是一个真正的术语)方法更快,因为你一次批量加载5000条记录,而不是一条一条地加载,不是因为它不是一个有准备的声明。
试着用5000来构建一个事先准备好的语句
?
是这样的:然后建立一个一次5000个单词的列表,用它来执行你准备好的语句。您将不得不处理最后一组(大概)少于5000个单词,并在最后一批中使用第二个动态生成的准备好的语句来处理相应数量的单词。
你也可以看看
LOAD DATA INFILE
散装货物。