SQL statement with Anorm gives me an other result than in PostgreSQL CLI

Question

I want to check if something is present in my database before saving it in order to avoid key duplicate errors. I'm using Play! 2.2.6 with anorm and Postgresql 9.3.

So I wrote a little function (I omit the errors check):

def testIfExist(fieldName: String, value: String): Boolean = {
  DB.withConnection { implicit connection =>
    SQL( """SELECT exists(SELECT 1 FROM events where {fieldName}={value} LIMIT 1)""")
      .on(
        'fieldName -> fieldName,
        'value -> value
      ).execute()
  }
}

But it always return true although my database is totally empty.

So I tested to replace

SELECT exists(SELECT 1 FROM events where {fieldName}={value} LIMIT 1

by

SELECT exists(SELECT 1 FROM events where name='aname' LIMIT 1

and it still always return true...

I also tested the same query directly in psql and the response is what I except : false...

Answer 1

execute returns true if anything was returned in the result set. In this case it will be 0 or 1. It will only return false if the query was an update (returns no result set). You need to use as with a ResultSetParser to parse the results.

There's another problem with this code as well. You can't supply column names in prepared statements. {fieldName}={value}. This will get turned into a string comparison, which will probably always be false. Instead, you can use string interpolation to insert the field name into the query. Though be wary, fieldName should be be from user defined input as it is vulnerable to SQL injection. (Your users shouldn't need know about your columns anyway)

SQL(s"SELECT exists(SELECT 1 FROM events where ${fieldName} = {value} LIMIT 1)")
   .on("value" -> value)
   .as(scalar[Boolean].single)