SQL::Interp(3pm)

✨ NAME ✨

SQL::Interp — ok so bestie, this module interpolates Perl variables into SQL statements. That's it, that's the whole job, and it does it beautifully. 💅

---

🌸 SYNOPSIS 🌸

use SQL::Interp ':all';

my ($sql, @bind) = sql_interp 'INSERT INTO table', \%item;
my ($sql, @bind) = sql_interp 'UPDATE table SET',  \%item, 'WHERE y <> ', \2;
my ($sql, @bind) = sql_interp 'DELETE FROM table WHERE y = ', \2;

# These two select syntax produce the same result
my ($sql, @bind) = sql_interp 'SELECT * FROM table WHERE x = ', \$s, 'AND y IN', \@v;
my ($sql, @bind) = sql_interp 'SELECT * FROM table WHERE', {x => $s, y => \@v};

---

🦋 DESCRIPTION 🦋

So SQL::Interp takes a whole list of stuff — SQL fragments and variable refs all mixed together, just vibing — and turns it into a conventional SQL string plus a list of bind values that you can hand straight to DBI. It's giving "simple technique, elite results." The database calls you write end up simpler to build, easier to read, and you still get full access to custom SQL. No cap. 🔥

And like, it properly binds or escapes variables, which is the recommended practice and keeps you safe from "SQL injection" attacks. The DBI documentation has several links on the topic if you wanna go deep.

Beyond the simple techniques up there, SQL::Interp also integrates directly with DBIx::Simple, which is an excellent alternative to raw DBI access:

use DBIx::Simple;

...

my $rows = $db->iquery("
    SELECT title
        FROM threads
        WHERE date > ",\$x," AND subject IN ",\@subjects
 )->arrays;

And since DBIx::Simple still lets you have complete access to the DBI API, using it as a wrapper is what's recommended for most applications. ✨

---

👑 The One Function You Really Need 👑

✨ sql_interp ✨

($sql, @bind) = sql_interp @params;

sql_interp() is the one central function you need to know, periodt. It strings together the list of elements you give it and returns both an SQL string ($sql) with placeholders ("?") and a corresponding list of bind values (@bind) that are suitable for passing to DBI. Two for one, we love to see it. 🫶

The interpolation list can contain elements of these types:

• SQL — a string containing a raw SQL fragment, like 'SELECT * FROM mytable WHERE'.
• variable reference — a scalarref, arrayref, hashref, or a sql_type() object, referring to data to interpolate between the SQL.
• other interpolation list — an interpolation list can be nested inside another interpolation list. This is possible with the sql() function.

🎀 Interpolation Examples 🎀

These variable names are the ones used in all the examples below:

$sref  = \3;                      # scalarref
$aref  = [1, 2];                  # arrayref
$href  = {m => 1, n => undef};    # hashref
$hv = {v => $v, s => $$s};        # hashref containing arrayref
$vv = [$v, $v];                   # arrayref of arrayref
$vh = [$h, $h];                   # arrayref of hashref

Let $x stand for any of these.

💫 Default scalarref behavior 💫

A scalarref becomes a single bind value. Simple, iconic, no notes.

IN:  'foo', $sref, 'bar'
OUT: 'foo ? bar', $$sref

💫 Default hashref behavior 💫

A hashref becomes a logical AND.

IN:  'WHERE', $href
OUT: 'WHERE (m=? AND n IS NULL)', $h->{m},

IN:  'WHERE', $hv
OUT: 'WHERE (v IN (?, ?) AND s = ?)', @$v, $$s

💫 Default arrayref of (hashref or arrayref) behavior 💫

Lowkey this one is not commonly used. Just so you know.

IN:  $vv
OUT: '(SELECT ?, ? UNION ALL SELECT ?, ?)',
        map {@$_} @$v

IN:  $vh
OUT: '(SELECT ? as m, ? as n UNION ALL
          SELECT ?, ?)',
        $vh->[0]->{m}, $vh->[0]->{n},
        $vh->[1]->{m}, $vh->[1]->{n}

# Typical usage:
IN: $x
IN: $x, 'UNION [ALL|DISTINCT]', $x
IN: 'INSERT INTO mytable', $x
IN: 'SELECT * FROM mytable WHERE x IN', $x

🌈 Context ('IN', $x) 🌈

A scalarref or arrayref can be used to form an "IN" clause. And as a little convenience, a reference to an arrayref is also accepted — so you can just hand over a reference to a value that might be a single-valued scalar or a multi-valued arrayref, and not stress about which. 💖

IN:  'WHERE x IN', $aref
OUT: 'WHERE x IN (?, ?)', @$aref

