sql string not interpolated as expected

[Alexander Mills]

I have this:

rows, err := c.Database.Db.Query(`

select *, (
select count(*) from mbk_file_label
where file_id = mbk_file.id and label_id IN (
select id
from mbk_user_label
where label_name IN (
'carnivore', 'mammal', 'vertebrate'
)
)
) as xxx
from mbk_file
where user_id = $1
order by xxx DESC
`,
loggedInUserId,
//labelStr,
)

the above works, but if I used labelString instead of the hardcoded version:

labelStr = "'carnivore', 'mammal', 'vertebrate'"

rows, err := c.Database.Db.Query(`

select *, (
select count(*) from mbk_file_label 
where file_id = mbk_file.id and label_id IN (
select id 
from mbk_user_label
where label_name IN (

        $2
)
)
) as xxx
from mbk_file
where user_id = $1
order by xxx DESC
`,
loggedInUserId,
labelStr,
)

then the query doesnt work and I dont know why?

[Reto]

On Sun, Jan 03, 2021 at 12:53:03AM -0800, Alexander Mills wrote:
> rows, err := c.Database.Db.Query(`
>
> select *, (
> select count(*) from mbk_file_label
> where file_id = mbk_file.id and label_id IN (
> select id
> from mbk_user_label
> where label_name IN (
> $2
> )
> )
> ) as xxx
> from mbk_file
> where user_id = $1
> order by xxx DESC
> `,
> loggedInUserId,
> labelStr,
> )
>
>
> then the query doesnt work and I dont know why?

You might want to debug log your statements in the database engine...
What you want to do is not what it's doing.

You ask the sql engine to escape the input you give it.

So your question becomes `where label_name in ('"carnivore", "mammal", "vertebrate"')`
Meaning the string exactly as given as single element.

Maybe that helps: https://stackoverflow.com/a/38878826/6212932 if you use postgres.

Cheers,
Reto

[Brian Candler]

I think the nearest is:

        labelStrs := []interface{}{"carnivore", "mammal", "vertebrate"}

        rows, err := c.Database.Db.Query(`

select id from mbk_user_label where label_name in (?,?,?)

`, labelStrs...)

Of course, you may need to change the number of question-marks to match len(labelStrs), but that's easily done with a helper function.  It would be nice if a placeholder could be a list and expand accordingly, though.

[Alexander Mills]

labels are variable arguments, so I don't know how to do it..i solved it for the time being using `fmt.Sprintf` but that leaves me vulnerable to sql injection I suppose.

--
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/PdzePaSYlUc/unsubscribe.
To unsubscribe from this group and all its topics, send an email to golang-nuts...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/67dabe1f-e99a-43c4-a686-528227b38f28n%40googlegroups.com.

--

Alexander D. Mills
New cell phone # (415)730-1805
linkedin.com/in/alexanderdmills

[Alexander Mills]

here is a question I posted on dba.stackexchange:

https://dba.stackexchange.com/questions/282501/variable-number-of-arguments-with-golang

[Marcin Romaszewicz]

Don't use fmt.Sprintf for the actual values, generate the positional arguments yourself.

Something like:

 

q := "SELECT x FROM t WHERE y IN (%s)"

labelName := []string{'carnivore', 'mammal', 'vertebrate'}

var arrayArgs []string

for i := range labelName {

  arrayArgs = append(arrayArgs, fmt.Sprintf("$%d", i+1))

}

db.Exec(fmt.Sprintf(q, strings.Join(",", names)), arrayArgs...)

This will escape each element of the array - dynamically allocate

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.
To view this discussion on the web visit https://groups.google.com/d/msgid/golang-nuts/CA%2BKyZp6GO08_JY7jNuKuQNN-GQV%3DL%2B910Cq1jtu57NF%2BV%3DF-BQ%40mail.gmail.com.