0

I am using the pgx library to populate a Postgres database in Go.

Following e.g. the tutorial here and this question, I construct my query like so:

// this works
tblinsert = `INSERT into tablename (id, body) VALUES ($1, $2) RETURNING id`
var id string
err := Connector.QueryRow(context.Background(), tblinsert, "value1", "value2").Scan(&id)

  • Question: I would like to supply the tablename as a variable to the query as well, e.g. change tblinsert to INSERT into $1 (id, body) VALUES ($2, $3)

  • Issue: the above code errors out and returns a "syntax error at or near "$1" (SQLSTATE 42601)" when I run:

      //this errors out
  err := Connector.QueryRow(context.Background(), tblinsert, "tablename", "value1", "value2").Scan(&id)`.

I do not fully understand why the error message even references the $ placeholder - I expected the query to do the string substitution here, just like for the VALUES.

I found similar questions in pure SQL here and here, so not sure if this is even possible. .

Any pointers on where I am going wrong, or where I can learn more about the $x syntax (I got the above to work using Sprintf, but this appears discouraged) are much appreciated - I am pretty new to both SQL and Go.

Jonathan Hall
  • 75,165
  • 16
  • 143
  • 189
patrick
  • 4,455
  • 6
  • 44
  • 61
  • 6
    It's not string substitution, it's a prepared statement, which allows passing SQL values, not query syntax. If you want to do this, you'll need to use string substitution, but I'd urge *extreme* caution in doing so, as this has the potential to create significant security vulnerabilities. – Adrian Dec 16 '20 at 17:01
  • Thank you @Adrian makes sense - so there is no way to do this using the `$` sign, I should do a string substitution for the table name, and use `$` for the others? (I understand the injection implications, but this is purely for my own personal use). – patrick Dec 16 '20 at 17:03
  • 2
    This has nothing to do with Go. It's a matter of what PostgreSQL supports, and PostgreSQL does not support this. To my knowledge, no database does, but there may be some odd exception. – Jonathan Hall Dec 16 '20 at 17:09
  • 4
    The use of `fmt.Sprintf` is usually discouraged to construct SQL queries because of [this](https://xkcd.com/327/), bu you appear to control the set of table names to be used, so I see no problem constructing a string this way. Just make sure you never substitute any user input in a template this way—well, unless you're definitely sure it's properly quoted. – kostix Dec 16 '20 at 17:29

0 Answers0