{ Soham Kamani }

About โ€ข Blog โ€ข Github โ€ข Twitter

Web security essentials - SQL Injection ๐Ÿ”‘

So you thought your database was completely safe? Well, for the most part, it actually is. Modern databases have lots of security features to prevent them from being compromised by an attacker. However, even with the most secure database, there is still a way in which a backdoor can be created to compromise it right from the browser!

This post will explain why SQL injection occurs and how you can prevent it.

For starters, lets assume thereโ€™s a simple form on your website to sign up a new user.

signupform

Now as soon as you sign up a new user, you have to create a new entry in the database.

From the backend, the database query would look something like :

INSERT INTO `users` (username, password) VALUES ('<username>', '<password>');

What you would now do is replace <username> and <password> with the values entered in our sign up form. So, if I entered my username as John Doe, and password as j0hnd03, my insert query would like :

INSERT INTO `users` (username, password) VALUES ('John Doe', 'j0hnd03');

Now, for a normal use case, this would work just fine. But what happens if I got smart and changed my username to ','');DROP TABLE `users`;-- ?

Once we insert this username into our query, it turns into :

INSERT INTO `users` (username, password) VALUES ('','');DROP TABLE `users`;--', '<password>');

Woahโ€ฆ what happened here? It turns out, that this query recognizes special characters like ` , " , or - as part of the query, and not as part of the input parameters. Since we are naively concatenating the entered username and password to generate our query, we donโ€™t exactly have a way to tell our query that these special characters should be a part of the username, and not a part of the query.

The result, as expected, is disastrous. In this case, it leads to our entire users table being dropped. Ouch!

Preventing SQL Injection

Make sure you always escape all your SQL queries! This means replacing special characters like ` , " with their escaped versions (i.e \` and \")

This is actually easier said than done, considering larger projects often have hundreds of queries with even more parameters to take care of, and itโ€™s often impossible to make sure everything is escaped. The solution?

Use an ORM instead of writing your own queries. ORMs like this one for python, or this one for NodeJs make sure that your queries are safe, even if you have a lot of questionable characters in your input parameters.

If you want to learn more about security on the web, be sure to read my other posts on web security essentials :

XSS (Cross site scripting)
CORS (Cross origin resource sharing)
Password storage
Sessions and cookies
CSRF (Cross site request forgery)
Human Error and UI/UX design


Like what I write? Join my mailing list, and I'll let you know whenever I write another post

Comments

Soham Kamani

Written by Soham Kamani, an author,and a full-stack developer who has extensive experience in the JavaScript ecosystem, and building large scale applications in Go. He is an open source enthusiast and an avid blogger. You should follow him on Twitter