6

How do you use a dynamic table name for a prepared INSERT statement for the pq postgres driver? At the moment I've got a test table with id SERIAL and values TEXT columns, and this statement is failing:

stmt, err := db.Prepare("INSERT INTO $1(values) VALUES($2);")
if err != nil {
    log.Fatal(err)
}

That's failing with:

pq: syntax error at or near "$1"

If I can only use placeholders for values and not table names, is there a way around using Sprintf here? The table name contains a string from user input and although I can sanitize it it will slow down the insert a bit compared to letting Postgres return an error on an prepared statement.

Escher
  • 5,418
  • 12
  • 54
  • 101
  • 2
    Taking a table name from user input, even if you deny SQL injection by using prepared statements, is still extraordinarily dangerous - they could hit any table. If you must do this, do it using `Sprintf`, but make sure to sanitize the table name down to only those characters allowed in table names per the database's documentation. – Adrian Mar 29 '18 at 15:19
  • @Adrian Noted. The table names are actually composed from a strong hash function, pre-calculated and stored in a `map`. – Escher Mar 29 '18 at 15:49
  • @AndreaM16 the question was specifically "is there a way around using Sprintf here". – Adrian Mar 29 '18 at 15:50
  • 1
    You can't use placeholders for identifiers (such as table and column names). Identifiers exist at a different level than values (just like variable *names* in Go exist at a different level from values like `6`). Whitelist them and quote them (manually since there is AFAIK no "quote identifier" function in the database interface) and use `Sprintf`. – mu is too short Mar 29 '18 at 17:37

1 Answers1

4

To substitute table name variables, go sql package doesn't yet provide a standard interface (in progress).

You might be able to use a database driver specific quote function, eg: QuoteIdentifier.

See also postgres parameter quoting examples.

Mark
  • 6,731
  • 1
  • 40
  • 38