Databases

Lecture 9: SQL in software applications

• Lecture notes: chapter 8
• Book: chapter 9
• 

Databases in software applications

End-user database access

• SQL was designed to be a high-level database query language.
  • But it is both too demanding and too powerful to be used directly by most database users.
• Users access databases through via high-level interfaces, e.g. via the web or apps.
  • Examples: ticket booking, money transfer.
• Applications are often built using a combination of SQL and a general purpose programming language
• A database is accessed by embedding SQL statements in a host language.

A typical web service infrastructure

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 Java

• Based on slides by Jonas Duregård.

Database connectivity in Java

Using JDBC

• Load the Postgres driver (or another DBMS driver).
• Initiate a single Connection object.
• Create one or more Statement or PreparedStatement objects.
• Execute queries through statements to get a ResultSet object, from which you can retrieve the results row by row.
• Each of these objects is a resource that needs to be closed after use.
• Any of these steps can fail for various reasons, so error handling is important.
• See the Documentation for details.

Database connectivity in Java

Installing the driver

• Download a driver , e.g. postgresql-42.2.10.jar.
• Make sure postgresql-42.2.10.jar is in your CLASSPATH.
  • Details vary depending on OS, IDE / command line tools.

Database connectivity in Java

Typical main program

• String DATABASE = "jdbc:postgresql://ate.ita.chalmers.se/"; // Load the driver Class.forName("org.postgresql.Driver"); Properties props = new Properties(); props.setProperty("user",USERNAME); props.setProperty("password",PASSWORD); // Open the connection  try (Connection conn = DriverManager.getConnection(DATABASE, props)) { // Actual code goes here … } catch (SQLException e) { // Deal with (unexpected) errors here System.err.println(e); System.exit(2); }

Database connectivity in Java

Database Connection

• For the PostgreSQL server on the Chalmers computers
  • String DATABASE = "jdbc:postgresql://ate.ita.chalmers.se/";
• For a database on your own machine
  • String DATABASE = "jdbc:postgresql://localhost/";
    • Details may vary…
• See the PostgreSQL JDBC Driver documentation:
  • Connecting to the Database

Database connectivity in Java

The try-with-resources statement

• try (Connection conn=DriverManager.getConnection(DATABASE, props)) { actual code goes here }
• It makes sure conn.close() is called, no matter what exceptions are thrown.
• It is a relatively new Java feature (requires Java 7).
• See the Java Tutorials:
  • The try-with-resources Statement

Database connectivity in Java

Executing queries and retrieving the results

