sqlutil

sqlutil quotes SQL identifiers and string literals for dynamic query fragments, with explicit limits on where value quoting is safe.

-
Updated:

Table of contents

Part of nurago, a collection of independent Go packages for backend services.

import "github.com/tecnickcom/nurago/pkg/sqlutil"

Quotes identifiers and literals when a query has to be assembled rather than parameterised, and builds IN and NOT IN clauses from typed slices.

s := sqlutil.New()

s.QuoteID("myschema.users.name")            // `myschema`.`users`.`name`
s.BuildInClauseInt64("id", []int64{1,2,3})  // id IN (1,2,3)

Placeholders cannot quote an identifier

A prepared statement binds values. It cannot bind a table name, a column name, or an ORDER BY direction, because those are parsed before the plan exists.

So a query with a user-selected sort column, a multi-tenant schema name, or a column list built at runtime has to be assembled as text, and the identifiers have to be quoted correctly. That is the job this package does.

The default is MySQL-like: identifiers split on . with each segment wrapped in backticks, and embedded backticks doubled. Inside backtick quotes the backtick is the only special character, so nothing else needs escaping.

WithQuoteIDFunc and WithQuoteValueFunc adapt the rules to PostgreSQL, SQLite, or another dialect.

Where value quoting is and is not safe

QuoteValue wraps a string in single quotes, doubles embedded single quotes, and escapes control characters (\0, \n, \r, \\, \Z). That is correct for MySQL-like databases in the default SQL mode, over an ASCII-compatible single-byte-safe connection charset such as utf8mb4 or latin1.

The package names two conditions where it is not safe for untrusted input, and both are worth understanding rather than skimming.

NO_BACKSLASH_ESCAPES mode. In this SQL mode the backslash is not an escape character. The backslash escaping applied here is then interpreted literally, which silently corrupts the stored value. The failure is data corruption rather than injection, and it is silent.

Non-self-synchronising multibyte charsets. GBK, Big5, SJIS and similar encodings have multibyte characters whose lead byte can be a value that byte-wise escaping treats as ordinary. A crafted lead byte consumes the escaping backslash, leaving the quote it was supposed to escape as an active quote. This is the classic escape-function SQL injection, and it is why escaping was abandoned in favour of parameter binding in the first place.

Both conditions are properties of the connection and the server, not of the input, so neither can be detected here.

Use bound parameters for untrusted data. This package is for the identifiers and fragments that placeholders cannot express. A value that came from a request belongs in a placeholder.

The IN clause builders

BuildInClauseString, BuildInClauseInt, BuildInClauseInt64, BuildInClauseUint and their NotIn counterparts take a field and a typed slice.

The typing is the safety property. BuildInClauseInt64 cannot produce injection from its values, whatever they contain, because an int64 has no textual representation that escapes the clause. Where the identifiers are trusted and the values are integers, the result is safe by construction.

BuildInClauseString routes its values through QuoteValue and therefore carries the limits above.

These exist because IN with a variable-length list is the one case placeholders handle badly: database/sql has no way to expand a slice into a placeholder list, so it is either string assembly or generating ?, ?, ? by hand and flattening the arguments.

Dependencies

This package reaches no external module: it uses only the Go standard library.