SQL::Interp(3pm)

1SQL::Interp(3)        User Contributed Perl Documentation       SQL::Interp(3)
2
3
4

NAME

6       SQL::Interp - Interpolate Perl variables into SQL statements
7

SYNOPSIS

9         use SQL::Interp ':all';
10
11         my ($sql, @bind) = sql_interp 'INSERT INTO table', \%item;
12         my ($sql, @bind) = sql_interp 'UPDATE table SET',  \%item, 'WHERE y <> ', \2;
13         my ($sql, @bind) = sql_interp 'DELETE FROM table WHERE y = ', \2;
14
15         # These two select syntax produce the same result
16         my ($sql, @bind) = sql_interp 'SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v;
17         my ($sql, @bind) = sql_interp 'SELECT * FROM table WHERE', {x => $s, y => \@v};
18

DESCRIPTION

20       SQL::Interp converts a list of intermixed SQL fragments and variable
21       references into a conventional SQL string and list of bind values
22       suitable for passing onto DBI. This simple technique creates database
23       calls that are simpler to create and easier to read, while still giving
24       you full access to custom SQL.
25
26       SQL::Interp properly binds or escapes variables.  This recommended
27       practice safeguards against "SQL injection" attacks. The DBI
28       documentation has several links on the topic.
29
30       Besides the simple techniques shown above, The SQL::Interp integrates
31       directly with DBIx::Simple for an excellent alternative to raw DBI
32       access:
33
34         use DBIx::Simple;
35
36         ...
37
38         my $rows = $db->iquery("
39             SELECT title
40                 FROM threads
41                 WHERE date > ",\$x," AND subject IN ",\@subjects
42          )->arrays;
43
44       Since DBIx::Simple still allows you complete access to the DBI API,
45       using it as wrapper is recommended for most applications.
46

The One Function You Really Need

