[Chicago-talk] Interpolation problem

[Alan Mead]

The placeholders are interpolated when you execute the SQL statement
($sth->execute($lid); replaces the placeholder with the value in $lid). 

You want to use single quotes when an SQL string contains a sigil that
would cause Perl to assume it's a Perl variable:

my $q = "set @rn = 0";

should be one of these:

my $q = 'set @rn = 0';
my $q = q/set @rn = 0/;
my $q = "set \@rn = 0";

or else Perl think you are trying to interpolate the list @rn into the
SQL string. The same thing occurs with dollar-signs:

print "$foo\n" # prints value of $foo and a newline
print '$foo', "\n" # prints the literal string '$foo' and a newline

The reason to use placeholders (as I understand it) is (a) to increase
the efficiency of statements that you may prepare once and use
repeatedly and (b) to make SQL injection attacks harder because the
placeholders are always quoted.

-Alan


On 9/30/2015 2:22 PM, Richard Reina wrote:
> I was under the impression that single quotes would prevent the placeholders (?) from interpolating. Will give it a try.
>
>
>
>> El 30 sept 2015, a las 1:29 PM, Jason A. Crome <cromedome at gmail.com> escribió:
>>
>> Shawn’s answer is best. You can put a \ before @rn to prevent interpolation as well. His way is cleaner though.
>>
>> Jason A. Crome / CromeDome
>>
>> AIM: TheOneCromeDome
>> Twitter: http://www.twitter.com/cromedome
>> Blog: http://crome-plated.tumblr.com/
>> CPAN: http://search.cpan.org/~cromedome/
>> github: http://github.com/cromedome
>> Bitbucket: https://bitbucket.org/cromedome/
>>
>>> On Sep 30, 2015, at 1:26 PM, Shawn Carroll <shawn.c.carroll at gmail.com> wrote:
>>>
>>> In the line 
>>>
>>> $q = "SELECT @rn:=@rn+1 AS RN, l.ID FROM logros l WHERE l.ID>?"
>>>
>>> Replace the double quotes (") with single quotes (') to prevent interpolation.
>>>
>>>
>>> shawn.c.carroll at gmail.com
>>> Software Engineer
>>> Soccer Referee
>>>
>>> On Wed, Sep 30, 2015 at 1:23 PM, <richard at rushlogistics.com> wrote:
>>> Wondering if anyone can lend a hand here. The set of queries below work when I just plug them directly into the MySQL console.  However, when I wrap them in perl they fail with SQL errors.
>>>
>>>   my @rn;
>>>   my $q = "set @rn = 0"'; # set row number var for query below in order to be able to iterate as a hash in featadd.tt
>>>   $sth = $dbh->prepare($q);
>>>   $sth->execute();
>>>
>>>   # for now in this same subroutine, let's get the sponsorships that these feats match
>>>   $q = "SELECT @rn:=@rn+1 AS RN, l.ID FROM logros l WHERE l.ID>?";
>>>   $sth = $dbh->prepare($q);
>>>   $sth->execute($lid);
>>>   my $log_ref = $sth->fetchall_hashref('RN');
>>>
>>> I get:
>>>
>>> DBD::mysql::st execute failed: 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 '= 0' at line 1 at test_sql.pl line 12.
>>> DBD::mysql::st execute failed: 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 ':=+1 AS RN, l.ID FROM logros l WHERE l.ID>'7'' at line 1 at test_sql.pl line 17.
>>> DBD::mysql::st fetchall_hashref failed: statement contains no result [err was 4 now 2000000000]
>>>
>>> Thanks for any help.
>>> _______________________________________________
>>> Chicago-talk mailing list
>>> Chicago-talk at pm.org
>>> http://mail.pm.org/mailman/listinfo/chicago-talk
>>>
>>> _______________________________________________
>>> Chicago-talk mailing list
>>> Chicago-talk at pm.org
>>> http://mail.pm.org/mailman/listinfo/chicago-talk
>> _______________________________________________
>> Chicago-talk mailing list
>> Chicago-talk at pm.org
>> http://mail.pm.org/mailman/listinfo/chicago-talk
> _______________________________________________
> Chicago-talk mailing list
> Chicago-talk at pm.org
> http://mail.pm.org/mailman/listinfo/chicago-talk

-- 

Alan D. Mead, Ph.D.
President, Talent Algorithms Inc.

science + technology = better workers

+815.588.3846 (Office)
+267.334.4143 (Mobile)

http://www.alanmead.org

Announcing the Journal of Computerized Adaptive Testing (JCAT), a
peer-reviewed electronic journal designed to advance the science and
practice of computerized adaptive testing: http://www.iacat.org/jcat