Wrap a string in single quotes so it is safe to use as a SQL string literal in dynamic SQL.
QUOTE_LITERAL(str)## Overview Wraps the input string in single quotes and escapes embedded single quotes by doubling them. The result is a syntactically valid SQL string literal that can be interpolated into a dynamically generated query. This is the canonical defense against SQL injection when the value must appear as a literal rather than as an identifier. QUOTE_LITERAL is strict about NULL: a NULL input yields a NULL result, not the string 'NULL'. Use QUOTE_NULLABLE if you want NULL inputs to produce the literal keyword NULL in the generated SQL. ## Behavior - Returns NULL when the input is NULL. - Wraps the input in single quotes. - Escapes embedded single quotes by doubling them. - Does not escape other characters; SQL does not require special handling for them inside a standard string literal. - The result is always a non-empty string when the input is non-NULL (at minimum, two single quotes for an empty input). - Operates on Unicode code points. ## Compatibility - Matches the common SQL QUOTE_LITERAL semantics. - Combines naturally with FORMAT's %L specifier, which applies QUOTE_NULLABLE-like behaviour.
| Name | Type | Description |
|---|---|---|
str | Specifies the string value to quote. Embedded single quotes are escaped by doubling. |
-- Simple string
SELECT QUOTE_LITERAL('hello'); -- '''hello'''-- String with an embedded single quote
SELECT QUOTE_LITERAL('O''Brien'); -- '''O''''Brien'''-- Numeric-looking string is still quoted (it is a string)
SELECT QUOTE_LITERAL('42'); -- '''42'''-- Empty string
SELECT QUOTE_LITERAL(''); -- ''''''-- NULL returns NULL (use QUOTE_NULLABLE to emit the keyword NULL)
SELECT QUOTE_LITERAL(CAST(NULL AS VARCHAR)); -- NULL-- Build a dynamic WHERE clause with literal comparison
SELECT FORMAT('SELECT * FROM retail.customers.profiles WHERE email = %s', QUOTE_LITERAL(@email))
AS dynamic_sql;