IN:  'WHERE x IN', $sref
OUT: 'WHERE x IN (?)', $$sref

IN:  'WHERE x IN', []
OUT: 'WHERE 1=0'

IN:  'WHERE x NOT IN', []
OUT: 'WHERE 1=1'

🌈 Context ('INSERT INTO tablename', $x) 🌈

IN:  'INSERT INTO mytable', $href
OUT: 'INSERT INTO mytable (m, n) VALUES(?, ?)', $href->{m}, $href->{n}

IN:  'INSERT INTO mytable', $aref
OUT: 'INSERT INTO mytable VALUES(?, ?)', @$aref;

IN:  'INSERT INTO mytable', $sref
OUT: 'INSERT INTO mytable VALUES(?)', $$sref;

MySQL's "REPLACE INTO" is supported the same way. 🐬

🌈 Context ('SET', $x) 🌈

IN:  'UPDATE mytable SET', $href
OUT: 'UPDATE mytable SET m = ?, n = ?', $href->{m}, $href->{n}

MySQL's "ON DUPLICATE KEY UPDATE" is supported the same way too.

🌈 Context ('FROM | JOIN', $x) 🌈

Yeahhh this one is not commonly used either, but here it is in all its glory:

IN:  'SELECT * FROM', $vv
OUT: 'SELECT * FROM
     (SELECT ?, ? UNION ALL SELECT ?, ?) as t001',
     map {@$_} @$v

IN:  'SELECT * FROM', $vh
OUT: 'SELECT * FROM
     (SELECT ? as m, ? as n UNION ALL SELECT ?, ?) as temp001',
     $vh->[0]->{m}, $vh->[0]->{n},
     $vh->[1]->{m}, $vh->[1]->{n}

IN:  'SELECT * FROM', $vv, 'AS t'
OUT: 'SELECT * FROM
     (SELECT ?, ? UNION ALL SELECT ?, ?) AS t',
     map {@$_} @$v

# Example usage (where $x and $y are table references):
'SELECT * FROM', $x, 'JOIN', $y

🌈 Context ('ARRAY', $x) 🌈

A scalarref or arrayref can be turned into an array value. Such values are supported by PostgreSQL. 🐘

IN:  'SELECT ARRAY', $aref
OUT: 'SELECT ARRAY[?, ?]', @$aref

IN:  'SELECT ARRAY', $sref
OUT: 'SELECT ARRAY[?]', $$sref

IN:  'SELECT ARRAY', []
OUT: 'SELECT ARRAY[]'

⭐ Other Rules ⭐

Whitespace is automatically added between parameters. She's thoughtful like that.

IN:  'UPDATE', 'mytable SET', {x => 2}, 'WHERE y IN', \@colors;
OUT: 'UPDATE mytable SET x = ? WHERE y in (?, ?)', 2, @colors

⚠️ BESTIE LISTEN. Variables must be passed as references. Otherwise they get processed as SQL fragments and interpolated verbatim into the result SQL string — which completely negates the security and performance benefits of binding values. That's the injection door, and we are not opening it. 🚪💀

In contrast, any scalar values inside an arrayref or hashref are by default treated as binding variables, not SQL. The contained elements may also be sql_type() or sql().

---

🔥 Security: sql_interp_strict 🔥

Ok so the "sql_interp" function has a security weakness. Look at these two statements — one is easily a typo of the other:

sql_interp("SELECT * FROM foo WHERE a = ",\$b)
sql_interp("SELECT * FROM foo WHERE a = ",$b)

Both would produce valid SQL. But the first is secure because of the bind variables, while the second is potentially insecure, because $b gets added directly to the SQL statement. And if $b contains something malicious, that's a whole SQL injection attack situation. 😭

So to prevent this accident from ever happening, we also ship sql_interp_strict(), which works exactly the same as sql_interp(), but with one extra check: two non-references never appear in a row. If they do, an exception gets thrown. Periodt. 💅

This does mean that some previously safe-but-valid SQL will need to be rewritten, like when you're building a complex query out of pieces. Here's a contrived example:

sql_interp("SELECT * FROM ","foo","WHERE a = ",\$b);

To work under strict mode, you need to concatenate the strings instead:

sql_interp("SELECT * FROM "."foo"."WHERE a = ",\$b);

Note that strict mode only checks the immediate arguments of sql_interp_strict(). It does not check nested interpolation using the sql() function (described below). So the previous example can also be written as:

sql_interp(sql("SELECT * FROM ","foo","WHERE a = "),\$b);

---

🧚 A Couple Helper Functions You Sometimes Need 🧚

