SQL made easy and secure with Slick

Slick stands for Scala Language-Integrated Connection Kit. It is Functional Relational Mapping (FRM) library for Scala that makes it easy to work with relational databases.

Slick can be considered as a replacement of writing SQL queries as Strings with a nicer API for handling connections, fetching results and using a query language, which is integrated more nicely into Scala. You can write your database queries in Scala instead of SQL, thus profiting from the static checking, compile-time safety and compositionality of Scala. Slick features an extensible query compiler which can generate code for different backends.

SQL queries are checked at run time only when they are actually executed, but with the help of libraries available in Slick, static checking occurs a compile time, thereby preventing the code to break at runtime i.e. when using Scala instead of raw SQL for your queries you benefit from compile-time safety and compositionality.

Slick’s key features are type-safe, composable queries. Slick comes with a Scala-to-SQL compiler, which allows a (purely functional) sub-set of the Scala language to be compiled to SQL queries. With the availability of standard libraries the Scala developers can write many queries against all supported relational databases with little learning required and without knowing SQL or remembering the particular dialect. Such Slick queries are composable, which means that you can write and re-use fragments and functions to avoid repetitive code like join conditions in a much more practical way than concatenating SQL strings. The fact that such queries are type-safe not only catches many mistakes early at compile time, but also eliminates the risk of SQL injection vulnerabilities.

Scala to SQL compilation during compile time :

Slick runs a Scala-to-SQL compiler to implement its type-safe query feature. The compiler runs at Scala run-time and it does take its time which can even go up to second or longer for complex queries. It can be very useful to run the compiler only once per defined query and upfront, e.g. at app startup instead of each execution over and over. Compiled queries allow you to cache the generated SQL for re-use.

Slick allows you to interact with stored data as if you were working with Scala collections. It has good support for MySQL and PostgreSQL.

To use slick library, if you are using MySQL database, you have to import :

import slick.driver.MySQLDriver

and if you are using Postgres database, you have to import :

import slick.driver.PostgresDriver

When combined with Lifted Embedding, Slick gives some protection against SQLInjection. However, it also allows you to drop back to plain SQL if you need to, which introduces the risk of SQL Injection.

What SQL Injection is?

SQL Injection (SQLi) refers to an injection attack wherein an attacker can execute malicious SQL statements that control a web application’s database server. Since an SQL Injection vulnerability could possibly affect any website or web application that makes use of an SQL-based database.

By leveraging an SQL Injection vulnerability, given the right circumstances, an attacker can use it to bypass a web application’s authentication and authorization mechanisms and retrieve the contents of an entire database. SQL Injection can also be used to add, modify and delete records in a database, affecting data integrity.

To such an extent, SQL Injection can provide an attacker with unauthorized access to sensitive data including, customer data, personal information, trade secrets, intellectual property and other sensitive information.

Lifted Embedding : Slick takes your collections and converts or “lifts” them into Rep objects. The original type is preserved and passed into the constructor to Rep, allowing for type safety and clean separation between code and data when communicating with the database. The example below demonstrates how Slick views your Int, String and Double values after lifting occurs.

Firstly you have to write a query and instead of creating an Action that gets the result of this query, call += on with value to be inserted, which gives you an Action that performs the insert. ++= allows insertion of a Seq of rows at once.