How can I use a parameterized query to search on a column by its name?

Question

My users should be able to search the database by entering a search term in an input box and selecting the column they'd like to search on from a dropdown box.

However, I can't find a way to parameterize a query in golang such that the column name is recognized as a column name instead of a value. Let me give an example:

rows, err := db.Query("SELECT * FROM mytable WHERE ? = ?", col, searchStr)

I turned on MySQL logging and this was the resulting query:

   41 Execute   SELECT *
    FROM mytable 
    WHERE 'Username' = 'Foo'

Clearly Username should not have single quotes around it.

How can I parameterize this query? Do I have to write application logic to check each query for the proper column names and use string manipulation to add the column name into the query (perhaps using some placeholder character to find-and-replace)?

Answer 1

This is trying to help keep you safe from bad args (prevent things like SQL injection) but isn't designed to do replacement on anything other than a value. You want it to insert a table name. Unfortunately for you the code is aware of col's type (string) and quotes it because in SQL it's nvarchar and that's how they literals are written, enclosed in single quotes. Might seem a bit like a hack but you need this instead;

db.Query(fmt.Sprintf("SELECT * FROM mytable WHERE %s = ?", col), searchStr)

Putting the table name into your query string before passing it to Query so it doesn't get treated like an argument (ie a value used in the where clause).

Answer 2

You should take a look at this package. https://github.com/gocraft/dbr

It's great for what you want to do.

import "github.com/gocraft/dbr"

// Simple data model
type Suggestion struct {
    Id        int64
    Title     string
    CreatedAt dbr.NullTime
}

var connection *dbr.Connection

func main() {

    db, _ := sql.Open("mysql","root@unix(/Applications/MAMP/tmp/mysql/mysql.sock)/dbname")
    connection = dbr.NewConnection(db, nil)
    dbrSess := connection.NewSession(nil)

    // Get a record
    var suggestion Suggestion
    err := dbrSess.Select("id, title").From("suggestions").
        Where("id = ?", 13).
        LoadStruct(&suggestion)

    if err != nil {
             fmt.Println(err.Error())
    } else {
             fmt.Println("Title:", suggestion.Title)
    }
}