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: youtube.com/watch?v=m879N2rzn2g — Roy Lee
– Roy Lee, Commented Mar 20, 2016 at 9:49

Adam Smith · Accepted Answer · 2016-03-20 06:03:43Z

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, 2016 at 6:03

answered Mar 20, 2016 at 5:48

Adam Smith

54.6k13 gold badges84 silver badges120 bronze badges

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

6 Comments

Adam Smith Over a year ago

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 Over a year ago

@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.

Riley Laine Over a year ago

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

Adam Smith Over a year ago

@jniedrauer see the docs for database/sql/DB.Query. You pass the arguments to the query itself, you don't interpolate the string by hand.

Riley Laine Over a year ago

Now I feel like an idiot. It works. Databases everywhere thank you for keeping them safe.

|

Collectives™ on Stack Overflow

Correct way to write a text SQL query in Go

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related