Search code examples
sqlscalah2slick

Single quotes cause trouble while filtering in Slick

I have statements such as below

  def create(company: Company): Future[Company] = {
    dbConfig.db.run(companies.filter(_.name === company.name).result.headOption)
}

and they fail with exceptions such as this

org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement

I have tried to escape the single quote but wasn't successful.

dbConfig.db.run(companies.filter(_.name === company.name.replace("'", "''")).result.headOption)

When I tried to insert a record such as this:

val company = doSync(companies.create(Company(0, "C's company")))

The exception I've gotten is:

Syntax error in SQL statement "select `id`, `name` from `company` where `name` = 'C\'s company[*]'"; SQL statement:
select `id`, `name` from `company` where `name` = 'C\'s company' [42000-200]
org.h2.jdbc.JdbcSQLSyntaxErrorException: Syntax error in SQL statement "select `id`, `name` from `company` where `name` = 'C\'s company[*]'"; SQL statement:
select `id`, `name` from `company` where `name` = 'C\'s company' [42000-200]

Please note that I am using H2 in Mysql mode to run my tests.

Solution

  • That error suggests to me that you have the wrong Slick profile imported. I'm not familiar with H2 in Mysql mode. Perhaps you need to use the MySQL profile? (Or the H2 one if you're using MySQL)