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

[Googol Lee]

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]

>> 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]

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]

String replacement is never a good idea and opens you up to injection attacks.

[André Paquet]

I do something like this: 

http://play.golang.org/p/fKNGf04Nug

[Gyepi SAM]

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]

I thought some db may query faster if it has prepared. I will do some test.

Thanks your code.

[Daniel Theophanes]

How to build SQL Queries and avoid injection attacks 100% of the time. Always. Forever. Easy.

http://play.golang.org/p/B41o22Lg6l

[Caleb Doxsey]

That won't work. In MySQL a simple \' would break it.

http://stackoverflow.com/questions/139199/can-i-protect-against-sql-injection-by-escaping-single-quote-and-surrounding-use

--
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]

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]

His example uses a parameterised query. It becomes:

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

--

[Naoki INADA]

You can use sql_mode=NO_BACKSLASH_ESCAPES to avoid it.

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

[Dave Cheney]

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]

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]

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]

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]

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]

I also asked this question on StackOverflow:
https://stackoverflow.com/questions/20652901/how-do-i-pass-a-slice-to-an-in-condition-in-a-prepared-sql-statement-with-non

[Gyepi SAM]

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]

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.