yesql - named parameter for column name

Question

I have a simple update statement:

-- name: add-response!
UPDATE survey
  SET :question = :response
  WHERE caseid = :caseid

And I invoke it like this:

(add-response! db-spec "q1" 2 1001)

However, yesql doesn't like using a string as a parameter for the column - it translates "q1" to 'q1', which isn't valid postgres syntax.

"BatchUpdateException Batch entry 0 UPDATE survey SET 'q1' = 2
WHERE caseid = 1001 was aborted."

Is there a way to make this work? I've tried using the question name as a symbol: 'q1. That doesn't work because:

"PSQLException Can't infer the SQL type to use for an instance of clojure.lang.Symbol."

score 4 · Accepted Answer · edited May 23 '17 at 11:52

4

I've had same problem some time ago with yesql, so I investigated its source code. I turns out that yesql converts query like

UPDATE survey SET :question = :response WHERE caseid = :caseid

to

["UPDATE survey SET ? = ? WHERE caseid = ?" question response caseid]

and feeds it to clojure.java.jdbc/query. So this is just a prepared statement. According to this StackOverflow question there is no way to pass column names as parameters to DB query. That actually makes sence, because one of purposes of prepared statements is to force values to be always treated as values and thus protect you from SQL injections or similar issues.

I your case, you could use clojure.java.jdbc/update! as it clearly allows parameterized colum names:

(:require [clojure.java.jdbc :as j])
(j/update! db-spec :survey
           {"q1" 2}
           ["caseid = ?" 1001])

Hope that helps. Cheers!

edited May 23 '17 at 11:52

Community

1
1

answered Mar 23 '15 at 08:43

lobanovadik

1,018
8
14

Thanks for digging that out. It looks like this also applies to select statements - column names can't be passed as parameters. I was able to get around it by using select * and then just parsing the returned map, but all these workarounds have me second-guessing - is it just a bad idea to pass parameters like this? This isn't user facing code, and I was hoping to abstract so I wouldn't have to write a query for each individual question. – Brady Mar 23 '15 at 23:52
This limitation for parameter placeholders come from the implementation of prepared statements at the database level. E.g. look at how `PreparedStatement` is implemented in PostgreSQL http://www.postgresql.org/docs/9.2/static/sql-prepare.html – hsestupin Mar 24 '15 at 10:50
@Brady as @hsestupin said, prepared statements exist on DB level. This also can be thought of as a optimisation - you explicitly tell PostgreSQL that you will later execute this query, so it can "prepare for it": parse it once and then just use parameters you provide. So it seems like you have 2 options: use `jdbc/update` or generate SQL on-the-fly. – lobanovadik Mar 24 '15 at 13:10

yesql - named parameter for column name

1 Answers1