48   "sql_interp"
49         ($sql, @bind) = sql_interp @params;
50
51       "sql_interp()" is the one central function you need to know.
52       "sql_interp()" strings together the given list of elements  and returns
53       both an SQL string ($sql) with placeholders ("?") and a corresponding
54       list of bind values (@bind) suitable for passing to DBI.
55
56       The interpolation list can contain elements of these types:
57
58       * SQL - string containing a raw SQL fragment such as 'SELECT * FROM
59       mytable WHERE'.
60
61       * variable reference - scalarref, arrayref, hashref, or A sql_type()
62       object referring to data to interpolate between the SQL.
63
64       * other interpolation list - an interpolation list can be nested inside
65       another interpolation list.  This is possible with the sql() function.
66
67       Interpolation Examples
68
69       The following variable names will be used in the below examples:
70
71        $sref  = \3;                      # scalarref
72        $aref  = [1, 2];                  # arrayref
73        $href  = {m => 1, n => undef};    # hashref
74        $hv = {v => $v, s => $$s};        # hashref containing arrayref
75        $vv = [$v, $v];                   # arrayref of arrayref
76        $vh = [$h, $h];                   # arrayref of hashref
77
78        Let $x stand for any of these.
79
80       Default scalarref behavior
81
82       A scalarref becomes a single bind value.
83
84         IN:  'foo', $sref, 'bar'
85         OUT: 'foo ? bar', $$sref
86
87       Default hashref behavior
88
89       A hashref becomes a logical AND
90
91         IN:  'WHERE', $href
92         OUT: 'WHERE (m=? AND n IS NULL)', $h->{m},
93
94         IN:  'WHERE', $hv
95         OUT: 'WHERE (v IN (?, ?) AND s = ?)', @$v, $$s
96
97       Default arrayref of (hashref or arrayref) behavior
98
99       This is not commonly used.
100
101         IN:  $vv
102         OUT: '(SELECT ?, ? UNION ALL SELECT ?, ?)',
103                 map {@$_} @$v
104
105         IN:  $vh
106         OUT: '(SELECT ? as m, ? as n UNION ALL
107                   SELECT ?, ?)',
108                 $vh->[0]->{m}, $vh->[0]->{n},
109                 $vh->[1]->{m}, $vh->[1]->{n}
110
111         # Typical usage:
112         IN: $x
113         IN: $x, 'UNION [ALL|DISTINCT]', $x
114         IN: 'INSERT INTO mytable', $x
115         IN: 'SELECT * FROM mytable WHERE x IN', $x
116
117       Context ('IN', $x)
118
119       A scalarref or arrayref can used to form an "IN" clause.  As a
120       convenience, a reference to an arrayref is also accepted.  This way,
121       you can simply provide a reference to a value which may be a single-
122       valued scalar or a multi-valued arrayref.
123
124         IN:  'WHERE x IN', $aref
125         OUT: 'WHERE x IN (?, ?)', @$aref
126
127         IN:  'WHERE x IN', $sref
128         OUT: 'WHERE x IN (?)', $$sref
129
130         IN:  'WHERE x IN', []
131         OUT: 'WHERE 1=0'
132
133         IN:  'WHERE x NOT IN', []
134         OUT: 'WHERE 1=1'
135
136       Context ('INSERT INTO tablename', $x)
137
138         IN:  'INSERT INTO mytable', $href
139         OUT: 'INSERT INTO mytable (m, n) VALUES(?, ?)', $href->{m}, $href->{n}
140
141         IN:  'INSERT INTO mytable', $aref
142         OUT: 'INSERT INTO mytable VALUES(?, ?)', @$aref;
143
144         IN:  'INSERT INTO mytable', $sref
145         OUT: 'INSERT INTO mytable VALUES(?)', $$sref;
146
147       MySQL's "REPLACE INTO" is supported the same way.
148
149       Context ('SET', $x)
150
151         IN:  'UPDATE mytable SET', $href
152         OUT: 'UPDATE mytable SET m = ?, n = ?', $href->{m}, $href->{n}
153
154       MySQL's "ON DUPLICATE KEY UPDATE" is supported the same way.
155
156       Context ('FROM | JOIN', $x)
157
158       This is not commonly used.
159
160         IN:  'SELECT * FROM', $vv
161         OUT: 'SELECT * FROM
162              (SELECT ?, ? UNION ALL SELECT ?, ?) as t001',
163              map {@$_} @$v
164
165         IN:  'SELECT * FROM', $vh
166         OUT: 'SELECT * FROM
167              (SELECT ? as m, ? as n UNION ALL SELECT ?, ?) as temp001',
168              $vh->[0]->{m}, $vh->[0]->{n},
169              $vh->[1]->{m}, $vh->[1]->{n}
170
171         IN:  'SELECT * FROM', $vv, 'AS t'
172         OUT: 'SELECT * FROM
173              (SELECT ?, ? UNION ALL SELECT ?, ?) AS t',
174              map {@$_} @$v
175
176         # Example usage (where $x and $y are table references):
177         'SELECT * FROM', $x, 'JOIN', $y
178
179       Context ('ARRAY', $x)
180
181       A scalarref or arrayref can be turned into an array value. Such values
182       are supported by PostgreSQL.
183
184         IN:  'SELECT ARRAY', $aref
185         OUT: 'SELECT ARRAY[?, ?]', @$aref
186
187         IN:  'SELECT ARRAY', $sref
188         OUT: 'SELECT ARRAY[?]', $$sref
189
190         IN:  'SELECT ARRAY', []
191         OUT: 'SELECT ARRAY[]'
192
193       Other Rules
194
195       Whitespace is automatically added between parameters:
196
197        IN:  'UPDATE', 'mytable SET', {x => 2}, 'WHERE y IN', \@colors;
198        OUT: 'UPDATE mytable SET x = ? WHERE y in (?, ?)', 2, @colors
199
200       Variables must be passed as references; otherwise, they will processed
201       as SQL fragments and interpolated verbatim into the result SQL string,
202       negating the security and performance benefits of binding values.
203
204       In contrast, any scalar values inside an arrayref or hashref are by
205       default treated as binding variables, not SQL.  The contained elements
206       may be also be sql_type() or sql().
207

