how to bind values INSERT INTO mysql perl

Question

I've got the code below that works but I need to know how to bind them for security. If I just replace $new_row with ? and put it in execute I get an error. Thanks for your help.

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

See TLP's answer on placeholders. Another reason to use placeholders: you can run prepared statements that only need to be parsed and compiled once by the DB manager. — Axeman
– Axeman, Commented Mar 5, 2012 at 19:25

TLP · Accepted Answer · 2012-03-05 16:34:05Z

5

You will want to use placeholders, and never interpolate variables in strings. You should probably use taint mode and de-taint your param values before using them, if safety is important to you. Documentation on placeholders here.

Try something like:

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

edited Mar 5, 2012 at 16:34

answered Mar 5, 2012 at 16:20

TLP

68.2k10 gold badges97 silver badges156 bronze badges

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

3 Comments

t a Over a year ago

ok.. I used your code and I get this error.. Column count doesn't match value count at row 1 at account.cgi line 745.

t a Over a year ago

my @values = map param($_), @account_field_order;      push @values, $status;     my $new_row = join ", ", ("?") x @values;      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       ($new_row)     )) or die $DBI::errstr;  $sth->execute(@values) or die $DBI::errstr;

TLP Over a year ago

@ta Well, the best I can do is repeat what the error says to you. You have a sync error somewhere. Try and find it. Oh, by the way, you are using use strict; use warnings;, right? If not, I suggest you do.

runrig · Accepted Answer · 2012-03-05 16:29:44Z

0

If your values were in a hash, there's the insert_hash example in the docs (under prepare_cached). Adjust as appropriate if not using an array.

answered Mar 5, 2012 at 16:29

runrig

6,5442 gold badges31 silver badges46 bronze badges

Collectives™ on Stack Overflow

how to bind values INSERT INTO mysql perl

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related