CODEWITHSUNDEEP
go
devnull
devnull

Reputation: 2862

Secure insert queries with go-sql-driver and mysql

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.

Upvotes: 0

Views: 1687

Answers (1)

slim
slim

Reputation: 41223

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.

Upvotes: 2

Related Questions