Beefy Boxes and Bandwidth Generously Provided by pair Networks
Do you know where your variables are?
 
PerlMonks  

Avoid embedding variables in SQL - Dancer App

by GertMT (Hermit)
on Sep 05, 2014 at 14:38 UTC ( #1099679=perlquestion: print w/replies, xml ) Need Help??

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' }; }; };

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' }; }; };

Replies are listed 'Best First'.
Re: Avoid embedding variables in SQL - Dancer App
by Corion (Pope) 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.

      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 ); ----
      Should be okay.

Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Node Status?
node history
Node Type: perlquestion [id://1099679]
Approved by marto
Front-paged by toolic
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others chanting in the Monastery: (4)
As of 2021-10-23 20:23 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?
    My first memorable Perl project was:







    Results (88 votes). Check out past polls.

    Notices?