no. 13| 06.06.2014 |
SQL Injections.
As a beginning coder, this kind of stuff freaks me out. SQL Injections are one of many security issues that software developers have to be on top of. SQL Injection is seriously old school, but it turns lots of websites are still vulnerable to this style of exploit. If experienced programmers are slipping up on this, what chance do I stand?
After doing a bit of research, there are at least a couple of places to start. The first is awareness. Knowing SQL Injections exist, and knowing the basics of how they work is helpful, and that leads to a couple of “best practices” that I can try to turn into regular habits.
So, first, what is SQLi?
One of the fundamental purposes of a web-based application is to serve as an interface between the end user and some kind of data. Applications take requests from users like, “make a new user account for me”, “show me a list of products”, “show me my recent transactions”, “show me some photos”, etc.. Fulfilling these requests means interacting with a database, which almost always means using SQL.
The “path from user to web app” often goes through a field on a web page. A “create account” page would have a field where you’d type in your hoped-for username. A product page might have a search field that gets user input like “skinny jeans” or “creamed corn”. Our apps pull that user-provided input into SQL queries and send them on to a DBMS that will return relevant data from, or make changes to a database.
The security risk comes when a user puts a carefully crafted string into an input field (or tacks the right stuff onto the end of an http request) that when shoved into an SQL query by an app, changes the SQL query. Usually to something bad for the other users of the app.
Here’s an example (thanks Wikipedia):
statement = "SELECT * FROM users WHERE name ='" + userName + "';"
This SQL code is designed to pull up the records of the specified username from its table of users. However, if the "userName" variable is crafted in a specific way by a malicious user, the SQL statement may do more than the code author intended. For example, setting the "userName" variable as:
' or '1'='1
renders one of the following SQL statements by the parent language:
SELECT * FROM users WHERE name = '' OR '1'='1';
If this code were to be used in an authentication procedure then this example could be used to force the selection of a valid username because the evaluation of '1'='1' is always true.
There are other SQLi tactics that can be used to return the entire contents of a database using that same ‘ or ‘1’ = ‘1 trick. Since the OR will always evaluate “true”, every record will match and get pulled by the DBMS. Even worse? Things like 1;DROP TABLE users
Congratulations asshole user. Your trickery has impressed us all.
So what’s the takeaway for a novice developer? First, never trust raw user input. Anything coming into your app from a user should be validated before it’s passed along to parts of the app that need to use it. There are lots of ways to do this and frameworks to help, but the general idea is to look through the input and allow only characters and character combinations that can’t be mistaken for code. “Don’t trust user input” is a helpful, high leverage best practice because it applies to more than just SQL - JavaScript is but one other example.
If you find yourself concatenating a variable full of raw user input to pretty much anything, probably stop what you’re doing and figure out how to validate the input first.
That leads me to my second beginner best practice. Ask for the advice of more experienced folk. The landscape of web security vulnerabilities is constantly changing and it’s really hard to know if you’re doing the right thing. The right thing might have changed since yesterday.