Clojurians Log v2

Hi! what’s the correct way to tell insert-into to treat a column from the map a as a vector? (i am trying to save it as a jsonb.) ex:

{:insert-into :cal_holiday_calendar,
 :values
 [{:name "tuesdays",
   :code "_tuesdays",
   :status {:v :active},
   :weekend {:v :sat-sun},
   :workdays [2 3 4]}]}

java.lang.AssertionError: Assert failed: Alias should have two parts[2 3 4]
(= 2 (count x)) 

@jmayaalv What JDBC library are you using with HoneySQL?

next.jdbc

next.jdbc has a bunch of functions in next.jdbc.types for wrapping values to "hint" how they should be stored into the JDBC parameters.

(make sure you're using a recent version of next.jdbc!)

https://cljdoc.org/d/seancorfield/next.jdbc/1.1.613/api/next.jdbc.types#as-array might help.

So :workdays (as-array [2 3 4]) might work. HoneySQL should treat that as an opaque value and next.jdbc should use the type hint to set the parameter.

perfect! thank you!

LMK if it works.

tested with as-array and got

Cannot cast an instance of clojure.lang.PersistentVector to type
  Types.ARRAY

Hmm, OK. So have a read of the next.jdbc docs that talk about using arrays in PostgreSQL (Tips & Tricks).

I assume you're using PG?

yes, i am using Postgres. If understand correctly this would save the object as Postgres’ Array but i can’t really change the type of the column right now need to store it as json (jsonb).

and can’t extend SettableParameter as java arrays don’t have a type 😕

I'd expect this to work

(extend-protocol SettableParameter
  (Class/forName "[Lfoo.bar.MyClass;")
  (set-parameter [a b c] ,,,))

scary but worth trying 🙂 thanks. I’ll confirm if it works in a while 🙂

@seancorfield will honeysql be safe against sql injection when we call sql/format with {:inline true} ?

The array is a Clojure vector, not a Java array.

@viebel In general that's never going to be safe. When I work on the documentation, I'll make that clear. The code can be updated to escape ' so that a string of "it's" will correctly convert to a legal SQL string (I don't think I've gotten that part done yet), but it can only do so much escaping. You should only use :inline when you have a very specific need to do that and you can trust the incoming data.

Could you give me an example of a malicious honeysql map ?

I thought this might be (in HoneySQL v2) but it seems that I do escape ' already:

user=> (-> (select :*) (from :table) (where [:= :id "foo'; drop table user"]) (h/format {:inline true}))
["SELECT * FROM table WHERE id = 'foo''; drop table user'"]

With HoneySQL v1, that is dangerous:

user=> (-> (select :*) (from :table) (where [:= :id "'foo'; drop table user"]) (h/format :parameterizer :none))
["SELECT * FROM table WHERE id = 'foo'; drop table user"]

Note: the string is slightly different in those two examples. Here's v2 presented with that string:

user=> (-> (select :*) (from :table) (where [:= :id "'foo'; drop table user"]) (h/format {:inline true}))
["SELECT * FROM table WHERE id = '''foo''; drop table user'"]

(not valid SQL -- I'm not sure if someone could construct a string parameter that would produce valid SQL and also be an injection attack but I wouldn't bet on it either way)

Yeah. I tried the same. But in V2 I was not able to unescape the string quotes.

For the sake of curiosity: Could it be proved that V2 is safe?

I'll leave that as an "exercise for the reader" 🙂

(I would be extremely wary of using :inline true except in very restrictive situations -- I mean, why would you want to inline the parameters in the first place?)

Hi, I need to call a Postgres stored procedure that expects a text[] parameter, but can't get it to work. Maybe you can enlighten me? If I try the following, here's what I get:

(def param-names ["HOST_MNO"])
  (def session-auth-id "1234")
  (def offer-id 88060)
  (def addon-name "MASTER_MNO")
  (println (-> (honeysql.helpers/select :*)
               (honeysql.helpers/from (honeysql.core/call :meta_db.get_offer_param_value_list :?session-auth-id :?offer-id :?addon-name :?param-name-array))
               (honeysql.core/format :params {:session-auth-id session-auth-id :offer-id offer-id :addon-name addon-name :param-name-array (honeysql.types/array param-names)})))

["SELECT * FROM meta_db.get_offer_param_value_list(?, ?, ?, ?)" "1234" 88060 "MASTER_MNO" #sql/array (["HOST_MNO"])]
nil

["SELECT * FROM meta_db.get_offer_param_value_list(?, ?, ?, ARRAY[?])" "1234" 88060 "MASTER_MNO" "HOST_MNO"]