Databases in software applications

Database connectivity

To connect to a database from an application, we need software libraries in the language the application is developed in.
All major programming languages have at least one such library.
- For Java: JDBC
- For Haskell: HDBC
They can connect to different DBMS by using different drivers.
They provide a common interface for running queries and processing their results in application programs.

Database connectivity in Haskell

HDBC

Common interface:
- Functions for sending SQL statements to the database and retrieving query results.
- ```
import Databases.HDBC
```
Drivers for different databases:
- Functions for connecting to the database.
- Other DBMS-specific functionality.
- ```
import Databases.HDBC.PostgreSQL
```
- ```
import Databases.HDBC.Sqlite3
```

Installing HDBC

Packages on Hackage:
- HDBC, HDBC-postgresql, HDBC-sqlite3, …
- cabal install HDBC-postgresql
  - installs what you need for the assignment.
Documentation
- The above packages come with the usual Haddock-generated documentation.
- For an introduction, see Chapter 21. Using Databases in the book Real World Haskell.

Programming with HDBC

Connecting to a PostgreSQL database

connectPostgreSQL :: String -> IO Connection

The argument is the database connection string
- Key-value pairs that specify server address, user name and password, database name, ...
- See the PostgreSQL documentation for details.

Example:

import Databases.HDBC.PostgreSQL

main = do conn <- connectPostreSQL "dbname=countries"
       -- ...

Programming with HDBC

Running statements (without query results)

run    :: Connection -> String -> [SqlValue] -> IO Integer
commit :: Connection -> IO ()

Example:

import Databases.HDBC.Postgres
import Databases.HDBC

main =
  do conn <- connectPostreSQL "dbname=countries"
     run conn "UPDATE Currencies SET value=10.61 WHERE code='EUR'" []
     commit

Programming with HDBC

Transactions

Instead of

commit   :: Connection -> IO ()
rollback :: Connection -> IO ()

it is better to use

withTransaction :: Connection -> (Connection -> IO a) -> IO a

Example:

main =
  do conn <- connectPostreSQL "dbname=countries"
     withTransaction conn $ \ conn ->
       do run conn "UPDATE Currencies SET value=10.61 WHERE code='EUR'" []
          (...)

Does
```
commit
```
if everyting went OK,
```
rollback
```
if there was an error.

Programming with HDBC

Running statements and retreiving query results

quickQuery' :: Connection -> String -> [SqlValue] -> IO [[SqlValue]]

Example:

import Databases.HDBC.Postgres
import Databases.HDBC

main =
  do conn <- connectPostreSQL "dbname=countries"
     rows <- quickQuery' conn query []
     print rows
  where
    query = "SELECT name,population FROM Countries WHERE continent='EU'"

Programming with HDBC

SqlValue

quickQuery' :: Connection -> String -> [SqlValue] -> IO [[SqlValue]]

All values sent to and retrieved from the database have type
```
SqlValue
```
.
The overloaded functions
```
toSql
```
and
```
fromSql
```
convert between
```
SqlValue
```
and normal Haskell types.

Strings with placeholders

getContinent :: Connection -> String -> IO [(String,Int)]
getContinent conn continent =
    map convertRow <$> quickQuery' conn query [toSql continent]
  where
    query = "SELECT name,population FROM Countries WHERE continent=?"
    
    convertRow :: [SqlValue] -> (String,Int)
    convertRow [name,pop] = (fromSql name,fromSql pop)

The query is a string with placeholders.
The DBMS will replace the placeholders with values from the list, creating a syntactically correct SQL statement regardless of the value.
Don't create query strings by concatenating pieces!

SQL injection

bobby-tables.com

SQL injection example

Bad programming

unregisterStudent :: Connection -> String -> String -> IO ()
unregisterStudent conn student course =
  run conn ("DELETE FROM Registered "++
            "WHERE course='"++course++"' AND student='"++student++"'") []

What if some does this:

unregisterStudent conn "TDA357" "x' OR 'a'='a"

Queries constructed in this way can be exploited in endless ways…

SQL injection example

Good programming

unregisterStudent :: Connection -> String -> String -> IO ()
unregisterStudent conn student course =
  run conn "DELETE FROM Registered WHERE course=? AND student=?"
           [toSql course,toSql student]

An unusual SQL injection example

In the 2010 Swedish election, someone wrote "DROP TABLE VALJ;" on their voting ballot
The text of the ballot was then manually entered into a computer system by election workers (as a non-registered party name)
The attack was not successful, but the vote can still be found in public records: handskrivna

Prepared Statements

The function
```
run
```
for running statements again:

run :: Connection -> String -> [SqlValue] -> IO Integer

This can be split into two steps:

prepare     :: Connection -> String -> IO Statement

execute     :: Statement -> [SqlValue]   -> IO Integer
executeMany :: Statement -> [[SqlValue]] -> IO Integer

Example:

main =
  do conn <- (...)
     stmt <- prepare conn "INSERT INTO Currencies VALUES (?, ?)"
     executeMany stmt [[toSql "EUR", toSql 10.55],
                       [toSql "GBP", toSql 12.15],
                       [toSql "USD", toSql 9.31]]

Prepared Statements (2)

The function
```
quickQuery'
```
again:

quickQuery' :: Connection -> String -> [SqlValue] -> IO [[SqlValue]]

This can also be split into smaller steps:

prepare       :: Connection -> String -> IO Statement
execute       :: Statement -> [SqlValue]   -> IO Integer
fetchAllRows' :: Statement -> IO [[SqlValue]]

fetchRow      :: Statement -> IO [SqlValue]

A
```
Statement
```
is a reference to an object with an internal state that is modified by
```
execute
```
and the fetch… functions.

Examples

Task 4 in the assignment.
A mini version of www.imdb.com:
- MiniIMDB.hs, HTML.hs views.sql (continued from Lecture 7).

Concluding remarks

Recommendations

Minimize the amount of embedded SQL code in your application.
- Add views in your database to simplify queries.
Do not assemble query strings by concatenating pieces.
- Use query strings with placeholders (prepared statements) to avoid SQL injections.

Concluding remarks

HDBC pitfalls

Haskell is a statically typed language, but…
…programming with HDBC is like programming in a dynamically typed language.
Programmer mistakes result in run-time errors. For example:
- Syntactically incorrect SQL code in a query string is rejected by the DBMS.
- The result of a query isn't of the expected type, causing fromSql to fail.
Thorough debugging is required!

Databases (TDA357 / DIT621)

Databases in software applications

Databases (TDA357 / DIT621)

Today

Databases in software applications

Databases in software applications

Database connectivity

Database connectivity in Haskell

HDBC

Installing HDBC

Programming with HDBC

Connecting to a PostgreSQL database

Programming with HDBC

Running statements (without query results)

Programming with HDBC

Transactions

Programming with HDBC

Running statements and retreiving query results

Programming with HDBC

SqlValue

Strings with placeholders

SQL injection

SQL injection example

Bad programming

SQL injection example

Good programming

An unusual SQL injection example

Prepared Statements

Prepared Statements (2)

Examples

Concluding remarks

Recommendations

Concluding remarks

HDBC pitfalls

Concluding remarks

Further reading