5

I am creating an application in Golang that uses Postgres using the pq driver. I want to make a function that can select a user-determined field from my database, but I get an error:

pq: could not determine data type of parameter $1

Below is the code that generated this error:

var ifc interface{}

if err := conn.QueryRow("SELECT $1 FROM "+db+" WHERE uuid=$3 OR uri=$4 LIMIT 1", field, UUIDOrURI, UUIDOrURI).Scan(&ifc); err != nil {
    if err == sql.ErrNoRows {
        return http.StatusNotFound
    }

    log.Println(err)

    return http.StatusInternalServerError
}

Why can I not insert the field that I want to SELECT using $1? Is there another way to do this?

Kurt Stolle
  • 322
  • 4
  • 18
  • Related / possible duplicate of [Golang ORDER BY issue with MySql](http://stackoverflow.com/questions/30867337/golang-order-by-issue-with-mysql). – icza Oct 05 '16 at 10:22
  • maybe you should perform a type cast as explained here: https://github.com/jackc/pgx/issues/281#issuecomment-307247685 – Victor Jun 06 '19 at 18:11

2 Answers2

2

You cannot use placeholders for field names. You'll have to build the query directly, as in:

"SELECT `" + field + "` FROM "

To avoid SQL injections, make sure that the field is part of a list of allowed fields beforehand.

laurent
  • 88,262
  • 77
  • 290
  • 428
  • I figured that is one method to do this, but that would require me to make a list of field names in the database, which is a hassle to update every time I update a field in the database. Is there not another way to do it using a placeholder instead? – Kurt Stolle Oct 05 '16 at 10:21
  • You can apply a regex and make sure only valid characters are allowed. – Kul Oct 05 '16 at 10:22
  • 1
    You can also get the list of valid fields using something like this: http://dba.stackexchange.com/a/22368/37012 Then maybe cache the result of this query so that you can refer to it as needed. – laurent Oct 05 '16 at 10:28
-3

IMHO an easier way, but not safe, to create SQL queries is to use fmt.Sprintf:

query := fmt.Sprintf("SELECT %s FROM %s WHERE uuid=%s", field, db, UUIDOrURI)
if err := conn.QueryRow(query).scan(&ifc); err != nil {

}

You can even specify an argument index:

query := fmt.Sprintf("SELECT %[2]s FROM %[1]s", db, field)

In order to ease the development, I recommend to use a package for the postgresql communication, I tried this one and worked great.

jnmoal
  • 985
  • 5
  • 7