glue glue_sql() in R: Safely Interpolate Values Into SQL

glue glue_sql() builds a SQL query by interpolating R values into a template, with each value auto-quoted for the connected database. It is the safe alternative to paste0() or sprintf() for SQL because the connection object decides how literals, identifiers, and vectors are escaped. Use it whenever the query depends on user input or runtime data.

glue_sql("SELECT * FROM x WHERE id = {id}", id = 7, .con = con)        # literal
glue_sql("SELECT * FROM x WHERE name = {nm}", nm = "Ada", .con = con)  # string auto-quoted
glue_sql("SELECT * FROM x WHERE id IN ({ids*})", ids = 1:3, .con = con) # vector splice
glue_sql("SELECT {`col`} FROM x", col = "name", .con = con)            # identifier
glue_sql("SELECT * FROM {tbl}", tbl = SQL("users"), .con = con)         # raw SQL
glue_sql("UPDATE x SET v = {v} WHERE id = {id}", v = NA, id = 5, .con = con) # NULL safe
q <- glue_sql("SELECT * FROM x WHERE age > {a}", a = 30, .con = con)   # then dbGetQuery(con, q)

What glue_sql() does in one sentence

glue_sql() interpolates R values into a SQL template and quotes each value according to the rules of the connected database. You pass a .con argument (a DBI connection), and the function uses that connection to decide how strings, numbers, vectors, identifiers, and NA appear inside the final query.

The result is a SQL object that you can hand to DBI::dbGetQuery() or DBI::dbExecute(). Because the connection drives the quoting, the same template works against SQLite, Postgres, MySQL, or any other DBI backend. The {name} syntax is identical to glue(), but every interpolated value is sanitised before insertion.

The {name} slot is replaced by the string Ada wrapped in single quotes, the way SQLite expects literal strings. No manual escaping was needed.

Syntax

glue_sql(..., .con, .envir = parent.frame(), .na = DBI::SQL("NULL")) builds a parameterized SQL statement. The ... arguments are the template strings, .con is the live DBI connection that drives quoting, and .na sets the SQL token used for missing values.

Interpolation rules inside the template:

• {value} is treated as a literal and quoted by DBI::dbQuoteLiteral(). Strings become quoted strings, numbers stay unquoted, and NA becomes the .na token.
• Wrapping the brace contents in backticks (as shown in example 3) flips the slot to identifier mode, with quoting handled by DBI::dbQuoteIdentifier().
• {vec*} (the * suffix) splices a vector into a comma-separated list, useful for IN (...) clauses and VALUES (...) tuples.
• A value wrapped in DBI::SQL() is treated as raw SQL and is NOT quoted, which is how you inject already-trusted fragments.

Common use cases

1. Literal value interpolation

The string Ada is wrapped in single quotes. If target contained an apostrophe (O'Brien), glue_sql() would double the quote ('O''Brien') automatically, which is what stops naive concatenation from breaking on weird names. Numeric values are not quoted, and dates render in the dialect's preferred format.

2. Splice a vector into an IN clause

The * suffix tells glue_sql() to expand ids into a comma-separated list, with each element quoted individually. Without *, a length-2 vector would produce a length-2 SQL vector (two separate queries), which is almost never what you want for IN.

3. Treat a value as an identifier

The backticks around the brace contents flip glue_sql() from "quote as literal" to "quote as identifier". Identifier quoting uses the database-specific quote character (backticks in SQLite, double quotes in Postgres), so the same template works on either backend.

4. Inject raw SQL with DBI::SQL()

DBI::SQL() marks a string as already-safe SQL, so glue_sql() drops it in unchanged. Use this for fixed fragments you control (column lists, ORDER BY direction). Never wrap user input in DBI::SQL(), that disables the safety the rest of the function provides.

5. End-to-end: build and execute

Build with glue_sql(), execute with dbGetQuery() or dbExecute(). Splitting the steps lets you inspect the assembled SQL before running it, which is much easier to debug than a paste0() buried inside the executor call.

glue_sql() vs paste0() and sprintf()

Three approaches build SQL strings; only glue_sql() escapes correctly. paste0() and sprintf() are general-purpose string builders unaware of SQL, so they do not quote, do not handle vectors, and silently produce injection bugs.

The decision rule: any query whose values come from outside the script (user input, file rows, API responses) belongs in glue_sql(). Hard-coded queries with no interpolation are fine as plain literals.

Common pitfalls

1. Forgetting .con argument. glue_sql() throws "Connection required" because it cannot quote without knowing the database. Pass a real DBI connection, not NULL or a string. For testing without a live driver, use DBI::ANSI().

1. Using {x} instead of {x*} for vectors. A length-N vector inside plain {x} produces a length-N SQL object, one query per element. That breaks IN clauses and most other multi-value patterns. Add the * suffix any time the value is a vector and you want it spliced into one query.

1. Wrapping user input in DBI::SQL(). The SQL() wrapper marks a string as already-safe and skips quoting. If user-supplied data flows through that wrapper, glue_sql()'s safety is gone. Reserve DBI::SQL() for fragments authored in code, never for runtime values.

Try it yourself

Related glue functions

• glue() interpolates {expressions} into plain text strings. Use it whenever the output is not SQL.
• glue_data() interpolates a template across the rows of a data frame, returning one string per row.
• glue_collapse() joins a character vector into one string, useful for building summary lines after a glue() pipeline.
• DBI::sqlInterpolate() is the lower-level DBI primitive that glue_sql() wraps. Reach for it directly only when you need full control over the placeholder syntax.
• See the glue_sql reference for the complete argument list and backend-specific examples.

FAQ

Why use glue_sql() instead of paste0() for SQL?

paste0() concatenates strings with no awareness of SQL syntax. A value like O'Brien breaks the query; 1 OR 1=1 runs as code. glue_sql() routes every value through DBI::dbQuoteLiteral(), which escapes quotes and handles NAs.

What does the * suffix do in a glue_sql() template?

The * after a name ({ids*}) splices the vector into a comma-separated list rather than producing one query per element. It is the standard pattern for IN () clauses and VALUES () lists. Each element of the spliced vector is quoted individually by DBI.

How do I pass a column or table name through glue_sql()?

Wrap the brace contents in backticks inside the template (as in example 3). This switches glue_sql() to DBI::dbQuoteIdentifier(), which uses the right identifier quote for the backend (backticks in SQLite, double quotes in Postgres). For a known-safe fragment, wrap the value in DBI::SQL() to skip quoting.

How does glue_sql() handle NA values?

By default, any NA in an interpolated value becomes NULL in the SQL output. Override the token via the .na argument, for example .na = DBI::SQL("'missing'") to use a sentinel string.

Can I use glue_sql() without a database connection for testing?

Yes, pass DBI::ANSI() as the .con. It is a synthetic connection that applies standard ANSI SQL quoting rules, so glue_sql() can build a representative query without a live driver. Switch back to the real connection before executing, since dialects diverge on identifier quoting and date formats.