Security: sql_interp_strict

209       The "sql_interp" function has a security weakness. Consider these two
210       statements, one easily a typo of the other:
211
212           sql_interp("SELECT * FROM foo WHERE a = ",\$b)
213           sql_interp("SELECT * FROM foo WHERE a = ",$b)
214
215       Both would produce valid SQL, but the first would be secure due to use
216       of bind variables, while the second is potentially insecure, because $b
217       is added directly to the SQL statement. If $b contains a malicious
218       value, it could be used for a SQL injection attack.
219
220       To prevent this accident, we also supply "sql_interp_strict()", which
221       works exactly the same as sql_interp(), but with an additional check
222       that two non-references never appear in a row . If they do, an
223       exception will be thrown.
224
225       This does mean some previously safe-but-valid SQL be need to be
226       rewritten, such as when you are building a complex query from pieces.
227       Here's a contrived example:
228
229           sql_interp("SELECT * FROM ","foo","WHERE a = ",\$b);
230
231       To work under strict mode, you need to concatenate the strings instead:
232
233           sql_interp("SELECT * FROM "."foo"."WHERE a = ",\$b);
234

A Couple Helper Functions You Sometimes Need

236   "sql()"
237         sql_interp 'INSERT INTO mytable',
238             {x => $x, y => sql('CURRENT_TIMESTAMP')};
239         # OUT: 'INSERT INTO mytable (x, y) VALUES(?, CURRENT_TIMESTAMP)', $x
240
241       sql() is useful if you want insert raw SQL as a value in an arrayref or
242       hashref.
243
244   "sql_type()"
245         my $sqlvar = sql_type($value_ref, type => $sql_type, %params);
246
247       "sql_type()" provides a general way to represent a binding variable
248       along with metadata.  It is necessary in rare applications which you
249       need to explicity give the bind type of a SQL variable.
250
251       $value_ref - variable reference contained
252
253       $sql_type - any DBI SQL_DATA_TYPE (e.g. SQL_INTEGER).  Optional.
254       Default is undef.
255
256       Any other named parameters (%params) passed in will be saved into the
257       object as attributes.
258
259       sql_type objects are useful only in special cases where additional
260       information should be tagged onto the variable.  For example, DBI
261       allows bind variables to be given an explicit type:
262
263         my ($sql, @bind) = sql_interp 'SELECT * FROM mytable WHERE',
264             'x=', \$x, 'AND y=', sql_type(\$y, SQL_VARCHAR), 'AND z IN',
265             sql_type([1, 2], SQL_INTEGER);
266         # RESULT: @bind =
267         #   ([$x, sql_type(\$x)], [$y, sql_type(\$y, type => SQL_VARCHAR)],
268         #    [1, sql_type([1, 2], type => SQL_INTEGER)],
269         #    [2, sql_type([1, 2], type => SQL_INTEGER)]);
270
271         my $idx = 1;
272         for my $var (@bind) {
273             $sth->bind_param($idx++, $var->[0], $var->[1]->{type});
274         }
275         $sth->execute();
276         my $ret = $sth->selectall_arrayref();
277
278       If the interpolation list contains at least one sql_type object, then
279       all the variable references are transparently converted into sql_type
280       objects, and the elements of @bind take a special form: an arrayref
281       consisting of the bind value and the sql_type object that generated the
282       bind value.  Note that a single sql_type holding an aggregate (arrayref
283       or hashref) may generate multiple bind values.
284

Enabling debugging output

286       To have the generated SQL and bind variables sent to STDOUT, you can
287       set the environment variable "TRACE_SQL" to "1"
288
289        TRACE_SQL=1 perl my_script.pl
290
291       Here's some example output:
292
293        DEBUG:interp[sql=INSERT INTO mytable VALUES(?),bind=5]
294

