Golang Web Application and MSSQL Injection Attacks

Not long ago I wrote a post on the first steps in developing an application that talks to an MSSQL database.

Those examples worked, obviously. I was compiling and testing the application before posting information. The example was my test case for a package that was later integrated into an application that integrated a web application with database access; that was when someone pointed out something that I should have checked earlier, but hadn't.

Namely in this line:

 // Add a record entry
 _, err := db.Exec("USE " + strDBName + "; INSERT INTO testtable (source, timestamp, content) VALUES ('" + strSource + "','" + strconv.FormatInt(int64Timestamp, 10) + "','" + strContent + "');")

In this statement there is a bit of content that comes from a user and is added to the database (namely strContent). Because it is concatenated, the string passed can be either a parameter or part of a query; a mischievous user could send commands to alter the database rather than just a value to add to the table.

Whoopsie!

I'll leave it up to you to Google what SQL injection attacks are. There has been a metaphorical ton of digital ink spilled discussing that topic. The summary is they're bad, and they're a very basic mistake in making a web app.

The good news in my case is that for what I was doing, the strContent string was partially filtered; before it was hitting the database, it was being fed into the template library. The template library made the text safe for HTML rendering, so much of the punctuation needed to turn that data into part of a query was transformed into HTML punctuation.

That's kind of a leather armor defense against injection attacks. The next step is to upgrade to chain mail.

The next thing to do is parameterize the query. This makes sure the query doesn't treat that string as part of the query to execute, parameterizing encapsulates the string and protects the database from clever users.

In Go, parameterization is characterized by:

db.Query("SELECT name FROM users WHERE age=?", userinput)  // OK
db.Query("SELECT name FROM users WHERE age=" + userinput)  // BAD

I altered the sample entry by doing the following:

 // Add a record entry
strTimeStamp := strconv.FormatInt(int64Timestamp, 10)
_, err := db.Exec("INSERT INTO "+strDBName+" (source, timestamp, content) VALUES (?,?,?);", strSource, strTimeStamp, strContent)

Basically the "?" marks are stand-ins for the variables in the query statement. I'm not sure if that fully secures the application from injection attacks, but I think it is a step in the right direction. If anyone has more information or suggestions, feel free to leave a comment...