Avoid embedding variables in SQL

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

Dear monks,

while working on Dancer app I try to use placeholders for the SQL queries. The first code gives a good result but it is without placeholders. The second with placeholders doesn't show the result I had expected. To my idea the problem is with the third placeholder $link. Can't find out what am I doing wrong?

Thanks,

Gert

First code without placeholders but it works

prefix '/en/collections' => sub {
    get '/:link' => sub {
        my $link          = params->{'link'};
        my $sql = "SELECT 
                      imgf,
                      long_i,
                      status_prod,
                      name_prod,
                      name_cat,
                      name_col
                 FROM products,
                      categories,
                      collections
                WHERE products.fk_cat = categories.id_cat 
                  AND products.fk_col = collections.id_col
                  AND link_col= '$link'";
        my $sth = database->prepare($sql);
        $sth->execute;
        template 'col-m',
          {
            have_text_markdown => $have_text_markdown,
            bigprodlist        => $sth->fetchall_arrayref( {} )
          },
          { layout => 'pin' };
    };
};
[download]

Second code without placeholders but does not work?

prefix '/en/collections' => sub {
    get '/:link' => sub {
        my $link          = params->{'link'};
    #print Dumper $link;
my $sql = <<'SQL';
                  SELECT 
                      imgf,
                      long_i,
                      status_prod,
                      name_prod,
                      name_cat,
                      name_col
                 FROM products,
                      categories,
                      collections
               WHERE products.fk_cat = ?
                  AND products.fk_col = ?
                  AND link_col = ? 
SQL
    my $sth = database->prepare($sql);
    #print Dumper $link; # is okay
    $sth->execute( 'categories.id_cat', 'collections.id_col', $link );
     template 'col-m',
          {
            have_text_markdown => $have_text_markdown,
            bigprodlist        => $sth->fetchall_arrayref( {} )
          },
          { layout => 'pin' };
    };
};
[download]

Comment on Avoid embedding variables in SQL - Dancer App Select or Download Code

Replies are listed 'Best First'.
Re: Avoid embedding variables in SQL - Dancer App by Corion (Patriarch) on Sep 05, 2014 at 14:43 UTC
Your first example only uses one variable in the place. Your second example uses three placeholders, but you don't want `categories.id_cat` to be compared as a string. Placeholders only work for constant values, not for column references. You will need to use string interpolation for that.	[reply] [d/l]
Re^2: Avoid embedding variables in SQL - Dancer App by GertMT (Hermit) on Sep 05, 2014 at 16:14 UTC
Thanks for your comments Reading about this I hope not to be wrong in my conclusion that `---- WHERE products.fk_cat = categories.id_cat AND products.fk_col = collections.id_col AND collections.link_col = ? SQL my $sth = database->prepare($sql); #print Dumper $link; # is okay $sth->execute( $link ); ----` [download] Should be okay.	[reply] [d/l]

Back to Seekers of Perl Wisdom