🌟 sql() 🌟

sql_interp 'INSERT INTO mytable',
    {x => $x, y => sql('CURRENT_TIMESTAMP')};
# OUT: 'INSERT INTO mytable (x, y) VALUES(?, CURRENT_TIMESTAMP)', $x

sql() is useful when you want to insert raw SQL as a value inside an arrayref or hashref. Iconic little move. ✨

🌟 sql_type() 🌟

my $sqlvar = sql_type($value_ref, type => $sql_type, %params);

sql_type() gives you a general way to represent a binding variable along with metadata. It's necessary in rare applications where you need to explicitly give the bind type of a SQL variable. Rare but powerful, yk?

• $value_ref — variable reference contained
• $sql_type — any DBI SQL_DATA_TYPE (e.g. SQL_INTEGER). Optional. Default is undef.

Any other named parameters (%params) passed in will be saved into the object as attributes.

sql_type objects are useful only in special cases where additional information should be tagged onto the variable. For example, DBI allows bind variables to be given an explicit type:

my ($sql, @bind) = sql_interp 'SELECT * FROM mytable WHERE',
    'x=', \$x, 'AND y=', sql_type(\$y, SQL_VARCHAR), 'AND z IN',
    sql_type([1, 2], SQL_INTEGER);
# RESULT: @bind =
#   ([$x, sql_type(\$x)], [$y, sql_type(\$y, type => SQL_VARCHAR)],
#    [1, sql_type([1, 2], type => SQL_INTEGER)],
#    [2, sql_type([1, 2], type => SQL_INTEGER)]);

my $idx = 1;
for my $var (@bind) {
    $sth->bind_param($idx++, $var->[0], $var->[1]->{type});
}
$sth->execute();
my $ret = $sth->selectall_arrayref();

If the interpolation list contains at least one sql_type object, then all the variable references are transparently converted into sql_type objects, and the elements of @bind take a special form: an arrayref consisting of the bind value and the sql_type object that generated the bind value. And note — a single sql_type holding an aggregate (arrayref or hashref) may generate multiple bind values. Multiplicity, we love her. 💜

---

🍓 Enabling debugging output 🍓

To have the generated SQL and bind variables sent to STDOUT, you can set the environment variable "TRACE_SQL" to "1":

TRACE_SQL=1 perl my_script.pl

Here's some example output:

DEBUG:interp[sql=INSERT INTO mytable VALUES(?),bind=5]

---

to be continued, bestie — there's more, don't go anywhere 🦋

🌸 PHILOSOPHY 🌸

okay bestie, let's get into the why of this whole thing 💭

