Using a hash value in a simple mysql query (perl)

Question

Can someone help me figure out the correct syntax of

for (my $i = 0; $i <=3; $i++)
{

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

The problem lies with $data{"relationship$1"}. I'm looping because I have 'relationship1', 'relationship2', and 'relationship3' in my data hash. I didn't want to go through 3 separate mysql queries to get the job done so I'm trying to loop over it.

Any pointers?

EDIT:

Thanks for your help with pointing me towards placeholders. It's not working as placeholders and it looks like it's because of

  $sth->execute($data{creatorname},$data{relationship},"DATE_ADD(NOW(), INTERVAL $interval)"

I have a DATE_ADD now that I'm using, it doesn't look like it likes to be used as a placeholder.

score 1 · Answer 1 · edited May 23 '17 at 10:25

As @mob says, you should use query parameters instead of fighting with how to interpolate variables directly into strings.

$store = qq(INSERT INTO main (creator_name, relationship) VALUES (?, ?));
$st = $dbi->prepare($store);
for (my $i = 0; $i < 3; $i++)
{
  $st->execute($data{creatorname}, $data{"relationship$i"});
}

Advantages of using parameters:

Easier to code, without worrying about awkward string interpolation.
Slightly better for performance, because the SQL statement is parsed once, instead of repeatedly during each loop iteration.
Safer with respect to application security; good defense against SQL injection.

Re your comment:

An SQL parameter can be used only in place of a single scalar value. Not an expression, or a table name or column name, or a list of values, or SQL keywords. Basically, any value you pass for the parameter value will be treated as though you had put quotes around it (there are some nuances to that, but it gives you the approximate idea).

Given the expression you described, I'd write the code like this:

$store = qq(INSERT INTO main (creator_name, relationship, complicated_column) 
    VALUES (?, ?, DATE_ADD(NOW(), INTERVAL ? HOUR)));
$st = $dbi->prepare($store);
for (my $i = 0; $i < 3; $i++)
{
  $st->execute($data{creatorname}, $data{"relationship$i"}, $interval);
}

Re answer from @harmic:

This is awkward to reply to another answer by adding to my own answer, but I wanted to share a code test that demonstrates the "double-interpolation" does in fact work.

$ cat test.pl
$i = 1;
$data{"key$i"} = "word";
$s = qq(string with parentheses ($data{"key$i"}));
print $s, "\n";

$ perl test.pl
string with parentheses (word)

The output of running this Perl script shows that the interpolation worked.

Thanks for your reply, Bill. I had another field I didn't show earlier because it's a complicated one. "DATE_ADD(NOW(), INTERVAL $interval)", it won't let me add that as a placeholder value for some reason. — Aaron Anderson, Nov 27 '13 at 01:17
Hi again. $store = qq(INSERT INTO main (release_date) VALUES(DATE_ADD(NOW(), INTERVAL ?)) ); my $sth = $dbh->prepare($store); $sth->execute($interval) or die $dbh->errstr; Results in "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '))'" Any idea why? — Aaron Anderson, Nov 27 '13 at 03:00
Ah - I forgot that `INTERVAL` must be followed by an integer *and an increment*. Like `INTERVAL 3 HOUR` or `INTERVAL 1 YEAR`. The integer part can be a parameter. The keyword for the increment cannot be a parameter. — Bill Karwin, Nov 27 '13 at 03:12
I have fixed the code example above to add `HOUR` as the interval increment type. — Bill Karwin, Nov 27 '13 at 16:27

score 1 · Answer 2 · answered Nov 27 '13 at 02:53

As pointed out by mob and Bill, if possible it is best to use place holders, but that's not the reason your code is not working.

It is not working because you are trying to do two levels of variable interpolation in one string: first interpolate $i into "relationship$i", then interpolate $data{"relationship$i"} into the larger string quoted with qq. They will not nest like that.

This would work:

for (my $i = 0; $i <=3; $i++)
{
    my $relationship = $data{"relationship$i"}
    $store = qq(INSERT INTO main (creator_name,relationship) 
    VALUES("$data{creatorname}",$relationship ) );

Nice thought, but the double-interpolation does work. See an example I have added to my answer. — Bill Karwin, Nov 27 '13 at 16:23

pilcrow · Answer 3 · 2013-11-27T18:35:10.710

It's a bit hard on the eyes, but if you always have three rows to enter you could do it all in one execute():

my $sth = $dbh->prepare(<<'__eosql');
          INSERT INTO main (time_column, creator_name, relationship)
          SELECT NOW() + INTERVAL ? HOUR, -- placeholder for $interval
                 ?,                       -- $data{creatorname}
                 relation
            FROM (SELECT ? AS relation    -- $data{relationship1}
                   UNION ALL
                  SELECT ?                -- $data{relationship2}
                   UNION ALL
                  SELECT ?) d             -- $data{relationship3}
          __eosql

$sth->execute($interval, @data{qw(creatorname relationship1 relationship2 relationship3)});

That uses a hash slice to pull the values out of %data.

Using a hash value in a simple mysql query (perl)

3 Answers3