How can I insert into PostgreSQL using Perl, DBI and placeholders?

Question

I am trying to insert a row into a pSQL table, specifying both keys and values as placeholders:

my @keys = keys %db_entr;                                                            
my @vals = values %db_entr;

my @items = (@keys, @values);

my $dbh = DBI->connect("DBI:Pg:dbname=testdb;host=localhost", "username", 'password', {'RaiseError' => 1});                                                                   
my $sth = $dbh->prepare("INSERT INTO grid ( ?, ?, ? ) values ( ?, ?, ? )");
my $rows = $sth->execute(@items);                                                    
print "$rows effected\n";

However, whatever I do, this gives me an error:

DBD::Pg::st execute failed: ERROR:  syntax error at or near "$1"
LINE 1: INSERT INTO grid ( $1, $2, ...
                           ^ at ./file.pl line 262, <STDIN> line 11.

Does anyone have an idea of what I might be doing wrong?

your code example doesn't make sense, you aren't showing where @items is created — plusplus
– plusplus, Commented Jan 8, 2013 at 10:43

mvp · Accepted Answer · 2013-01-08 10:13:27Z

6

You cannot use placeholders for column names like this:

INSERT INTO grid (?, ?, ?) VALUES (?, ?, ?)

You must specify column names explicitly and can use placeholders for values only:

INSERT INTO grid (x, y, z) VALUES (?, ?, ?)

answered Jan 8, 2013 at 10:13

mvp

118k15 gold badges132 silver badges155 bronze badges

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

Comments

Borodin · Accepted Answer · 2015-09-13 15:40:04Z

4

You can't use placeholders for column names in the prepare call. The best you can do is to either interpolate the variable names into the SQL string or use sprintf to do the equivalent.

This is an example, but you may need to do something different. Note that it modifies the @items array, and you need to call prepare again each time the contents of @items may have changed.

my $sth = $dbh->prepare(
    sprintf "INSERT INTO grid ( %s, %s, %s ) values ( ?, ?, ? )",
    splice @items, 0, 3
);
my $rows = $sth->execute(@items);
print "$rows affected\n";

edited Sep 13, 2015 at 15:40

answered Jan 8, 2013 at 10:23

Borodin

127k9 gold badges72 silver badges146 bronze badges

1 Comment

Ansgar Wiechers Over a year ago

A word of warning (belated as it may be): doing this with user-provided column names defeats the purpose of using prepared statements in the first place, since it will make you vulnerable to SQL injection again.

Collectives™ on Stack Overflow

How can I insert into PostgreSQL using Perl, DBI and placeholders?

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related