How to prepare a sql query with variable 'IN' parameters?

13,496 views
Skip to first unread message

Googol Lee

unread,
Feb 12, 2014, 3:40:52 AM2/12/14
to golan...@googlegroups.com
Hi,

I want to query rows in table t with some ids, and the length of ids is changing while running.

I tried some code like this:
http://play.golang.org/p/z0b9nlqmdt

But got error:
[] sql: converting Exec argument #0's type: unsupported type []int, a slice

 How to write sql query with variable 'IN' with sql.Stmt?

Didier Spezia

unread,
Feb 12, 2014, 6:05:46 AM2/12/14
to golan...@googlegroups.com

>> How to write sql query with variable 'IN' with sql.Stmt?

I would be surprised if you could.
None of the database server I know supports this feature.

Regards,
Didier.

Gyepi SAM

unread,
Feb 12, 2014, 6:24:43 AM2/12/14
to golan...@googlegroups.com
You have to expand the args into the simple types that sql.Driver.Value
supports.

http://play.golang.org/p/RK_5-JQI0j

As I recall, Prepared queries are not cached so there's no extra cost
to generating a new query string each time.

-Gyepi

Matt Silverlock

unread,
Feb 12, 2014, 8:46:34 AM2/12/14
to golan...@googlegroups.com
String replacement is never a good idea and opens you up to injection attacks.

André Paquet

unread,
Feb 12, 2014, 9:01:10 AM2/12/14
to golan...@googlegroups.com
I do something like this: 

Gyepi SAM

unread,
Feb 12, 2014, 12:57:50 PM2/12/14
to golan...@googlegroups.com
On Wed, Feb 12, 2014 at 05:46:34AM -0800, Matt Silverlock wrote:
> String replacement is never a good idea and opens you up to injection attacks.

Not sure if you are referring to my solution, but there is no string
replacement with user input in there. That would be silly.

-Gyepi

Googol Lee

unread,
Feb 13, 2014, 12:30:39 AM2/13/14
to golan...@googlegroups.com, self-...@gyepi.com
I thought some db may query faster if it has prepared. I will do some test.

Thanks your code.

Daniel Theophanes

unread,
Feb 13, 2014, 1:54:00 PM2/13/14
to golan...@googlegroups.com
How to build SQL Queries and avoid injection attacks 100% of the time. Always. Forever. Easy.

Caleb Doxsey

unread,
Feb 13, 2014, 5:13:28 PM2/13/14
to Daniel Theophanes, golang-nuts


--
You received this message because you are subscribed to the Google Groups "golang-nuts" group.
To unsubscribe from this group and stop receiving emails from it, send an email to golang-nuts...@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Matt Silverlock

unread,
Feb 13, 2014, 5:24:23 PM2/13/14
to golan...@googlegroups.com, self-...@gyepi.com
You should always (and only) use parameterised queries. You have to assume that even in cases like that that the list of ids may come from a URL (and therefore, user input), API or some other such source that might be manipulated. 

Caleb Doxsey

unread,
Feb 13, 2014, 5:35:53 PM2/13/14
to Matt Silverlock, golang-nuts, self-...@gyepi.com
His example uses a parameterised query. It becomes:

"SELECT * FROM t WHERE id IN (?, ?, ?)", 1, 2, 3


--

Naoki INADA

unread,
Feb 13, 2014, 6:30:42 PM2/13/14
to golan...@googlegroups.com, Daniel Theophanes, ca...@doxsey.net
You can use sql_mode=NO_BACKSLASH_ESCAPES to avoid it.

2014年2月14日金曜日 7時13分28秒 UTC+9 Caleb Doxsey:

Dave Cheney

unread,
Feb 13, 2014, 6:44:02 PM2/13/14
to Caleb Doxsey, Matt Silverlock, golang-nuts, self-...@gyepi.com
This is the only safe alternative. If you're preparing SQL with string concatenation, please return your white hat to the place you got it.

Gyepi SAM

unread,
Feb 14, 2014, 1:02:08 AM2/14/14
to golan...@googlegroups.com
Your method works fine for single quotes but is generally a bad idea because
different SQL systems support quote mechanisms. In addition to single quotes,
I've seen double quotes and square brackets used. To worsen matters, the quote
handling sometimes depends on compatibility mode settings and can change.

