在Go中编写文本SQL查询的正确方法

Question

I can't find a good example of the right way to concat the string portion of a text query with the values. For example:

query := `SELECT column_name FROM table_name
        WHERE column1_name = %d AND column2_name = %d` % (val1, val2)
rows, res, err := db.Query(query)

This doesn't work. The compiler returns syntax error: unexpected comma, expecting ) Likely because I'm trying to use a python style tuple.

If I rewrite it as

query := `SELECT column_name FROM table_name
        WHERE column1_name = %d AND column2_name = %d` % val1

I get (mismatched types string and int) which tells me that the tuple was ONE OF the problems.

If I cast my parameters as strings first, I get (operator % not defined on string)

In python, you'd do something like

query = """SELECT column_name FROM table_name
    WHERE column1_name = %d
    AND column2_name = %d""" % (val1, val2)

OR

query = """SELECT column_name FROM table_name
    WHERE column1_name = %s
    AND column2_name = %s""" % (val1_string, val2_string)

I know I could just cast the values as strings and concat with "STRING" + var + "STRING", but that seems really messy compared to the python version. What's the equivalent of that python code in Go? Specifically including the tuple portion, and concatenating a string and an integer.

Answer 1

< standard admonishment about using string interpolation with SQL statements because of injection vulnerabilities >

You can use fmt.Sprintf to handle this.

query := fmt.Sprintf(`SELECT columnA FROM tableA WHERE columnB = %d AND columnB = %s`,
                     someNumber, someString)

To avoid injection issues, write your first code as:

query := `SELECT column_name FROM table_name
    WHERE column1_name = %d AND column2_name = %d`

rows, err := db.Query(query, val1, val2)