$data{"str$i"} not valid in mysql select?

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

My mysql statement is pretty simple.

    $store = qq(INSERT INTO main (creator_name,relationship) VALUES("$
+data{creatorname}","$data{relationship}") );
[download]

This works perfectly as it is but I have need to loop over this three times to get all the form variables without actually hardcoding three mysql statements.

    $store = qq(INSERT INTO main (creator_name,relationship,reason, em
+ail,name,creator_email,creator_url,victim_url,length_of_stay,release_
+date,ip,state) VALUES("$data{creatorname}","$data{relationship}") );
[/code]

What I'm trying to do is
[code]
for (my $i = 0; $i <=3; $i++)
{

    $store = qq(INSERT INTO main (creator_name,relationship) VALUES("$
+data{creatorname}",$data{"relationship$i"}) );
[download]

And it errors with the generic "something is bad with your mysql query. Anyone know what I need to do to get $data{relationship$i} to work in this way?

Comment on $data{"str$i"} not valid in mysql select? Select or Download Code

Replies are listed 'Best First'.
Re: $data{"str$i"} not valid in mysql select? by MidLifeXis (Monsignor) on Nov 27, 2013 at 14:09 UTC
Since you don't say what the source of the data happens to be, I can only assume that it is coming from a potentially untrusted source. Since that is the baseline assumption, do not interpolate -- use placeholders. See http://bobby-tables.com. --MidLifeXis	[reply]
Re: $data{"str$i"} not valid in mysql select? by Anonymous Monk on Nov 27, 2013 at 06:48 UTC
May I recommend using prepared queries? `$store = qq-INSERT INTO main (creator_name,relationship) VALUES(?, ?)- +; my $sth = $dbh->prepare($store); for my $i (0..3) { $sth->execute($data{creatorname}, $data{"relationship$i"}); # or a nicer syntax using a hash slice: # $sth->execute( @data{ "creatorname", "relationship$i" } ); }` [download]	[reply] [d/l]
Re: $data{"str$i"} not valid in mysql select? by Anonymous Monk on Nov 27, 2013 at 00:44 UTC
Ahh man I messed up that post. Please ignore the long $store statement that has all those extra values in it, I meant to clean those up.	[reply]
Re^2: $data{"str$i"} not valid in mysql select? by taint (Chaplain) on Nov 27, 2013 at 02:26 UTC
I suspect I might know the answer. But before I commit, having the actual error. Would be extremely helpful. --Chris #!/usr/bin/perl -Tw use Perl::Always or die; my $perl_version = (5.12.5); print $perl_version;	[reply]