postgresql-orm-0.3.0: An ORM (Object Relational Mapping) and migrations DSL for PostgreSQL.

Safe HaskellTrustworthy

Database.PostgreSQL.Escape

Description

This module deals with escaping and sanitizing SQL templates.

Synopsis

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 "?string", as these could get expanded to, e.g., "'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.

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

A lower-level function used by buildSql and fmtSql. You probably don't need to call it directly.

buildIdent :: ByteString -> BuilderSource

Build a quoted identifier. Generally you will want to use quoteIdent, and for repeated use it will be faster to use fromByteString . quoteIdent, but this internal function is exposed in case it is useful.