Correct way to write a text SQL query in Go

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.

there is an awesome video that I would also like to reference to you, its about how-to-database in golang: https://www.youtube.com/watch?v=m879N2rzn2g — Roy Lee, Mar 20 '16 at 09:49

Adam Smith · Accepted Answer · 2016-03-20T06:03:43.517

8

< 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)

edited Mar 20 '16 at 06:03

answered Mar 20 '16 at 05:48

Adam Smith

52,157
12
73
112

1

in case you're brand new to this, SQL injection is giving a specially-crafted value to the query that terminates and runs another command, e.g. `1; DROP TABLE tableA`. Now when you're running your seemingly benign select query, it actually drops your table. – Adam Smith Mar 20 '16 at 05:51
@jniedrauer that works great! But that's the opposite of what you're doing :). Any production SQL API will have injection-proof interpolation options. Doing it within the language (even in Python as you've done above) is prone to injection. – Adam Smith Mar 20 '16 at 05:58
Taken from bobby-tables.com: `# Do NOT do it this way.` `cmd = "update people set name='%s' where id='%s'" % (name, id)` `curs.execute(cmd)` `#Instead, do this:` `cmd = "update people set name=%s where id=%s"` `curs.execute(cmd, (name, id))` But I don't see a way to do that with Go's MyMySQL. Maybe I just didn't RTFM hard enough. I cast the user input as an integer specifically to prevent injection before this snipped of code. I guess I really just need to know the right way to write a text query in MyMySQL... – Riley Laine Mar 20 '16 at 06:01
1

@jniedrauer see the docs for [`database/sql/DB.Query`](https://golang.org/pkg/database/sql/#DB.Query). You pass the arguments to the query itself, you don't interpolate the string by hand. – Adam Smith Mar 20 '16 at 06:01
@jniedrauer I added an example to my answer. Try that out – Adam Smith Mar 20 '16 at 06:13
1

Now I feel like an idiot. It works. Databases everywhere thank you for keeping them safe. – Riley Laine Mar 20 '16 at 06:26

Correct way to write a text SQL query in Go

1 Answers1

Linked