Portability | portable |
---|---|
Stability | experimental |
Maintainer | Janne Hellsten <jjhellst@gmail.com> |
Safe Haskell | None |
- newtype Query = Query {}
- data Connection
- class ToRow a where
- class FromRow a where
- newtype Only a = Only {
- fromOnly :: a
- data h :. t = h :. t
- data SQLData
- = SQLInteger !Int64
- | SQLFloat !Double
- | SQLText !Text
- | SQLBlob !ByteString
- | SQLNull
- open :: String -> IO Connection
- close :: Connection -> IO ()
- query :: (ToRow q, FromRow r) => Connection -> Query -> q -> IO [r]
- query_ :: FromRow r => Connection -> Query -> IO [r]
- execute :: ToRow q => Connection -> Query -> q -> IO ()
- execute_ :: Connection -> Query -> IO ()
- field :: FromField a => RowParser a
- data FormatError
- data ResultError
Examples of use
Create a test database by copy pasting the below snippet to your shell:
sqlite3 test.db "CREATE TABLE test (id INTEGER PRIMARY KEY, str text); \ INSERT INTO test (str) VALUES ('test string');"
..and access it from Haskell:
{-# LANGUAGE OverloadedStrings #-} import Control.Applicative import Database.SQLite.Simple import Database.SQLite.Simple.FromRow data TestField = TestField Int String deriving (Show) instance FromRow TestField where fromRow = TestField <$> field <*> field main :: IO () main = do conn <- open "test.db" execute conn "INSERT INTO test (str) VALUES (?)" (Only ("test string 2" :: String)) r <- query_ conn "SELECT * from test" :: IO [TestField] mapM_ print r close conn
The Query type
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. A Query
is a
newtype
-wrapped Text
. It intentionally exposes a tiny API that
is not compatible with the Text
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.
{-# LANGUAGE OverloadedStrings #-} import Database.SQLite.Simple hello = do conn <- open "test.db" query conn "select 2 + 2"
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 uses SQLite's parameter binding 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.
For example, given the following Query
template:
select * from user where first_name = ? and age > ?
And a tuple of this form:
("Boris" :: String, 37 :: Int)
The query to be executed will look like this after substitution:
select * from user where first_name = 'Boris' and age > 37
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.
This library restricts parameter substitution to work only with
"?
" characters. SQLite natively supports several other
syntaxes for binding query parameters. Unsupported parameters will
be rejected and a FormatError
will be thrown.
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 case 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?
To represent a single value val
as a parameter, write a singleton
list [val]
, use Just
val
, or use Only
val
.
Here's an example using a singleton list:
execute conn "insert into users (first_name) values (?)" ["Nuala"]
Extracting results
The query
and query_
functions return a list of values in the
FromRow
typeclass. This class performs automatic extraction
and type conversion of rows from a query result.
Here is a simple example of how to extract results:
import qualified Data.Text as T xs <- query_ conn "select name,age from users" forM_ xs $ \(name,age) -> putStrLn $ T.unpack name ++ " is " ++ show (age :: Int)
Notice two important details about this code:
- 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 aText
, due to our use of theunpack
function. However, we have to tell it the type ofage
, 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 an SQLite INTEGER is considered "compatible".
- If a numeric incompatibility is found,
query
will throw aResultError
. - SQLite's TEXT type is always encoded in UTF-8. Thus any text
data coming from an SQLite database should always be compatible
with Haskell
String
andText
types. - SQLite's BLOB type will only be conversible to a Haskell
ByteString
.
You can extend conversion support to your own types be adding your
own FromField
/ ToField
instances.
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.
{-# LANGUAGE OverloadedStrings #-} import Database.PostgreSQL.Simple q :: Query q = "select ?"
The underlying type is a Text
, and literal Haskell strings that
contain Unicode characters will be correctly transformed to UTF-8.
data Connection Source
Connection to an open database.
A collection type that can be turned into a list of SQLData elements.
ToRow () | |
ToField a => ToRow [a] | |
ToField a => ToRow (Only a) | |
(ToField a, ToField b) => ToRow (a, b) | |
(ToRow a, ToRow b) => ToRow (:. a b) | |
(ToField a, ToField b, ToField c) => ToRow (a, b, c) | |
(ToField a, ToField b, ToField c, ToField d) => ToRow (a, b, c, d) | |
(ToField a, ToField b, ToField c, ToField d, ToField e) => ToRow (a, b, c, d, e) | |
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f) => ToRow (a, b, c, d, e, f) | |
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g) => ToRow (a, b, c, d, e, f, g) | |
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h) => ToRow (a, b, c, d, e, f, g, h) | |
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i) => ToRow (a, b, c, d, e, f, g, h, i) | |
(ToField a, ToField b, ToField c, ToField d, ToField e, ToField f, ToField g, ToField h, ToField i, ToField j) => ToRow (a, b, c, d, e, f, g, h, i, j) |
A collection type that can be converted from a sequence of fields. Instances are provided for tuples up to 10 elements and lists of any length.
Note that instances can defined outside of sqlite-simple, which is often useful. For example, here's an instance for a user-defined pair:
data User = User { name :: String, fileQuota :: Int } instanceFromRow
User where fromRow = User <$>field
<*>field
The number of calls to field
must match the number of fields returned
in a single row of the query result. Otherwise, a ConversionFailed
exception will be thrown.
Note the caveats associated with user-defined implementations of
fromRow
.
A single-value "collection".
This is useful if you need to supply a single parameter to a SQL query, or extract a single column from a SQL result.
Parameter example:
query c "select x from scores where x > ?" (Only
(42::Int))
Result example:
xs <- query_ c "select id from users"
forM_ xs $ \(Only
id) -> {- ... -}
A composite type to parse your custom data structures without having to define dummy newtype wrappers every time.
instance FromRow MyData where ...
instance FromRow MyData2 where ...
then I can do the following for free:
res <- query' c ... forM res $ \(MyData{..} :. MyData2{..}) -> do ....
h :. t |
data SQLData
Connections
open :: String -> IO ConnectionSource
Open a database connection to a given file. Will throw an exception if it cannot connect.
Every open
must be closed with a call to close
.
If you specify ":memory:" or an empty string as the input filename, then a private, temporary in-memory database is created for the connection. This database will vanish when you close the connection.
close :: Connection -> IO ()Source
Close a database connection.
Queries that return results
query :: (ToRow q, FromRow r) => Connection -> Query -> q -> IO [r]Source
Perform a SELECT
or other SQL query that is expected to return
results. All results are retrieved and converted before this
function returns.
When processing large results, this function will consume a lot of
client-side memory. Consider using fold
instead.
Exceptions that may be thrown:
-
FormatError
: the query string mismatched with given arguments. -
ResultError
: result conversion failed.
query_ :: FromRow r => Connection -> Query -> IO [r]Source
A version of query
that does not perform query substitution.
Statements that do not return results
execute :: ToRow q => Connection -> Query -> q -> IO ()Source
Execute an INSERT
, UPDATE
, or other SQL query that is not
expected to return results.
Throws FormatError
if the query could not be formatted correctly.
execute_ :: Connection -> Query -> IO ()Source
A version of execute
that does not perform query substitution.
Exceptions
data FormatError Source
Exception thrown if a Query
was malformed.
This may occur if the number of '?
' characters in the query
string does not match the number of parameters provided.
data ResultError Source
Exception thrown if conversion from a SQL value to a Haskell value fails.