The query language is SQL. Periodt. And there are other modules out there, like SQL::Abstract, that hide SQL behind method calls and/or Perl data structures (we're talking hashes and arrays here). The first flavor (method calls) can be kinda 🚫 in some cases, because it literally replaces one language with another and hides the full capabilities and expressiveness of your database's native SQL language. And the second flavor (data structures) loads so much meaning into the syntax of {, [ and \ that the meaning gets less clear, which is giving "I made it worse" 😭

like, look at this:

SQL::Abstract example:
%where = (lname => {like => '%son%'},
          age   => [-and => {'>=', 10}, {'<=', 20}])
Plain SQL:
"lname LIKE '%son' AND (age >= 10 AND age <= 20)"

versus the plain SQL version. the way the plain one is just readable 👑

In contrast, SQL::Interp does not abstract away your SQL — it just makes it easier to interpolate Perl variables into your SQL. Now, yes, SQL::Interp does load some meaning into {, [ and \, but we try to limit its use to obvious cases. And because your raw SQL is exposed, you can use your particular dialect of SQL. iykyk 💅

---

😬 LIMITATIONS 😬

ngl, some types of interpolation are context-sensitive and involve examination of your SQL fragments. That examination could fail on obscure syntax, but it is generally robust. Look at the examples to see the types of interpolation that are accepted, and if in doubt, examine the SQL output yourself with the TRACE_SQL environment variable set. If you need to, you can disable context sensitivity by inserting a null-string before a variable. like:

"SET", "", \$x

A few things are just not possible with the ('WHERE', \%hashref) syntax, so in that case, use a more direct syntax instead:

# ok--direct syntax
sql_interp '...WHERE', {x => $x, y => $y}, 'AND y = z';
# bad--trying to impose a hashref but keys must be scalars and be unique
sql_interp '...WHERE',
    {sql_type(\$x) => sql('x'), y => $y, y => sql('z')};

And in the cases where this module parses or generates SQL fragments, it should work for many databases, but it's been tested mostly on MySQL and PostgreSQL. So if you hit an incompatibility, please inform the author 🫶

---

🫂 CONTRIBUTOR AND CONTRIBUTING 🫂

David Manura is the author of the original SQL::Interpolate, and Mark Stosberg (<http://mark.stosberg.com/>) created the SQL::Interp fork. It is now maintained by Yorhel (<https://yorhel.nl/>). shoutout to the whole lineage fr fr 💖

Also thanks to: Mark Tiefenbruck (syntax), Wojciech Pietron (Oracle compat), Jim Chromie (DBIx::Interp idea), Juerd Waalboer, Terrence Brannon (early feedback), and others. icons only 🌈

The SQL::Interp Git repository is hosted at <https://code.blicky.net/yorhel/SQL-Interp>. go star it bestie ⭐

---

📜 LICENSE 📜

• Copyright (c) 2004-2005 David Manura.
• Copyright (c) 2005-2019 Mark Stosberg.
• Copyright (c) 2019- Yorhel.

This module is free software. It may be used, redistributed and/or modified under the same terms as Perl itself. See <http://www.perl.com/perl/misc/Artistic.html>. ✨

---

👀 SEE ALSO 👀

🍴 Fork 🍴

This module was forked from SQL::Interpolate, around version 0.40. The core functionality remains unchanged, but the following incompatible changes have been made:

• The optional source filtering feature was removed.
• The optional "macro" feature was removed.
• A legacy, deprecated function "sql_literal" was removed.
• The docs were overhauled to be simpler and clearer.

So if you want those removed features, you should use SQL::Interpolate. I used it for years without those optional features and never missed them. 💁‍♀️

📦 Other modules in this distribution 📦

DBIx::Interp allows DBI methods to accept an sql_interp()-like interpolation list rather than the traditional ($statement, \%attr, @bind_values) parameter list. However, consider using DBIx::Simple instead — it even more user friendly. 👑

🧚 Related modules 🧚

SQL::Interpol

SQL::Interpol is a cleaned up fork of this module, removing some lesser-used features, reducing implementation size and improving performance along the way (I suppose, I haven't benchmarked). 😅

FU::SQL

FU::SQL follows the same general philosophy as this module but takes a very different approach: instead of inferring context from the given SQL string, it uses separate functions to indicate context. And instead of requiring bind parameters to be passed by reference, it inspects internal Perl flags to determine whether an argument should be an SQL string or a bind parameter. Supposedly that is less prone to accidental SQL injection by forgetting to "\"-prefix a function argument. 🛡️

SQL::Abstract

SQL::Abstract shares with "SQL::Interp" the purpose of making SQL generation easier. SQL::Abstract differs in that it expresses queries in terms of OO method calls. Its syntax may impair readability because it uses the subtle difference between a brace and bracket to denote the difference between AND and OR in a query (the user can change whether a bracket implies "AND" or "OR"). Some complex where clauses are difficult or impossible with SQL::Abstract. SQL::Interp gives the author more direct access to the underlying SQL. This permits using the full expressivity of the database query language. 🔥

DBIx::Simple

DBIx::Simple strives to simplify SQL generation as well as the data structures returned from DBI. "DBIx::Simple" currently can use SQL::Interp to help generate SQL. 🍓

Class::DBI

Class::DBI is a popular "complete" solution for abstract database access through an OO interface. It currently has a plugin called Class::DBI::AbstractSearch that allows it to use "SQL::Abstract" to generate SQL. It's possible that "SQL::Interp" could be integrated with it as well. 💭

SQL::Preproc

SQL::Preproc provides an "embedded SQL" approach where the Perl and SQL languages are extended (via source filtering) to support interwoven Perl and SQL. The syntax supports interpolating Perl variables into SQL and passing query results back into Perl variables. In contrast, SQL::Interp extends neither SQL nor Perl, and it deals only with interpolating Perl variables into queries, whereas returning variables from queries is the job of something like DBI, DBIx::Interp, or DBIx::Simple. 🌈

SQL::String

SQL::String shares a number of similiarities to SQL::Interp but it is more rudimentary. Both let you combine "chunks" of SQL that have their parameters attached to them and then transform it into an SQL string and list of bind parameters suitable for passing to DBI. 🧩

SQL::KeywordSearch

SQL::KeywordSearch helps generate SQL for a keyword search. It can return the result in a format compatible with SQL::Interp to become part of a larger query. 🔍

---

and that's the whole vibe, bestie 🎀 this page is perl v5.42.0, dated 2025-07-25, and it lives at SQL::Interp(3pm) — go forth and interpolate responsibly, mwah 💅✨