bind values INSERT INTO mysql perl

Question

Novice here. Just trying to bind values to eliminate sql injections. I've got the code below but I get this error... called with 1 bind variables when 47 are needed at my.cgi line 803. and output looks like..

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

I've tried it with/without quotes and commas. Any ideas appreciated.

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;

Community · Accepted Answer · 2017-05-23 12:29:07Z

2

You're supposed to supply a list of arguments, one for each questionmark, not a single scalar argument that contains the strings of the arguments. When I answered your question before, I told you to do:

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

Where $new_row is your placeholder string, not your argument list. Not:

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

Because $new_row counts as one argument, since it is a scalar. You need an array or list of the same length as the number of questionmarks.

edited May 23, 2017 at 12:29

CommunityBot

11 silver badge

answered Mar 6, 2012 at 16:33

TLP

68.2k10 gold badges97 silver badges156 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

t a Over a year ago

I used your code and got an error like 'values don't match column count' because it was only executing 22 params/values. The table has 47 fields/columns. BUT, it gave me the idea for the above code which seems to be closer.

TLP Over a year ago

Perhaps it is an SQL error. It is hard to tell, since you do not give me much information.

Wes Hardaker · Accepted Answer · 2012-03-06 16:32:24Z

0

First, lets fix the first statements:

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

These will create an array of values and a single string with all the ?s.

Then in the execute statement:

$sth->execute(@new_row) or die $DBI::errstr;

which will pass in your array of values into the execute line, rather than a single argument like you were doing before.

answered Mar 6, 2012 at 16:32

Wes Hardaker

22.3k2 gold badges42 silver badges70 bronze badges

3 Comments

t a Over a year ago

Thanks. That makes sense but when I modified it I get this error... called with 2 bind variables when 0 are needed at account.cgi line 752.

t a Over a year ago

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

t a Over a year ago

Ok. Figured it out..

foreach my $field (@account_field_order) { 	push (@new_row, "'param($field)', "); 	$questionmarks .="?, "; 	}#foreach  push (@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(@new_row) or die $DBI::errstr;

Collectives™ on Stack Overflow

bind values INSERT INTO mysql perl

2 Answers 2

2 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related