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