Postgres 中的 Go 和 IN 子句 [英] Go and IN clause in Postgres

问题描述

我正在尝试使用 pq 驱动程序在 Go 中对 PostgreSQL 数据库执行以下查询::>

SELECT COUNT(id)FROM 标签在哪里 ID (1, 2, 3)

其中 1, 2, 3 在切片 tags := []string{"1", "2", "3"} 处传递.>

我尝试了很多不同的东西，例如:

s := "(" + strings.Join(tags, ",") + ")"如果错误 := Db.QueryRow(`选择 COUNT(id)FROM 标签WHERE id IN $1`, s,).Scan(&num);错误！= 零{log.Println(err)}

导致pq:$1"处或附近的语法错误.我也试过

if err := Db.QueryRow(`选择 COUNT(id)FROM 标签WHERE id IN ($1)`, strings.Join(stringTagIds, ","),).Scan(&num);错误！= 零{log.Println(err)}

也失败了 pq: invalid input syntax for integer: "1,2,3"

我也尝试过直接传递一个整数/字符串片段并得到 sql: Conversion Exec argument #0's type: unsupported type []string, a slice.

那么我如何在 Go 中执行这个查询?

解决方案

预构建 SQL 查询(防止 SQL 注入)

如果您要为每个值生成一个带有参数占位符的 SQL 字符串，那么立即生成最终的 SQL 会更容易.

注意，由于值是strings，存在SQL注入攻击的空间，所以我们首先测试所有string值是否确实是数字，如果我们只继续所以:

tags := []string{"1", "2", "3"}buf := bytes.NewBufferString("SELECT COUNT(id) FROM tags WHERE id IN(")对于 i, v := 范围标签 {如果我＞0 {buf.WriteString(",")}如果 _, 错误 := strconv.Atoi(v);错误！= 零{恐慌(不是数字！")}buf.WriteString(v)}buf.WriteString(")")

执行:

num := 0如果错误:= Db.QueryRow(buf.String()).Scan(&num);错误！= 零{log.Println(err)}

使用ANY

您也可以使用 Postgresql 的 ANY，其语法如下:

expression operator ANY(数组表达式)

使用它，我们的查询可能如下所示:

SELECT COUNT(id) FROM tags WHERE id = ANY('{1,2,3}'::int[])

在这种情况下，您可以将数组的文本形式声明为参数:

SELECT COUNT(id) FROM tags WHERE id = ANY($1::int[])

可以像这样简单地构建:

tags := []string{"1", "2", "3"}参数 := "{" + strings.Join(tags, ",") + "}"

请注意，在这种情况下不需要检查，因为数组表达式将不允许 SQL 注入(而是会导致查询执行错误).

所以完整代码:

tags := []string{"1", "2", "3"}q := "SELECT COUNT(id) FROM tags WHERE id = ANY($1::int[])"参数 := "{" + strings.Join(tags, ",") + "}"数量:= 0如果错误:= Db.QueryRow(q, param).Scan(&num);错误！= 零{log.Println(err)}

I am trying to execute the following query against the PostgreSQL database in Go using pq driver:

SELECT COUNT(id)
FROM tags
WHERE id IN (1, 2, 3)

where 1, 2, 3 is passed at a slice tags := []string{"1", "2", "3"}.

I have tried many different things like:

s := "(" + strings.Join(tags, ",") + ")"
if err := Db.QueryRow(`
    SELECT COUNT(id)
    FROM tags
    WHERE id IN $1`, s,
).Scan(&num); err != nil {
    log.Println(err)
}

which results in pq: syntax error at or near "$1". I also tried

if err := Db.QueryRow(`
    SELECT COUNT(id)
    FROM tags
    WHERE id IN ($1)`, strings.Join(stringTagIds, ","),
).Scan(&num); err != nil {
    log.Println(err)
}

which also fails with pq: invalid input syntax for integer: "1,2,3"

I also tried passing a slice of integers/strings directly and got sql: converting Exec argument #0's type: unsupported type []string, a slice.

So how can I execute this query in Go?

解决方案

Pre-building the SQL query (preventing SQL injection)

If you're generating an SQL string with a param placeholder for each of the values, it's easier to just generate the final SQL right away.

Note that since values are strings, there's place for SQL injection attack, so we first test if all the string values are indeed numbers, and we only proceed if so:

tags := []string{"1", "2", "3"}
buf := bytes.NewBufferString("SELECT COUNT(id) FROM tags WHERE id IN(")
for i, v := range tags {
    if i > 0 {
        buf.WriteString(",")
    }
    if _, err := strconv.Atoi(v); err != nil {
        panic("Not number!")
    }
    buf.WriteString(v)
}
buf.WriteString(")")

Executing it:

num := 0
if err := Db.QueryRow(buf.String()).Scan(&num); err != nil {
    log.Println(err)
}

Using ANY

You can also use Postgresql's ANY, whose syntax is as follows:

expression operator ANY (array expression)

Using that, our query may look like this:

SELECT COUNT(id) FROM tags WHERE id = ANY('{1,2,3}'::int[])

In this case you can declare the text form of the array as a parameter:

SELECT COUNT(id) FROM tags WHERE id = ANY($1::int[])

Which can simply be built like this:

tags := []string{"1", "2", "3"}
param := "{" + strings.Join(tags, ",") + "}"

Note that no check is required in this case as the array expression will not allow SQL injection (but rather will result in a query execution error).

So the full code:

tags := []string{"1", "2", "3"}

q := "SELECT COUNT(id) FROM tags WHERE id = ANY($1::int[])"
param := "{" + strings.Join(tags, ",") + "}"

num := 0
if err := Db.QueryRow(q, param).Scan(&num); err != nil {
    log.Println(err)
}

这篇关于Postgres 中的 Go 和 IN 子句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！