Safe Haskell | Trustworthy |
---|
This module deals with escaping and sanitizing SQL templates.
- fmtSql :: ToRow p => Query -> p -> Query
- quoteIdent :: ByteString -> ByteString
- newtype Id = Id ByteString
- buildSql :: ToRow p => Query -> p -> Builder
- buildSqlFromActions :: Query -> [Action] -> Builder
- buildAction :: Action -> Builder
- buildLiteral :: ByteString -> Builder
- buildByteA :: ByteString -> Builder
- buildIdent :: ByteString -> Builder
Documentation
fmtSql :: ToRow p => Query -> p -> QuerySource
Take a SQL template containing '?' characters and a list of
paremeters whose length must match the number of '?' characters,
and format the result as an escaped ByteString
that can be used
as a query.
Like formatQuery
, this function is naive about the placement of
'?' characters and will expand all of them, even ones within
quotes. To avoid this, you must use quoteIdent
on identifiers
containing question marks.
Also like formatQuery
, '?' characters touching other '?'
characters or quoted strings may do the wrong thing, and end up
doubling a quote, so avoid substrings such as "??"
or
"?
, as these could get expanded to, e.g.,
string
""'param''string'"
, which is a single string containing an
apostrophe, when you probably wanted two strings.
quoteIdent :: ByteString -> ByteStringSource
Quote an identifier such as a table or column name using
double-quote characters. Note this has nothing to do with quoting
values, which must be quoted using single quotes. (Anyway, all
values should be quoted by query
or fmtSql
.) This function
uses a unicode escape sequence to escape '?' characters, which
would otherwise be expanded by query
, formatQuery
, or fmtSql
.
>>>
S8.putStrLn $ quoteIdent "hello \"world\"!"
"hello ""world""!">>>
S8.putStrLn $ quoteIdent "hello \"world\"?"
U&"hello ""world""\003f"
Note that this quoting function is correct only if
client_encoding
is SQL_ASCII
, client_coding
is UTF8
, or the
identifier contains no multi-byte characters. For other coding
schemes, this function may erroneously duplicate bytes that look
like quote characters but are actually part of a multi-byte
character code. In such cases, maliciously crafted identifiers
will, even after quoting, allow injection of arbitrary SQL commands
to the server.
The upshot is that it is unwise to use this function on identifiers
provided by untrustworthy sources. Note this is true anyway,
regardless of client_encoding
setting, because certain "system
column" names (e.g., oid
, tableoid
, xmin
, cmin
, xmax
,
cmax
, ctid
) are likely to produce unexpected results even when
properly quoted.
See Id
for a convenient way to include quoted identifiers in
parameter lists.
An identifier is a table or column name. When rendered into a
SQL query by fmtSql
, it will be double-quoted, rather than
single-quoted. For example:
>>>
fmtSql "select * from ? where name = ?" (Id "MyTable", "A Name")
"select * from \"MyTable\" where name = E'A Name'"
buildSql :: ToRow p => Query -> p -> BuilderSource
A builder version of fmtSql
, possibly useful if you are about
to concatenate various individually formatted query fragments and
want to save the work of concatenating each individually.
buildSqlFromActions :: Query -> [Action] -> BuilderSource
buildAction :: Action -> BuilderSource
buildIdent :: ByteString -> BuilderSource
Build a quoted identifier. Generally you will want to use
quoteIdent
, and for repeated use it will be faster to use
, but this internal function is
exposed in case it is useful.
fromByteString
. quoteIdent