0

I have the simple query in Go:

import "database/sql"

Db, err := sql.Open(...)
p1 := "string param"
Db.QueryRow("select * from some_func(?)", p1)

The some_func accepts string param. In terms of PGsql it is a single-quoted text: 'some val'.

To achieve passing single quoted param I did the following: Db.QueryRow("select * from some_func('?')", p1). I’ve wrapped ? with single quotes. But I’m getting error: 1 parameter is passed when expecting 0

Varp
  • 46
  • 5
  • Those quotes you provided are not valid Go syntax, please provide code that's valid. https://play.golang.org/p/StkVcAWayPu – mkopriva Feb 05 '18 at 19:27
  • Not sure what db driver you're using but a placeholder in postgres looks like this: `$1` for first param, `$2` for second, and so on. `?` placeholders are mysql syntax. Using `?` with the `lib/pq`, if that's what you're using, driver is not gonna work. – mkopriva Feb 05 '18 at 19:30
  • As a workaround I can achieve that by using `fmt..Sprintf`, but it’s not SQL injection safe, because I got param from for the `some_func` from user. If I would have separate sql escaping function in addition to raw `fmt.Sprintf` then may be I could be statisfied by such workaround – Varp Feb 05 '18 at 19:31
  • 1
    Finally, most db drivers evaluate your parameters and substitute the placeholders according the their type automatically, meaning that you don't have to worry about putting quotes around it yourself, that's the driver's job. – mkopriva Feb 05 '18 at 19:31
  • 1
    Given your update, and assuming you're using `lib/pq`, you should do this `Db.QueryRow("select * from some_func($1)", p1)`. – mkopriva Feb 05 '18 at 19:36
  • @mkopriva I've also tried with $1 placeholders. It's also not work, I think the db driver not properly guess params for `some_func` – Varp Feb 05 '18 at 19:37
  • 1
    Please provide the error you get when you use `$1`. – mkopriva Feb 05 '18 at 19:38
  • Please also provide what driver you are using. – mkopriva Feb 05 '18 at 19:40
  • @mkopriva you were right about `$1` PostgresSQL placeholders. I carefully refactored the code and the problem is gone. Now params is properly quoted. I was getting error when just replacing `?` with `$`. THX!!! – Varp Feb 05 '18 at 19:51
  • 1
    Different error message than the duplicate but the same underlying problem: PostgreSQL uses numbered placeholders natively, not positional question marks. – mu is too short Feb 05 '18 at 19:53
  • `res, err := DB.Exec("COPY spark_phones (inn, phone) FROM $1", csvName)` @mkopriva what about that query?? I'm getting error, `syntax error at $1` – Varp Feb 08 '18 at 08:38
  • 1
    @Varp try `DB.Exec(\`EXECUTE "COPY spark_phones (inn, phone) FROM " || $1\`, csvName)` use backtics for the execute string so that you don't have to escape the inner quotes. – mkopriva Feb 08 '18 at 08:49

0 Answers0