glue single_quote() in R: Wrap Strings for SQL Literals

glue single_quote() wraps each element of a character vector in single quotes and escapes any embedded single quote (apostrophe) with a backslash. It is the SQL-92 standard way to quote a string literal, and it is the helper to reach for when you build SQL WHERE clauses, parsed R expressions, or any text that must contain a quoted literal alongside an apostrophe.

single_quote("hi")                            # 'hi'  with surrounding quotes
single_quote(c("a", "b"))                     # vectorised over input
single_quote("O'Brien")                       # escapes inner apostrophe
single_quote(NA)                              # NA passes through unchanged
single_quote("")                              # empty string becomes ''
single_quote(as.character(c(1, 2)))           # coerce numbers first
paste0("name = ", single_quote(nm))           # build a SQL fragment

What single_quote() does in one sentence

single_quote() takes a character vector and returns it with each element wrapped in single quotes and any internal apostrophe escaped with a backslash. It is a one-line helper exported by the glue package and is the underlying machinery behind glue_sql() when it quotes string literals against a database connection.

The function is vectorised over its input, NA-safe, and changes nothing about the string apart from adding the surrounding ' characters and escaping any inner '. There is no template syntax, no formatter logic, no connection awareness. Once you reach for paste0("'", x, "'") to build SQL or generated R code, single_quote() is the safer replacement because it handles the awkward apostrophe-inside-string case for free.

The result is a character vector of the same length as the input, with each element padded by single quotes. The outer R-level double quotes you see in the printed output are how R displays a character string; the value itself contains the literal single quotes.

Syntax

The signature is minimal because the job is small.

One argument, no options, output length always matches input length. NA inputs return NA, not the string 'NA', which matters when you assemble SQL because the literal text 'NA' is almost never what you want.

Common use cases

1. Build a SQL WHERE clause for a string column

The most frequent reason to call single_quote() is to wrap a string value before it slots into a SQL fragment. Single quotes are the SQL-92 standard for string literals, so a portable WHERE clause uses them regardless of database engine.

The output is a glue object that prints without R-level escaping, so it pastes straight into a SQL string and the database sees a properly quoted literal.

2. Escape an embedded apostrophe in a name

Names like O'Brien, D'Souza, and Cote d'Ivoire contain an apostrophe that doubles as the SQL string-literal delimiter. single_quote() escapes that internal apostrophe automatically so you never construct broken SQL.

The doubled backslash is R's print form for a single backslash plus apostrophe; the on-the-wire SQL receives 'O\'Brien', which most engines accept. For strict SQL-92 doubling ('O''Brien') use glue_sql() with a connection.

3. Vectorise over a column to build an IN clause

Because single_quote() is vectorised, you can wrap every element of a column in one call and then collapse the result into a comma-separated IN clause.

The two-step approach (wrap, then collapse) reads more clearly than a nested expression and keeps the quoted vector reusable.

4. Quote values inside a generated R expression

Generated R code needs quoted string literals too. R, like SQL, accepts single quotes around character strings, so single_quote() output drops into a parseable expression unchanged.

Pass expr_text to parse(text = expr_text) followed by eval(), or write it into a generated script. The single quotes around treatment make the literal valid R syntax.

5. Pair with glue_sql() for connection-aware quoting

For production SQL, prefer glue_sql() because it knows the database engine. single_quote() still shines for offline tests, fixtures, and fragments you splice into a larger template later.

The offline fragment is deterministic and never reaches the database, so it works for assertion tests. Against a live connection, swap to glue_sql() so the engine's preferred escape style takes over.

single_quote() vs shQuote() and paste0()

Three R functions can put single quotes around a string, and they are not interchangeable. Pick the one that matches the consumer of the output.

The shell variant uses the POSIX close-escape-reopen trick: right for system2() arguments, wrong for SQL. The paste0() variant looks fine until the data contains an apostrophe, when it silently produces malformed output.

Common pitfalls

NA flows through as NA, not as the string 'NA'. Splice that NA into SQL with paste() and you get the literal text NA instead of the keyword NULL. Convert NA explicitly with ifelse(is.na(x), "NULL", single_quote(x)) before assembly.

Numeric input errors. single_quote(123) fails because the input is not character. Coerce with as.character() first, or skip quoting entirely; SQL accepts numeric literals unquoted and quoting them can defeat numeric indexes.

Backslash escaping is not universal. Strict SQL-92 engines (PostgreSQL with standard_conforming_strings = on) reject \\' and require apostrophe doubling. Route through glue_sql() with a live connection on those engines.

Try it yourself

Related glue functions

• double_quote(x) wraps in escaped double quotes; pair with this when you need identifier quoting on engines that accept doubles.
• backtick(x) wraps in backticks; the right choice for SQL identifiers on MySQL and SQLite.
• glue_sql(.con = con, ...) is the connection-aware template that routes both literal and identifier quoting through the database driver.
• glue() is the underlying template engine; single_quote() output drops into a glue template without further escaping.
• as_glue() promotes a plain character to a glue object so print methods render without R-level quoting.

FAQ

What does glue single_quote() do?

It takes a character vector and returns the same vector with each element wrapped in single quotes and any internal apostrophe escaped with a backslash. The function is exported by the glue package and powers glue_sql() string-literal quoting. It is vectorised, NA-safe, and has no options.

When should I use single_quote() instead of glue_sql()?

Use single_quote() when no live database connection is available (offline tests, fixtures, code generation) or when you assemble a fragment to splice into a larger glue_sql() template later. With a DBI connection in scope, prefer glue_sql() because it routes the escape through the driver.

Why does single_quote("O'Brien") show two backslashes?

The double backslash is R's print representation of a single backslash. The character vector actually holds one backslash followed by one apostrophe inside the surrounding single quotes. Write the value to a file or pass it to cat() and you will see the on-the-wire form, 'O\'Brien', with a single backslash.

Is single_quote() safe against SQL injection?

Not on its own. It handles apostrophe-in-string for many engines but strict SQL-92 engines reject backslash escaping. For untrusted input, route through glue_sql() with a live connection or use parameterised queries via DBI directly.

Does single_quote() work on numeric vectors?

No. It errors on non-character input. Coerce with as.character() first if you really need to quote a number, but usually you should leave numeric columns unquoted because SQL accepts them as numeric literals.