[Chicago-talk] Interpolation problem

Steven Lembark lembark at wrkhors.com
Thu Oct 1 14:21:07 PDT 2015


> I was under the impression that single quotes would prevent the 
> placeholders (?) from interpolating. Will give it a try.

Issue is which layer of software does the interpolation.

The '?' doesnt' mean anything to Perl to MySQL gets it as-is through
quoting on the Perly side.

The '@' does mean soemthing to Perl: Interoplate an array. At that
point you have to escape the '@' in order to avoid Perl's interrepting
it as an array you want merged into the string.

One way is escaping it with "\@foo", another is telling Perl not 
to interpolate the string at all (which usually works for SQL since
you don't generally build the stuff on the fly from variables):

my $sql = 'blah blah @foo blah blah';
my $sql = q{ whatever @foo whatever };
my $sql = <<'SQL';
select foo from bar
where blah = @bletch
SQL

One reson to store your SQL strings outside of Perl in YAML or JSON
(I usually use YAML) is that the quoting issues are dealt with for
you when the string is imported:

---
query_1 :
  - |
    your sql 
    here with the '|'
    preserving format
query_2 :
  - drop table if exists foobar
  - |
    create table foobar
    (
        fields...
    )
query_3 :
  - |
    select  whatever
    from    foobar
    where   bletch = @blort
 
Nice thing about this approach is also keeping the SQL content out
of your code, which usually easier to maintain since you don't have
to worry about accidentally botching the code to modify some SQL.

-- 
Steven Lembark                                             3646 Flora Pl
Workhorse Computing                                   St Louis, MO 63110
lembark at wrkhors.com                                      +1 888 359 3508


More information about the Chicago-talk mailing list