This post is an extract from a larger post about inserting records into an SQL database with Go's database/sql package, but I am also publishing it on its own because I believe it is an important topic that deserves to be covered on it's own. If you enjoyed this post, you might enjoy some of the others in my series "Using PostgreSQL with Go" where I cover installing PostgreSQL, using it on its own, and then we jump into using it with Go.
I am going to assume you have basic SQL knowledge
Going into this article, I am going to assume that you have some basic experience with SQL, so you probably know that something like the code snippet below might be used to insert a new user record into a users table, assigning each of the provided values (30, "jon@calhoun.io", "Jonathan", and "Calhoun") to their respective columns.
INSERT INTO users (age, email, first_name, last_name)
VALUES (30, 'jon@calhoun.io', 'Jonathan', 'Calhoun');
Similarly, you should know that you could use the following SQL to look up a user from the same table.
SELECT * FROM users WHERE email='jon@calhoun.io';
The rest of this article will focus on the SELECT, so you don't technically have to know what the first does to get value from this post, but it will likely help.
What is SQL injection?
SQL injection happens when a developer like yourself has enough knowledge to create SQL queries like the ones shown above and thinks, "Why don't I just create the SQL statement on my own using something like fmt.Sprintf()?". At first this seems perfectly acceptable. The queries you are creating are relatively simple, so why bother learning about a whole new package when we know what we are doing? So you start coding and come up with some code like the code shown below.
func buildSql(email string) string {
return fmt.Sprintf("SELECT * FROM users WHERE email='%s';", email)
}
While this might seem okay at first, it actually has a few potential issues and they are big ones.
The problem with this approach is that there are countless edge cases that you are unlikely to account for, and at some point or another one of them is going to bite you.
This problem isn't limited to just SQL. When you create strings using packages like fmt you are working with a package that is completely unaware of your intentions or the context that you are modifying strings under. When we are printing out log statements and doing relatively simple things this is usually fine, but it can become problematic fairly quickly when we are working with more complicated technologies like SQL or HTML.
This is much easier to explain with an example, so let's imagine that you wanted to recreate the SQL statement above using the fmt package and you came up with some code like the following:
func insertQuery(email string) string {
return fmt.Sprintf("SELECT * FROM users WHERE email='%s';", email)
}
While this might appear to work with an email address like jon@calhoun.io, what happens if a user attempts to sign in using an email address like '; DROP TABLE users;'? Go ahead and try it - https://play.golang.org/p/v9qXpK4IrQ
You should see an SQL statement that looks like the following.
SELECT * FROM users WHERE email=''; DROP TABLE users;'';
Looks good right? Wrong! If you take a closer look this statement doesn't just look for a user, but it also adds an extra SQL statement to the end that drops the entire users table! Yikes! That would mean you would lose ALL of your user data if this were executed.
This is called SQL injection, and it happens when you let users input data that needs to be used in SQL statements and you don't escape any special characters, like the single quote (') character. Unfortunately, this is probably one of the most common ways that "hackers" will attempt to attack your website, and while some SQL injection attacks can be used to gain data, a large chunk of them will simply destroy a large portion of your data, leaving you with an empty database and a lot of explaining to do to your users.
How do we avoid this in Go?
By using the database/sql package along with argument placeholders we are able to construct SQL statements that are automatically escaped properly. For example, you might have some code that looks roughly like this:
// This is for Postgres. Other SQL variants may use the ? character.
db.Exec("INSERT INTO users(name, email) VALUES($1, $2)",
"Jon Calhoun", "jon@calhoun.io")
The key distinction here is that we aren't trying to construct the SQL statement ourselves, but are instead providing arguments that can be easily escaped for us. The underlying driver for database/sql will ultimately be aware of what special characters it needs to handle and will escape them for us, preventing any nefarious SQL from running.
This is easier to understand with an example, so let's go back to the previous example where we want to search for a user using an email address. Rather than executing the dangerous SQL above, the database/sql package (along with a driver) would instead execute something like the SQL below.
SELECT * FROM users WHERE email='''; DROP TABLE users;''';
While this might look very similar, there is one very significant difference - the single quotes in the email address are doubled up, which is how you escape a single quote character in SQL. It would be the equivalent to putting a backslash before a quote in Go. eg fmt.Println("\"hi\", said the man") would output "hi", said the man, and '''; DROP TABLE users;''' is treated like the string '; DROP TABLE users;' in SQL, so rather than executing the dangerous DROP TABLE users; command, this statement would search for a user with the email address '; DROP TABLE users;'. This likely won't return any users, but more importantly it wouldn't delete and of your data!
So the short version of this story is always use the database/sql package to construct SQL statements and insert values into them*. It will save you a lot of headaches down the road, I promise.
Did you enjoy this article? Join my mailing list!
If you enjoyed this article, please consider joining my mailing list.
I will send you roughly one email every week letting you know about new articles or screencasts (like this one) that I am working on or have published recently. No spam. No selling your emails. Nothing shady - I'll treat your inbox like it was my own.
As a special thank you for joining, I'll also send you a both screencast and ebook samples from my upcoming course, Web Development with Go.
Top comments (7)
Please at least mention argument placeholders!
See golang.org/pkg/database/sql/#db.Query it's in the Examples!
database/sql doesn't even escape those args, just handles them to the underlying driver, which sends them to the database separately (at least for Oracle, which I'm quite sure of, as I'm the maintainer of the rana/ora driver).
This post was originally written in the context of Postgres, and as I understand it each driver defines its own set of argument placeholders (see github.com/golang/go/issues/3602), which is why this post only really covers $1.
I'll see if I can tweak the post to make it clearer that we are referring to Postgres here and that other drivers use different placeholders.
Sorry, I can't find the word "placeholder" in the article. That's my concern, not postgres or whatever.
I just realized I didn't show an example of using placeholders in this article. My original draft did so not sure how it got dropped. That is why I mentioned
$1in the comment. I'll update this shortly with more context and thanks for pointing it out!Edit: Done. Is that clearer now?
One more contribution, multiple statements are unsupported for database/sql in Exec function, No matter if we send a single argument for this function, if this arg has sql injection the Exec function will return us a error.
SELECT * FROM users WHERE email='''; DROP TABLE users;'''; doesnt work
Is database/sql ecaping or placeholders with precompiled queries? If it's the former, you sould stop recommending it. If it's the ladder, you should make that a bit clearer in your article
I just realized I didn't show an example of using placeholders in this article which makes this clearer. As stated in another comment, I'm going to update shortly to make this more explicit.
Edit: Done. Is that clearer now?