mysql - 绑定(bind)值 INSERT INTO mysql perl

Question

这里是新手。只是试图绑定(bind)值来消除 sql 注入(inject)。我有下面的代码，但我收到此错误......当 my.cgi 第 803 行需要 47 个绑定(bind)变量时，使用 1 个绑定(bind)变量进行调用。输出看起来像..

$new_row='53616c7465645f5fd8b88f6a16704f8ebc0a2002dfg45633617bbb0446fa', 'test12', 'user', '2012-03-06', 'xcvb', 'xb', 'xcvbb', 'xcvbb', 'UT', 'US', '4566', '4564564566', 'todd@my.com', 'vbn', '', '200', 'Monthly', 'eBook', 'WebStore', '9.95', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', 'http://my.com', 'my.com', '', '', '', '', '', '', '', '', '2012-03-06', '30-Day-Trial'
$questionmarks=?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?

我已经尝试过带/不带引号和逗号。任何想法表示赞赏。

foreach my $field (@account_field_order) {
$new_row .= "'" . param($field) . "', ";
$questionmarks .="?, ";
}#foreach
$new_row .= "'$status'";
$questionmarks .= "? ";
my $dsn = "DBI:mysql:$database";
my $dbh = DBI->connect($dsn, $MYSQLuserid, $MYSQLpassword ) 
          or die $DBI::errstr;
my $sth = $dbh->prepare(qq(INSERT INTO $table VALUES ($questionmarks) )) 
or  die $DBI::errstr;
$sth->execute(qq($new_row)) or die $DBI::errstr;

Answer 1

您应该提供一个参数列表，每个问号对应一个参数列表，而不是包含参数字符串的单个标量参数。当我 answered your question之前，我告诉过你这样做:

my @values = map param($_), @account_field_order; # add values to array
push @values, $status;                  # for simplicity
$new_row = join ", ", ("?") x @values;  # add ? for each value

... # basically same code as before, except the execute statement:

$sth->execute(@values);      # arguments given will be inserted at placeholders

其中 $new_row 是占位符字符串，而不是参数列表。 不是:

$new_row .= "'" . param($field) . "', ";
...
$new_row .= "'$status'";
$sth->execute(qq($new_row)) or die $DBI::errstr;

因为 $new_row 算作一个参数，因为它是一个标量。您需要一个与问号数量相同长度的数组或列表。