Preventing SQL Injections
Nik Kenney - October 9, 2018
Danger, Will Robinson
Databases are a sacred place where website/application information is stored, hallowed ground for all of your personal identifiable data. Because of this, databases are what hackers are (almost) always after. There are a few methods they may use to try and do shady things like stealing the data or break your database, the most malicious of which is something called SQL Injections. But as dangerous as they are, they’re also one of the easiest things to protect against if you know what you’re doing. So let’s discuss stuff.
Also, I wanted to point out that I hoped to provide some code examples, but WordPress has some security features that prevented me from doing so. Because, security!
All Your (data) Base Are Belong To Us
So SQL statements are basically lines of code you type to interact with a database. They can be anything from “Get Everything From users where username=’yourUsername'” to something as dangerous as “Delete Table users”. Obviously, you don’t want someone other than you getting your information, or removing data from your database. But you can’t just type statements anywhere to do the damage. It has to get to the database first to do the damage. The main way this happens is through form submissions. When you type in a username and password, the application will use your username to know which row in the users’ table to get the information for. And with a little bit of know-how, you can use this to your advantage. You could type an SQL statement as a username, as long as you have a few special characters before it, in a username box, which would basically have an ending single quote followed by a semicolon to end the select statement, then follow up with another statement such as one that deletes an entire table.
You Shall Not Pass
A lot of little things can be done to prevent this kind of stuff, like having unconventional (but not entirely unrelated) table/column names that aren’t easy to guess. If you type “Delete table users”, but you don’t have a table named “users”, then nothing is going to get dropped. Even if you just prefix it with something like “app_users”, it’ll make it harder for people to guess what it could be. And without being able to SEE the database, it’s effectively just them taking stabs in the dark.
Disclaimer: Red Shark Digital does not condone stabbing people in the dark. In fact, we condemn all stabbing.
WordPress actually does something similar to this on initial installation. You can choose a table prefix, the default being “wp_”, that gets added before the name of each table in the database being used for the site. It’s extremely simple but highly effective. But let’s say you were lazy, or maybe you have an older application that you’re suddenly worried about and don’t want to rename ALL of your database tables with a prefix, or don’t feel like it’s enough (better safe than sorry) to secure your application. There’s another nifty thing called “Prepared Statements” that you can use to add yet another layer of security.
You Killed My Father, Prepare To Die
So the easy way to submit a query is to just use a query function that runs said statement. You have some kind of function that will go to the database with the query and run it. If someone with the right level of know-how decides to type an SQL statement in there and do it in the right way, they can do some major harm. They could potentially even submit a query that removes the tables if they know what they’re called. And that’s it. Your users’ table is gone. Now sure, you can do what’s called “escaping” for special characters. This is a simple process that puts an escape character, which is a backslash, to let the application know that this part of a string of text, and should not be treated as a statement-altering character. It’s good practice to implement this, which is why it is a part of what preparing statements do. It ultimately comes down to having a second line of code. So instead of one line that runs a query, you have one that prepares it and binds a variable, then a second that executes the statement
And that’s all there is to it. It’s a single extra line of code that will make all the difference in the world. Essentially, the “prepare” function throws in a variable (and can take multiple ones) that says “Hey, I’m gonna put something here later, but I want to do stuff to it first”. Then the “execute” portion will replace that variable with the actual value, but not before doing a few things (such as escaping special characters) to the value first. So worst case scenario, the database will look for a username that is equal to “‘HEY KILL THE DATABASE”, which will return no results, thus no harm is done.
That’s All, Folks
As usual, this won’t prevent 100% of the issues. It will, however, make a HUGE difference in preventing any malicious attacks that basement-dwelling hackers will try and throw at your website or application. It’s basically the difference between putting your diary in your pillowcase vs putting your diary in a locked box and keeping the key with you at all times. Not perfectly secure, but safe enough that no one will find out your embarrassing secrets.
Red Shark Digital is a superior full-service digital marketing agency that specializes in Web Design, SEO, Branding, and Digital Advertising. With offices across North Carolina including Raleigh, Greenville, and Wilmington, our team of digital marketing specialists are able to create catered solutions for your business needs. Our clients have allowed us to push the boundaries of web and creative design, while building innovative results for their company and their brand. These clients have encompassed multiple industries including higher education, healthcare, and major commercial franchises. Our wide range of clientele has allowed us to craft specialized strategies for businesses of all sizes while conveying our knowledge across a variety of platforms. We always strive to connect brands to customers, learn what we can do for your business today.