Secure insert queries with go-sql-driver and mysql

Question

I have reviewed the go-sql-driver examples with db.Prepare but I couldn't get it to work properly so I modified it to work directly with db.Query. My understanding is that by using ?? the value is escaped anyway so I was wondering if the following is correct and secure against SQL injection (note that I am using MySQL)

stmtIns, err := db.Query("INSERT INTO users (name, address) VALUES(?,?)", name, address) // ? = placeholder
        if err != nil {
            panic(err.Error()) // proper error handling instead of panic in your app
        }
        defer stmtIns.Close() // Close the statement when we leave main() / the program terminates

Also, what is the best way to detect if the row has been inserted?

clarification: for secure I meant from SQL Injection. I think it is but then I wonder why all example I could find use db.Prepare and not db.Query as I do.

Define "secure". Your code is not vulnerable to SQL injection, but is that what you're asking about? — slim
– slim, Commented Oct 2, 2019 at 8:09
yes I meant secure from SQL injection. I also want to understand if it is (which I think so) why all examples I could find on go-sql-driver have db.Prepare and not db-Query — devnull
– devnull, Commented Oct 2, 2019 at 8:41

slim · Accepted Answer · 2019-10-02 09:19:49Z

2

Yes, this safe from SQL injection attacks.

Note that the client code does not "escape" parameters in a query of this kind. It does something much simpler and more secure:

The client does not combine INSERT INTO users (name) VALUES(?) and the parameter ;DROP TABLE USERS; into INSERT INTO users (name) VALUES(;DROP TABLE USERS;.

Nor does the client do the above with escape characters to make it "safe". The client does not construct something like INSERT INTO users (name) VALUES("\;DROP TABLE USERS\;")

What it does is send a query to MySQL that contains, separately:

the query string INSERT INTO users (name) VALUES(?) -- MySQL knows this is a query string and knows that the ? is where parameters are plugged in.
the parameter ;DROP TABLE USERS; -- MySQL knows this is a parameter to the query

Hence the server knows that the parameter is not SQL code, and that neatly avoids the whole nasty problem of escaping characters.

By choosing not to use Prepare you are skipping some potential for optimisation in the server. The server compiles each statement into an internal representation, and this takes time. By making this explicit and re-using the prepared statement, you explicitly save repeating that compilation step.

However, MySQL does cache compiled statements, to avoid repeatedly compiling exactly the same string -- so you probably won't see big performance issues.

edited Oct 2, 2019 at 9:19

answered Oct 2, 2019 at 9:05

slim

41.5k14 gold badges101 silver badges131 bronze badges

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

3 Comments

devnull Over a year ago

thank you for your answer. Could you please add an example on how it would be done as a prepared statement and perhaps how to check if the query was successful e.g. by printing "record added" ? sorry if this sounds trivial but I think it will benefit other readers too.

slim Over a year ago

If you want an example of Prepare in use, then you should have asked that question. But there are plenty of examples elsewhere, so it's not clear why it belongs in SO. go-database-sql.org/prepared.html

devnull Over a year ago

fair enough but my question states Also, what is the best way to detect if the row has been inserted?

Collectives™ on Stack Overflow

Secure insert queries with go-sql-driver and mysql

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related