Consider using HoneySQL

[jamesaoverton]

Let's assess using https://github.com/seancorfield/honeysql to work with SQL in Clojure, only rendering to a SQL string just before execution.

I think this will work, but we specifically need to see if it supports WITH.

[jamesaoverton]

Please also check whether HoneySQL mitigates SQL injection.

[lmcmicu]

Yes HoneySQL supports the with statement: https://cljdoc.org/d/com.github.seancorfield/honeysql/2.1.818/doc/getting-started/sql-clause-reference#with-with-recursive

I've tried it and it works with the toy example. E.g.,

(->> {:with [[[:mybreads {:columns [:bread]}]
                {:select [:*] :from [:breads]}]]
        :select [:*]
        :from [:mybreads]}
       (sql/format)
       (jdbc/execute! conn)

There is also a helper function: https://cljdoc.org/d/com.github.seancorfield/honeysql/2.1.818/api/honey.sql.helpers#with which I guess is easier to use, although there does not seem to be any documentation for it.

[lmcmicu]

@jamesaoverton I am not sure what you mean by "Please also check whether HoneySQL mitigates SQL injection." Did you have something in mind in particular?

In any case there is no section dedicated to the topic in the documentation for HoneySQL as far as I can tell. However there have been some changes and bug fixes between version 1 and version 2 of the library to do with potential sql-injection-related security risks. See: https://cljdoc.org/d/seancorfield/honeysql/2.0.0-rc2/doc/changes, seancorfield/honeysql#316, seancorfield/honeysql#299 (see this comment in particular: seancorfield/honeysql#299 (comment)).

[jamesaoverton]

Thanks! I think seancorfield/honeysql#299 (comment) successfully addresses my worry.