The only sure way to quote is to use query parameters and let the database driver,
which knows the most about that particular system's quirks and settings do the job.

I respond out of concern that those who most need to understand these nuances will instead
be convinced by the certainty of presentation.

-Gyepi

Gyepi SAM

unread,
Feb 14, 2014, 1:24:10 AM2/14/14
to golan...@googlegroups.com
Yes, of course. But I am not sure why you are telling me this. My example does
exactly that. Like I said, it would be silly to do otherwise.

-Gyepi

Andy Balholm

unread,
Feb 14, 2014, 11:33:49 AM2/14/14
to golan...@googlegroups.com, self-...@gyepi.com
With PostgreSQL, at least, you have the option of passing the entire array as a string, using a single placeholder:

db.Query("select 1 = any($1::integer[])", "{1,2,3}")

That way, you can use a single query string, and all the string concatenation is confined to the parameter. And if the parameter is malformed, you don't get an SQL injection; you just get something like: ERROR:  invalid input syntax for integer: "xyz"

I don't know if MySQL can do something like this or not. I doubt the syntax is portable, but there may be some equivalent.

Svip

unread,
Feb 14, 2014, 11:48:34 AM2/14/14
to Googol Lee, golan...@googlegroups.com
I once ran into this issue as well. But in addition, I also had
non-in parameters as well for the same query.

In truth, you need to concat the query together (with ? for each
field, obviously) and then prepare it.

var params []interface{}
sql := "SELECT * FROM table WHERE a = ? AND b IN (%s)"
params = append(params, firstParam)
var sqlIn string
for p := range bParams {
params = append(params, p)
if sqlIn != "" {
sqlIn += ", "
}
sqlIn += "?"
}
sql = fmt.Sprintf(sql, sqlIn)
db.Query(sql, params...)

Svip

unread,
Feb 14, 2014, 11:56:45 AM2/14/14
to Googol Lee, golan...@googlegroups.com

Gyepi SAM

unread,
Feb 14, 2014, 12:12:46 PM2/14/14
to golan...@googlegroups.com
On Fri, Feb 14, 2014 at 08:33:49AM -0800, Andy Balholm wrote:
> With PostgreSQL, at least, you have the option of passing the entire array
> as a string, using a single placeholder:
>
> db.Query("select 1 = any($1::integer[])", "{1,2,3}")
>
> That way, you can use a single query string, and all the string
> concatenation is confined to the parameter. And if the parameter is
> malformed, you don't get an SQL injection; you just get something like:
> ERROR: invalid input syntax for integer: "xyz"

Right. Note that from the client point of view, we are just passing a string
mapped to a parameter, so any invalid or malicious input is still correctly
quoted by the driver. So even if the input strings came in as

[]string{"1", "2", "3;--drop table xxx"}

and you strings.Join() manually, you're still safe.
However, the whole thing looks rather inelegant to me.

The array coercion occurs much later, relatively speaking.

> I don't know if MySQL can do something like this or not. I doubt the syntax
> is portable, but there may be some equivalent.

The syntax is definitely not portable, but is occasionally useful.
MySQL does not support arrays. I have seen people stuff json arrays and
other delimited data in text fields, but those are all blobs as far the
database is concerned unless you write extensions to grok it.

-Gyepi

Daniel Theophanes

unread,
Feb 14, 2014, 12:42:07 PM2/14/14
to golang-nuts
If you want to use a database for more then just CRUD, no SQL is
portable. ... Of all the databases available, MySQL is probably the
least powerful, and due to that \', maybe the most dangerous.

How databases vendors currently act, is kinda like saying go fmt is
portable with c fmt. It largely is and is in the same spirit, but
you'd still have to port the fmt strings. What may be a security issue
in one Db or language may not be a security issue in another Db or
language. Know your specific tool.
> --
> You received this message because you are subscribed to a topic in the Google Groups "golang-nuts" group.
> To unsubscribe from this topic, visit https://groups.google.com/d/topic/golang-nuts/vHbg09g7s2I/unsubscribe.
> To unsubscribe from this group and all its topics, send an email to golang-nuts...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages