Anorm: Escape Single Quote

I'm using anorm for simple db crude, there is an issue with silent fail when the input parameter contains single quote. Eg surname "O'Brien" or "O'Neill" in user table. I attempted to use the double quote to escape the single quote in the query, this removes the silent fail however the database entry contains surname "O''Brien" and "O''Neill". What is the proper way to escape single quote?

The code looks like (sorry please bear with formatting tabs in SO):

def insertNewUser(user: User): \/[Exception, UUID] = DB.withConnection { implicit connection =>
val updatedRowCount =
  SQL"""insert into user(id, email, first_name, surname)
        values (${user.id}::uuid, ${user.email}, ${sanitiseSqlQueryInput(user.firstName)},
        ${sanitiseSqlQueryInput(user.surname)})""".executeUpdate()
updatedRowCount match {
  case 1 =>
    log.debug(s"New user is successfully created [userId=${user.id.toString}]")
    \/-(user.id)
  case _ => -\/(new Exception(s"""Fail to create new user [id=${user.id.toString}] [email=${user.email}] [user=$user]"""))
}}

def sanitiseSqlQueryInput(s: String): String = s.replace("'", "''")

Thanks in advance!

My response may be limited as I cannot comment yet.

Solution

Using the SQL interpolator, you don't need to. Anorm creates a prepared statement for you. Note the parameter mapping in the result res0:

scala> val name = "O'Neill"
name: String = O'Neill

scala> SQL"""SELECT ${name}"""
res0: anorm.SimpleSql[anorm.Row] = SimpleSql(anorm.SqlQuery$$anon$1@6e7633d,Map(_0 -> ParameterValue(O'Neill)),<function1>,false)
                                                                                  ^
                                                                 // --------------|

You can simply write:

SQL"""
  insert into user (id, email, first_name, surname) values 
  (${user.id}, ${user.email}, ${user.firstName}, ${user.surname})
   """.executeUpdate()