parameterizing SQL subquery in golang

Question

Please consider this question.

I need to parameterize a SQL (sub) query in golang. Please consider the pseudo-code below or at https://go.dev/play/p/F-jZGEiDnsd

The hayStack details come to me in an string slice lookIn and can vary. I need to search for %eedl% (needle) in all these haystacks.

The code in the comment is how I currently handle it - I only parameterize the needle I am looking for.

How do I parameterize the hayStacks as well?

package main

import "fmt"

func main() {
    fmt.Println("Hello, 世界")
    lookIn := []string{"hayStack.1", "hayStack.2", "hayStack.3", "hayStack.4", "hayStack.5"}
    needle := "eedl"
    needle = "%" + needle + "%"

    for i := range lookIn {
        fmt.Println("lookIn", lookIn[i])
    }
    fmt.Println("needle", needle)

    /* this is how I currently do 
        txt := `select needle,tagTitle,Epoch from ( select needle,tagTitle,Epoch where hayStackName = "hayStack.1" or hayStackName = "hayStack.2" or hayStackName = "hayStack.3" or hayStackName = "hayStack.3" or hayStackName = "hayStack.4" or hayStackName = "hayStack.5" )  where tagTitle like ? order by tagTitle COLLATE NOCASE ASC ;`
        rows, err := sqliteDbPtr.Query(txt, needle)

        if err != nil {
            if err != sql.ErrNoRows {
                checkErr(err)
                panic(err)
            }
            return
        }
        defer rows.Close()
    */

}

Any input will be much appreciated.

You cannot parameterize identifiers (table names, column names and such). You have to build the query string using Go facilities like fmt.Sprintf or so, after rigorously validating the input. — Peter
– Peter, Commented Feb 10, 2022 at 6:42

mkopriva · Accepted Answer · 2022-02-10 07:00:12Z

1

Using just the standard library you can collect the haystack arguments and the needle argument into a single slice and then pass that to the Query method.

For the SQL itself you can use the IN operator and generate its right hand side operand as a list of ? based on the number of haystacks.

args := make([]interface{}, len(lookIn)+1) // slice of all args
inRHS := "" // string of ?,... for the IN operator's right hand side
for i := range lookIn {
    args[i] = lookIn[i]
    inRHS += "?,"
}
args[len(args)-1] = needle   // add the last arg
inRHS = inRHS[:len(inRHS)-1] // remove last ","

query := `SELECT needle,tagTitle,Epoch
FROM some_table
WHERE hayStackName IN (` + inRHS + `)
AND tagTitle LIKE ?
ORDER BY tagTitle COLLATE NOCASE ASC`

rows, err := sqliteDbPtr.Query(query, args...)
if err != nil {
    // handle error
}
defer rows.Close()

for rows.Next() {
    //
}
if err := rows.Err(); err != nil {
    //
}

NOTE that I've removed the subquery and added the FROM some_table clause which your question was missing.

edited Feb 10, 2022 at 7:00

answered Feb 10, 2022 at 6:54

mkopriva

38.7k4 gold badges73 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

struggling_learner Over a year ago

Amazing! Slice of interfaces and the ellipsis did the trick. Kudos for understanding my poorly worded question, and for going above and beyond to answer! Keep up the great work! :)

Collectives™ on Stack Overflow

parameterizing SQL subquery in golang

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related