SQL Injection

Because Some People Enjoy Being Terrible

Tuesday, July 14, 2015

"I just learned all this cool stuff about relational databases and SQL and can't wait to integrate it into my site!" Said...well, I'm sure at least one person in history. Rightfully so, I suppose, as from what we have touched on so far, these appear to be very powerful tools for handling and accessing vast quantities of data. With all this power though (oh yeah, I'm going there), comes great responsibility. SQL might be a superhero when it comes to accessing data in relational databases, but it comes with it's own potential kryptonite, SQL Injection.

SQL Injection is most often an intentional attack on a website or database by a malicious user. It can come in a number of different forms, but the idea behind them all is the same. An application asks for some user input, and that input is inserted into an SQL statement. Pretty standard procedure, until Ms./Mr. Code Injector comes along. Assume you ask a user for their username and then stick their input into an SQL statement like this:


          statement = "SELECT * FROM users WHERE name = '" + userName +"';"

Any expected input such as jbomotti would pull up information for the specified user, so no big deal, right? Wrong. If the aforementioned code injector decided to input something like ' OR '1'='1 they would get much more than just information on one user. This would change the statement into:


          SELECT * FROM users WHERE name = '' OR '1'='1';

Since '1'='1' will always evaluate to true, this statement now turns into a massive data dump of all the data in the users table. Not exactly what you'd call great, but certainly not unheard of even among some big name companies. What's the main cause for this vulnerability? Lazy programmers (and not in the good DRY way of lazy).

There are actually some pretty simple ways to remove all SQL Injection vulnerabilities from your web application. The easiest, and arguably (or just outright) most effective way using PHP is hexadecimal conversion. There are two functions, DECHEX() which converts any integer value, and BIN2HEX() which converts any other type of variable. Used together with a call to UNHEX() in the statement, the value isn't applied until after the statement is interpreted. Voila, SQL injection denied.

Conclusion/TL;DR So we learned a bit about SQL this week. Super useful and awesome, but also highly vulnerable to malicious malcontents. Luckily, this vulnerability is also easily avoided by the conscientious coder. So now, go forth, make wonderful web apps and protect your SQL statements!!