Function to generate a SQL IN clause from a list of values
85 views
Skip to first unread message
Shoeb Bhinderwala
Oct 21, 2011, 8:54:41 PM10/21/11
to Clojure
Hi
I wrote the following function to create a SQL IN clause from a list
of values. Essentially the function creates a single string which is a
comma separated quoted list of the values surrounded by parenthesis.
user=> (def xs [1 2 3 4 5])
user=>(str "('" (first xs) (reduce #(str %1 "', '" %2) "" (rest xs))
"')")
"('1', '2', '3', '4', '5')"
user=> (def xs ["ab" "cd" "ef" "gh"])
user=> (str "('" (first xs) (reduce #(str %1 "', '" %2) "" (rest xs))
"')")
"('ab', 'cd', 'ef', 'gh')"
I am wondering if there is a better/easier/more elegant way to write
this function. Or if I can make use of a more suitable function from
the clojure core library to achieve this?
Thanks
Shoeb
I wrote the following function to create a SQL IN clause from a list
of values. Essentially the function creates a single string which is a
comma separated quoted list of the values surrounded by parenthesis.
user=> (def xs [1 2 3 4 5])
user=>(str "('" (first xs) (reduce #(str %1 "', '" %2) "" (rest xs))
"')")
"('1', '2', '3', '4', '5')"
user=> (def xs ["ab" "cd" "ef" "gh"])
user=> (str "('" (first xs) (reduce #(str %1 "', '" %2) "" (rest xs))
"')")
"('ab', 'cd', 'ef', 'gh')"
I am wondering if there is a better/easier/more elegant way to write
this function. Or if I can make use of a more suitable function from
the clojure core library to achieve this?
Thanks
Shoeb
Luc Prefontaine
Oct 21, 2011, 9:24:27 PM10/21/11
to clo...@googlegroups.com
user=> (str "('" (apply str (interpose "', '" [1 2 3 4 5])) "')")
"('1', '2', '3', '4', '5')"
"('1', '2', '3', '4', '5')"
Would be a way to do it. Interpose returns a lazy sequence so you need to apply str to realize the sequence.
Luc P.
--
Luc P.
================
The rabid Muppet
Alan Malloy
Oct 21, 2011, 10:23:22 PM10/21/11
to Clojure
Augh don't do this, you are begging for SQL injection attacks. I'll
set one of the elements in your list to:
'); DROP TABLE users; --
On Oct 21, 5:54 pm, Shoeb Bhinderwala <shoeb.bhinderw...@gmail.com>
wrote:
set one of the elements in your list to:
'); DROP TABLE users; --
On Oct 21, 5:54 pm, Shoeb Bhinderwala <shoeb.bhinderw...@gmail.com>
wrote:
Luc Prefontaine
Oct 21, 2011, 10:40:34 PM10/21/11
to clo...@googlegroups.com, al...@malloys.org
It all depends if you sanitize the arguments yourself before building the SQL string...
Luc
--
Shoeb Bhinderwala
Oct 21, 2011, 11:54:54 PM10/21/11
to Clojure
Thanks. It is so much cleaner with interpose.
On Oct 21, 9:24 pm, Luc Prefontaine <lprefonta...@softaddicts.ca>
wrote:
On Oct 21, 9:24 pm, Luc Prefontaine <lprefonta...@softaddicts.ca>
wrote:
Alan Malloy
Oct 22, 2011, 12:15:51 AM10/22/11
to Clojure
Sean Corfield
Oct 22, 2011, 12:22:25 AM10/22/11
to clo...@googlegroups.com
On Fri, Oct 21, 2011 at 5:54 PM, Shoeb Bhinderwala
<shoeb.bh...@gmail.com> wrote:
> I wrote the following function to create a SQL IN clause from a list
> of values. Essentially the function creates a single string which is a
> comma separated quoted list of the values surrounded by parenthesis.
<shoeb.bh...@gmail.com> wrote:
> I wrote the following function to create a SQL IN clause from a list
> of values. Essentially the function creates a single string which is a
> comma separated quoted list of the values surrounded by parenthesis.
If you're using clojure.java.jdbc, you could generate a lit of ? for
the SQL and just using the vector directly... something like this (off
the top of my head, completely untested):
(def qs (clojure.string/join "," (repeat (count xs) "?")))
(def sql (str "select * from table where id in (" qs ")"))
(clojure.java.jdbc/with-query-result conn rows
[ sql xs ]
(do-something-to rows))
Sorry, don't have a REPL open right now to test this...
--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
World Singles, LLC. -- http://worldsingles.com/
Railo Technologies, Inc. -- http://www.getrailo.com/
"Perfection is the enemy of the good."
-- Gustave Flaubert, French realist novelist (1821-1880)
Luc Prefontaine
Oct 22, 2011, 12:28:25 AM10/22/11
to clo...@googlegroups.com
Always forgetting this one :)
It performs better than the other solutions...
Alan Malloy
Oct 22, 2011, 1:47:48 AM10/22/11
to Clojure
Can't repeat this strongly enough. Do not, ever, decide you can escape/
sanitize the strings yourself so you don't need a parameterized query.
Maybe it works, but one of these days you'll slip up and get something
wrong. Just prepare a statement with the right number of ?s in it, and
then ask the SQL driver/server to fill in the blanks. They'll never
get it wrong, and it will be more efficient to boot if you can reuse a
parameterized query later.
On Oct 21, 9:22 pm, Sean Corfield <seancorfi...@gmail.com> wrote:
> On Fri, Oct 21, 2011 at 5:54 PM, Shoeb Bhinderwala
>
sanitize the strings yourself so you don't need a parameterized query.
Maybe it works, but one of these days you'll slip up and get something
wrong. Just prepare a statement with the right number of ?s in it, and
then ask the SQL driver/server to fill in the blanks. They'll never
get it wrong, and it will be more efficient to boot if you can reuse a
parameterized query later.
On Oct 21, 9:22 pm, Sean Corfield <seancorfi...@gmail.com> wrote:
> On Fri, Oct 21, 2011 at 5:54 PM, Shoeb Bhinderwala
>
> <shoeb.bhinderw...@gmail.com> wrote:
> > I wrote the following function to create a SQL IN clause from a list
> > of values. Essentially the function creates a single string which is a
> > comma separated quoted list of the values surrounded by parenthesis.
>
> If you're using clojure.java.jdbc, you could generate a lit of ? for
> the SQL and just using the vector directly... something like this (off
> the top of my head, completely untested):
>
> (def qs (clojure.string/join "," (repeat (count xs) "?")))
>
> (def sql (str "select * from table where id in (" qs ")"))
>
> (clojure.java.jdbc/with-query-result conn rows
> [ sql xs ]
> (do-something-to rows))
>
> Sorry, don't have a REPL open right now to test this...
> --
> Sean A Corfield -- (904) 302-SEAN
> An Architect's View --http://corfield.org/
> World Singles, LLC. --http://worldsingles.com/
> Railo Technologies, Inc. --http://www.getrailo.com/
> > I wrote the following function to create a SQL IN clause from a list
> > of values. Essentially the function creates a single string which is a
> > comma separated quoted list of the values surrounded by parenthesis.
>
> If you're using clojure.java.jdbc, you could generate a lit of ? for
> the SQL and just using the vector directly... something like this (off
> the top of my head, completely untested):
>
> (def qs (clojure.string/join "," (repeat (count xs) "?")))
>
> (def sql (str "select * from table where id in (" qs ")"))
>
> (clojure.java.jdbc/with-query-result conn rows
> [ sql xs ]
> (do-something-to rows))
>
> Sorry, don't have a REPL open right now to test this...
> --
> Sean A Corfield -- (904) 302-SEAN
> An Architect's View --http://corfield.org/
> World Singles, LLC. --http://worldsingles.com/
Sean Corfield
Oct 22, 2011, 3:37:30 AM10/22/11
to clo...@googlegroups.com
On Fri, Oct 21, 2011 at 10:47 PM, Alan Malloy <al...@malloys.org> wrote:
> Can't repeat this strongly enough. Do not, ever, decide you can escape/
> sanitize the strings yourself so you don't need a parameterized query.
> Maybe it works, but one of these days you'll slip up and get something
> wrong. Just prepare a statement with the right number of ?s in it, and
> then ask the SQL driver/server to fill in the blanks. They'll never
> get it wrong, and it will be more efficient to boot if you can reuse a
> parameterized query later.
> Can't repeat this strongly enough. Do not, ever, decide you can escape/
> sanitize the strings yourself so you don't need a parameterized query.
> Maybe it works, but one of these days you'll slip up and get something
> wrong. Just prepare a statement with the right number of ?s in it, and
> then ask the SQL driver/server to fill in the blanks. They'll never
> get it wrong, and it will be more efficient to boot if you can reuse a
> parameterized query later.
Which is exactly what I said, yes? (just checking we're on the same page here).
--
Sean A Corfield -- (904) 302-SEAN
An Architect's View -- http://corfield.org/
World Singles, LLC. -- http://worldsingles.com/
Railo Technologies, Inc. -- http://www.getrailo.com/
Alan Malloy
Oct 22, 2011, 3:51:57 AM10/22/11
to Clojure
Yep. Rpeating you for emphasis, not repeating myself to disagree with
you.
On Oct 22, 12:37 am, Sean Corfield <seancorfi...@gmail.com> wrote:
you.
On Oct 22, 12:37 am, Sean Corfield <seancorfi...@gmail.com> wrote:
> On Fri, Oct 21, 2011 at 10:47 PM, Alan Malloy <a...@malloys.org> wrote:
> > Can't repeat this strongly enough. Do not, ever, decide you can escape/
> > sanitize the strings yourself so you don't need a parameterized query.
> > Maybe it works, but one of these days you'll slip up and get something
> > wrong. Just prepare a statement with the right number of ?s in it, and
> > then ask the SQL driver/server to fill in the blanks. They'll never
> > get it wrong, and it will be more efficient to boot if you can reuse a
> > parameterized query later.
>
> Which is exactly what I said, yes? (just checking we're on the same page here).
> --
> Sean A Corfield -- (904) 302-SEAN
> Railo Technologies, Inc. --http://www.getrailo.com/
> > Can't repeat this strongly enough. Do not, ever, decide you can escape/
> > sanitize the strings yourself so you don't need a parameterized query.
> > Maybe it works, but one of these days you'll slip up and get something
> > wrong. Just prepare a statement with the right number of ?s in it, and
> > then ask the SQL driver/server to fill in the blanks. They'll never
> > get it wrong, and it will be more efficient to boot if you can reuse a
> > parameterized query later.
>
> Which is exactly what I said, yes? (just checking we're on the same page here).
> --
> Sean A Corfield -- (904) 302-SEAN
Shoeb Bhinderwala
Oct 22, 2011, 10:01:35 AM10/22/11
to Clojure
I agree. Thanks for general guidance on using parameterized queries. I
will switch to use prepared statements instead.
will switch to use prepared statements instead.
Reply all
Reply to author
Forward
0 new messages