What is SQL injection and how do I avoid it in Go?

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
, "[email protected]"
, "Jonathan"
, and "Calhoun"
) to their respective columns.

INSERT INTO users (age, email, first_name, last_name)  
VALUES (30, '[email protected]', '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='[email protected]';

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 [email protected]
, 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.

By using the database/sql
package to create our SQL statements, we get a free layer of protection against this. The database/sql
package is aware of all special SQL characters, so when you try to insert a string with a single quote ( '
) into a statement being constructed by the database/sql
package it will escape the special characters and prevent any nefarious SQL statements from ever being executed.

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

稿源:Calhounio (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 综合技术 » What is SQL injection and how do I avoid it in Go?

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录