• String query = "SELECT idnr,name FROM BasicInformation"; try (Statement s = conn.createStatement()) { // Run the query ResultSet rs = s.executeQuery(query); // Use the next method to retrieve the rows  while(rs.next()) { long id = rs.getLong(1); String name = rs.getString(2); System.out.println(id + " " + name); } }
• The next() method gets the next row, or returns false if there are no more rows in the ResultSet.
• getLong, getString, etc, retrieve values from the current row. See also wasNull.

Database connectivity in Java

If you only expect one row

• String query = "SELECT idnr,name FROM BasicInformation WHERE idnr=2222222222"; try (Statement s = conn.createStatement();) { ResultSet rs = s.executeQuery(query); if(rs.next()) // Always a single call to next() System.out.println(rs.getString(2)); else System.out.println("error, no such student!"); }

Database connectivity in Java

Insert, delete, update, etc.

• For queries (i.e. SELECT statements), use executeQuery().
• For other SQL statements, use executeUpdate().
• Example:
  • String query = "DELETE FROM Registered WHERE student=1111111111"; try (Statement s = conn.createStatement()) { int r = s.executeUpdate(query); System.out.println("Deleted "+r+" registrations."); }

About using strings for SQL statements (1)

• In the examples we saw that SQL is embedded in Java programs in the form of string constants.
• The Java compiler does not know anything about SQL.
• The SQL code is not checked for errors by the Java compiler.
  • Errors in the SQL code are not detected until you run the Java program and the SQL code is sent to the DBMS.

About using strings for SQL statements (2)

• In the examples above we saw things like

• "DELETE FROM Registered WHERE student=1111111111 AND course='ccc111'"

• but a more realistic example would be something like

• "DELETE FROM Registered WHERE student="+idnr+" AND course='"+code+"'"

• It is easy to get some detail wrong! So don't do it like this! There is a better solution!
• This problem is more serious than it might at first appear.

A serious problem

• "DELETE FROM Registered WHERE student="+idnr+" AND course='"+code+"'"

• What if a user provides the following input?
  • idnr=0
  • code="x' OR 'a'='a"

A serious problem

• "DELETE FROM Registered WHERE student="+idnr+" AND course='"+code+"'"

• What if a user provides the following input?
  • idnr=0
  • code="x' OR 'a'='a"
• The result would be the following SQL statement:

• DELETE FROM Registered WHERE student=0 AND course='x' OR 'a'='a'
  

• This is known as an SQL injection.
• Queries constructed in this way can be exploited in endless ways…

SQL injection

• 
• Did you really name your son Robert'); DROP TABLE Students;--?
• bobby-tables.com gives advice on how to avoid SQL injection problems in various languages.

SQL injection attacks

• This was a common problem in the early days of the WWW.
• The problem still shows up every now and then
  • Example: Debian Security Advisory DSA-4629-1 (19 February 2020)

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

• A better way to create SQL statements that prevents SQL injections.
• long idnr = … ; String code = … ;
    String query = "DELETE FROM Registered WHERE student=? AND course=?"; try(PreparedStatement ps = conn.prepareStatement(query));) { ps.setLong(1,idnr); // Replace the first ? ps.setString(2,code); // Replace the second ? ps.executeUpdate(); }
• Put a ? for every parameter in the SQL statement string and use the prepareStatement method.
• Then use methods setLong(), setString(), etc, to replace each ? with a value in a safe way.

Prepared Statements

Use prepared statements!

• Use prepared statements for all queries and statements that contain any kind of user input!
• Good rule of thumbs: always use prepareStatement instead of createStatement().
• A PreparedStatement can be used many times, with different values for the parameters.
  • But only with one ResultSet at a time.

Debugging JDBC code

Getting syntax errors or unexpected results?

• Add some debug printing (or use breakpoints).
• Try running the query manually in psql.
• Remove debugging code before submitting!
• String query = "DELETE FROM Registered WHERE student=? AND course=?"; try(PreparedStatement ps = conn.prepareStatement(query));) { ps.setLong(1,idnr); ps.setString(2,code); System.out.println("Query is: " + ps); // Prints the actual query ps.executeUpdate(); }

Debugging JDBC code, more hints

• Remember: changes to the database are persistent.
  • If your program deletes a registration (deliberately or by accident), it will be gone even if you recompile and re-run the program.
  • Solution: re-run your setup.sql in psql
  • If you need additional tests, add them to your setup.sql
• If both members of the group connect to the same database, you will both see change made by the other member.

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 to access a PostgreSQL database.
• 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 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]

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.

A complete example in Haskell

A mini version of www.imdb.com

• Haskell code: MiniIMDB.hs, SqlRows.hs, HTML.hs, miniimdb.cabal.
• SQL code: movies.sql (tables), import.psql, views.sql, indexes.sql.
• IMDB Database downloads: datasets.imdbws.com

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.
• A few big queries are better than many small queries.
  • Both for efficiency and atomicity.

Concluding remarks

Pitfalls

• Java and Haskell are statically typed languages, but…
• …programming with JDBC/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 run-time type errors.
• Thorough debugging is required!

Concluding remarks

Further reading

• Java
  • There are links to various documentation in these slides.
  • Jonas Duregård's slides about Java programming with jdbc.
• Haskell
  • HDBC: Chapter 21. Using Databases in the book Real World Haskell.
  • Other libraries:
    • hasql and hasql-th, with compile-time syntax checking of embedded SQL statements. Only for PostgreSQL.
    • Selda, has better type safety, using its own SQL-like embedded query language.