FAQ
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+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/groups/opt_out.

Search Discussions

  • Tad Glines at May 19, 2013 at 1:11 am
    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, 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+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.

    --
    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+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Dougx at May 19, 2013 at 4:07 am
    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.
    On Sunday, May 19, 2013 2:54:06 AM UTC+8, 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+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Julien Schmidt at May 19, 2013 at 2:13 pm
    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
    On Sunday, May 19, 2013 6:06:42 AM UTC+2, Dougx wrote:

    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.
    On Sunday, May 19, 2013 2:54:06 AM UTC+8, 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+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Jonathan Novak at May 19, 2013 at 4:25 pm
    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

    On Sun, May 19, 2013 at 7:13 AM, Julien Schmidt wrote:

    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
    On Sunday, May 19, 2013 6:06:42 AM UTC+2, Dougx wrote:

    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/<https://downloads.mariadb.org/client-native/>

    ~
    Doug.
    On Sunday, May 19, 2013 2:54:06 AM UTC+8, 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+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.
  • Tad Glines at May 19, 2013 at 4:32 pm
    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.


    On Sun, May 19, 2013 at 8:51 AM, Jonathan Novak wrote:

    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

    On Sun, May 19, 2013 at 7:13 AM, Julien Schmidt wrote:

    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
    On Sunday, May 19, 2013 6:06:42 AM UTC+2, Dougx wrote:

    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/<https://downloads.mariadb.org/client-native/>

    ~
    Doug.
    On Sunday, May 19, 2013 2:54:06 AM UTC+8, 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+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.

    --
    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+unsubscribe@googlegroups.com.
    For more options, visit https://groups.google.com/groups/opt_out.

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupgolang-nuts @
categoriesgo
postedMay 19, '13 at 12:58a
activeMay 19, '13 at 4:32p
posts6
users4
websitegolang.org

People

Translate

site design / logo © 2022 Grokbase