Writing queries

SQL-based applications are somewhat notorious for their
susceptibility to attacks through the injection of maliciously
crafted data. The primary reason for widespread vulnerability to
SQL injections is that many applications are sloppy in handling
user data when constructing SQL queries.

This library provides a Query type and a parameter substitution
facility to address both ease of use and security.

The Query type

A Query is a newtype-wrapped ByteString. It intentionally
exposes a tiny API that is not compatible with the ByteString
API; this makes it difficult to construct queries from fragments of
strings. The query and execute functions require queries to be
of type Query.

To most easily construct a query, enable GHC's OverloadedStrings
language extension and write your query as a normal literal string.

A Query value does not represent the actual query that will be
executed, but is a template for constructing the final query.

Parameter substitution

Since applications need to be able to construct queries with
parameters that change, this library provides a query substitution
capability.

The Query template accepted by query and execute can contain
any number of "?" characters. Both query and execute
accept a third argument, typically a tuple. When constructing the
real query to execute, these functions replace the first "?" in
the template with the first element of the tuple, the second
"?" with the second element, and so on. If necessary, each
tuple element will be quoted and escaped prior to substitution;
this defeats the single most common injection vector for malicious
data.

If there is a mismatch between the number of "?" characters in
your template and the number of elements in your tuple, a
FormatError will be thrown.

Note that the substitution functions do not attempt to parse or
validate your query. It's up to you to write syntactically valid
SQL, and to ensure that each "?" in your query template is
matched with the right tuple element.

Type inference

Automated type inference means that you will often be able to avoid
supplying explicit type signatures for the elements of a tuple.
However, sometimes the compiler will not be able to infer your
types. Consider a care where you write a numeric literal in a
parameter tuple:

query conn "select ? + ?" (40,2)

The above query will be rejected by the compiler, because it does
not know the specific numeric types of the literals 40 and 2.
This is easily fixed:

query conn "select ? + ?" (40 :: Double, 2 :: Double)

The same kind of problem can arise with string literals if you have
the OverloadedStrings language extension enabled. Again, just
use an explicit type signature if this happens.

Substituting a single parameter

Haskell lacks a single-element tuple type, so if you have just one
value you want substituted into a query, what should you do?

The obvious approach would appear to be something like this:

instance (Param a) => QueryParam a where
...

Unfortunately, this wreaks havoc with type inference, so we take a
different tack. To represent a single value val as a parameter, write
a singleton list [val], use Justval, or use Onlyval.

Here's an example using a singleton list:

execute conn "insert into users (first_name) values (?)"
["Nuala"]

Representing a list of values

Suppose you want to write a query using an IN clause:

select * from users where first_name in ('Anna', 'Boris', 'Carla')

In such cases, it's common for both the elements and length of the
list after the IN keyword to vary from query to query.

To address this case, use the In type wrapper, and use a single
"?" character to represent the list. Omit the parentheses
around the list; these will be added for you.

The number of columns we ask for in the query template must
exactly match the number of elements we specify in a row of the
result tuple. If they do not match, a ResultError exception
will be thrown.

Sometimes, the compiler needs our help in specifying types. It
can infer that name must be a Text, due to our use of the
unpack function. However, we have to tell it the type of age,
as it has no other information to determine the exact type.

Handling null values

The type of a result tuple will look something like this:

(Text, Int, Int)

Although SQL can accommodate NULL as a value for any of these
types, Haskell cannot. If your result contains columns that may be
NULL, be sure that you use Maybe in those positions of of your
tuple.

(Text, Maybe Int, Int)

If query encounters a NULL in a row where the corresponding
Haskell type is not Maybe, it will throw a ResultError
exception.

Type conversions

Conversion of SQL values to Haskell values is somewhat
permissive. Here are the rules.

For numeric types, any Haskell type that can accurately represent
all values of the given MySQL type is considered "compatible".
For instance, you can always extract a MySQL TINYINT column to
a Haskell Int. The Haskell Float type can accurately
represent MySQL integer types of size up to INT24, so it is
considered compatble with those types.

A numeric compatibility check is based only on the type of a
column, not on its values. For instance, a MySQL LONG_LONG
column will be considered incompatible with a Haskell Int8,
even if it contains the value 1.

The String and Text types are assumed to be encoded as
UTF-8. If you use some other encoding, decoding may fail or give
wrong results. In such cases, write a newtype wrapper and a
custom Result instance to handle your encoding.

A query string. This type is intended to make it difficult to
construct a SQL query by concatenating string fragments, as that is
an extremely common way to accidentally introduce SQL injection
vulnerabilities into an application.

This type is an instance of IsString, so the easiest way to
construct a query is to enable the OverloadedStrings language
extension and then simply write the query in double quotes.

Transaction handling

This function initiates a transaction with a "begin
transaction" statement, then executes the supplied action. If
the action succeeds, the transaction will be completed with
commit before this function returns.

If the action throws any kind of exception (not just a
MySQL-related exception), the transaction will be rolled back using
rollback, then the exception will be rethrown.

Helper functions

This function is exposed to help with debugging and logging. Do not
use it to prepare queries for execution.

The query string must contain exactly one substitution group,
identified by the SQL keyword "VALUES" (case insensitive)
followed by an "(" character, a series of one or more "?"
characters separated by commas, and a ")" character. White
space in a substitution group is permitted.

Throws FormatError if the query string could not be formatted
correctly.