Golang database/sql.DB.QueryRow with multiple args

Question

I want to execute a query something like this (using MySql):

select * from user where id = 5

Ex 1. This returns err = sql.ErrNoRows:

err := db.QueryRow("select * from user where ? = ?", f, v).Scan(&user.Id, etc...)

Ex 2. Since the above doesn't work, I am doing it this way, which works but doesn't feel right:

err := db.QueryRow("select * from user where "+f+" = ?", v).Scan(&user.Id, etc...)

What is wrong in Ex. 1? Is Ex 2. an acceptable way to do this?

EDIT

From the link in the comments I can do it a third way.

Ex 3:

q := fmt.Sprintf("select * from user where %s = ?", f)
err := db.QueryRow(q, v).Scan(&user.Id, etc...)

Related / possible duplicate of Golang ORDER BY issue with MySql. — icza
– icza, Commented Jul 19, 2017 at 15:31
Whether WHERE ? = ? works depends on your database driver, and what each ? represents. In the DBs I'm familiar with (Postgres, MySQL, and SQLite), you can use variable substitution only for values, not for column names. This means if f is your column name, your second try (and equivalent third) is not only "right", but the only option. — Jonathan Hall
– Jonathan Hall, Commented Jul 19, 2017 at 15:51

Pavlo Strokov · Accepted Answer · 2017-07-21 18:23:35Z

1

You need to provide column names directly in sql query. '?' sign is a placeholder for values you provide after sql string. Your Ex3. is correct, but it is not a good solution, because it is open for SQL-injection attacks.

If you need a lightweight solution you can create a map with known column names for each table like: userColumns := map[string]bool {"id": true, ...} and then just use next check:

if userColumns[f] {
    ...you able to use f in sql...
} else {
    ...provided value is not one of known columns, it is an error...
}

edited Jul 21, 2017 at 18:23

user142162

answered Jul 19, 2017 at 18:50

Pavlo Strokov

2,09716 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Golang database/sql.DB.QueryRow with multiple args

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related