Philosophy

296       The query language is SQL.  There are other modules, such as
297       SQL::Abstract, that hide SQL behind method calls and/or Perl data
298       structures (hashes and arrays).  The former may be undesirable in some
299       cases since it replaces one language with another and hides the full
300       capabilities and expressiveness of your database's native SQL language.
301       The latter may load too much meaning into the syntax of "{, "[" and "\"
302       thereby rendering the meaning less clear:
303
304         SQL::Abstract example:
305         %where = (lname => {like => '%son%'},
306                   age   => [-and => {'>=', 10}, {'<=', 20}])
307         Plain SQL:
308         "lname LIKE '%son' AND (age >= 10 AND age <= 20)"
309
310       In contrast, SQL::Interp does not abstract away your SQL but rather
311       makes it easier to interpolate Perl variables into your SQL.  Now,
312       SQL::Interp does load some meaning into "{, "[" and "\", but we try to
313       limit its use to obvious cases.  Since your raw SQL is exposed, you can
314       use your particular dialect of SQL.
315

Limitations

317       Some types of interpolation are context-sensitive and involve
318       examination of your SQL fragments.  The examination could fail on
319       obscure syntax, but it is generally robust.  Look at the examples to
320       see the types of interpolation that are accepted, and if doubt, examine
321       the SQL output yourself with the TRACE_SQL environment variable set.
322       If needed, you can disable context sensitivity by inserting a null-
323       string before a variable.
324
325        "SET", "", \$x
326
327       A few things are just not possible with the ('WHERE', \%hashref)
328       syntax, so in such case, use a more direct syntax:
329
330         # ok--direct syntax
331         sql_interp '...WHERE', {x => $x, y => $y}, 'AND y = z';
332         # bad--trying to impose a hashref but keys must be scalars and be unique
333         sql_interp '...WHERE',
334             {sql_type(\$x) => sql('x'), y => $y, y => sql('z')};
335
336       In the cases where this module parses or generates SQL fragments, this
337       module should work for many databases, but its been tested mostly on
338       MySQL and PostgreSQL.  Please inform the author of any
339       incompatibilities.
340

Contributor and Contributing

342       David Manura is the author of the original SQL::Interpolate, Mark
343       Stosberg (<http://mark.stosberg.com/>) created the SQL::Interp fork. It
344       is now maintained by Yoran Heling (<https://yorhel.nl/>).
345
346       Also thanks to: Mark Tiefenbruck (syntax), Wojciech Pietron (Oracle
347       compat), Jim Chromie (DBIx::Interp idea), Juerd Waalboer, Terrence
348       Brannon (early feedback), and others.
349
350       If you like SQL::Interp, please consider supporting the project by
351       adding support for the 'quote_char' and 'name_sep' options.
352       SQL::Abstract has code that can be borrowed for this. See this bug
353       report for details: http://rt.cpan.org/Public/Bug/Display.html?id=31488
354
355       If you use SQL::Interp with PostgreSQL and are interested in a further
356       performance improvement, considering working on this optimization:
357       "RT#39778: wish: optimize IN() to be ANY() for compatible PostgreSQL
358       versions": https://rt.cpan.org/Ticket/Display.html?id=39778
359
360       The SQL::Interp Git repository is hosted at
361       <https://code.blicky.net/yorhel/SQL-Interp>.
362

Bug Reporting

364       Use rt.cpan.org for bug reports.
365

License

367       Copyright (c) 2004-2005 David Manura.
368
369       Copyright (c) 2005-2019 Mark Stosberg.
370
371       Copyright (c) 2019 Yoran Heling.
372
373       This module is free software. It may be used, redistributed and/or
374       modified under the same terms as Perl itself.  See
375       <http://www.perl.com/perl/misc/Artistic.html>.
376