Re: [go-nuts] database/sql: constructing IN queries

[Tad Glines]

That isn't supported. Fundamentally it has to do with the way the server parses queries and does argument substitution.

I'm not aware of any database that supports parameterized "IN" clauses like you (and many other people desire).

There are generally two options. If you want to avoid SQL injection attacks and don't want to bother sanitizing inputs, then autogenerate the SQL statement with a dynamic number of parameters. Or if you have done your own input sanitization, then concatenate the arguments into the statement.

The former is easier to secure, the later is more likely to produce optimized query plans.

On Sat, May 18, 2013 at 11:54 AM, <jno...@gmail.com> wrote:

I'd like to run a query like this using the "database/sql" package:

SELECT id, name FROM users WHERE id IN (1,2,3,4);

How do I use variable substitution in the query command? E.g., I want to do this (which of course doesn't work):

db.Query("SELECT id, name FROM users WHERE id IN (?)", []int{1,2,3,4})


Thanks

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

[Dougx]

The driver doesn't support it (surprise), but mysql/mariadb at least supports the syntax:

MariaDB [Test]> SET @s = 'select * from Core where id in (?)';
Query OK, 0 rows affected (0.00 sec)

MariaDB [Test]> SET @set = '1, 2, 3';
Query OK, 0 rows affected (0.00 sec)

MariaDB [Test]> PREPARE stmt3 FROM @s;
Query OK, 0 rows affected (0.00 sec)
Statement prepared

MariaDB [Test]> EXECUTE stmt3 USING @set;
+----+------+
| id | Name |
+----+------+
|  1 | Doug |
+----+------+
1 row in set (0.00 sec)

Use cgo and a mysql database driver, I suggest:
https://downloads.mariadb.org/client-native/

~
Doug.

[Julien Schmidt]

This doesn't work. It only returns the first row, so the IDs "2" and "3" are ignored in your example.

I think MySQL doesn't allow this to prevent injections. The only way I can think of how to do this is http://play.golang.org/p/dGigVxIYu6

[Jonathan Novak]

Appreciate the help guys.

I'm basically constructing a query manually has has all my IDs baked in (taking care of sanitization myself), which solves my short-term problem. I believe Julien's method would also work but I haven't verified.

One thing I've noticed is that this all uses prepared statements. For this situation, where I have a variable number of elements in the IN clause, it seems like prepared statements aren't the way to go. Am I correct that database/sql doesn't let you issue non-prepared statements? For these dynamic queries, wouldn't his hurt performance?

Thanks

[Tad Glines]

If the driver.Conn implements driver.Execer or driver.Queryer then database/sql will use those interfaces to execute a statement directly.

If you obtain a sql.Stmt first then call stmt.Query or stmt.Exec, then a prepared statement will always be used. But if you use db.Query/db.Exec or db.Begin() plus tx.Query/tx.Exec then a prepared statement will only be used if the underlying driver doesn't implement driver.Execer/driver.Queryer.