Chances are, if you're interested in hacking, you've heard of SQL injection. In fact, OWASP Top 10 2013 lists injection as the #1 security vulnerability in websites, and SQL injection is by far the most prevalent injection used. Understanding what SQL injection is and how it is used, is valuable knowledge. That said, this tutorial is written assuming the reader has at least a basic understanding of programming (namely PHP) and how websites work.
Now, what is SQL?
SQL stands for Structured Query Language. In essence, SQL is the language that allows for an application to communicate with a database. However, the specific syntax will vary between databases. MySQL and SQL Server, for example, are both SQL databases, but they will have slight differences in the syntax. Another thing to note is that there are non-SQL based databases as well. These are commonly referred to as NoSQL or non-relational databases (SQL databases are relational as they are designed with relations between stored data). In short, databases store data, and SQL is a form of language used to query that data from the database. For the purpose of this writeup, we'll focus on MySQL as that is what we see most of out in the wild.
To avoid confusion concerning MySQL databases, here is a quick overview. A database contains all of the data for a certain project. A table is the next size down, containing all of the data for a specific purpose. Next are the columns, which specify data types and requirements for specific rows (rows are the individual sections of data inputted into a database).
Here's a quick example. For a website with a forum, a database might contain all of our forum data. The database could be called `myforum`. A given table in the database might be the `user` table which contains all of the data regarding users. A couple columns within the `user` table might be `username`, `password`, or `email`. Some column specifications on these columns might be data types, lengths, default values, etc. Finally, the rows contain the data correlated to the columns. So the `username` column could contain "Sally" ("Sally" is part of the row). Moving on.
Example #1: News Time
Let's take a look at an example of what a basic MySQL/PHP5 setup might look like in a simple web application. We'll say Sally is visiting our news site and follows a link at http://mynewssite.com/viewnews.php?newsid=10.* Sally is presented with a webpage that displays the news article that she was expecting, but before this happens, our application needs to talk with the database in order to get the data for the news article. Here's an example of what that PHP code might look like at viewnews.php:
Code: Select all
<?php
//Handle MySQLi connection
$mysqli = new mysqli("localhost", "username", "secret_password", "database_name");
//Craft the query that we'll use to grab the news title and article
$newsid = $_GET['newsid'];
$sql_query = "SELECT title,article FROM news WHERE newsid='$newsid'";
$result = $mysqli->query($sql_query); //Send the query to the database
if($result) { //Check the result
while($row = $result->fetch_assoc()) {
//Display data
echo($row['title']);
echo("<br />");
echo($row['article']);
echo("<br />");
}
}
- -SELECT is the command. We're telling MySQL that we're going to be SELECTing data from the database. Other commands include UPDATE (update data in the database) or DROP (delete data from the database).
-title,article are the columns from the table that we are going to be selecting. This tells the database that the only data we are interested in are the title and the article sections of our specified table.
-FROM is pretty self explanatory. It tells MySQL that the next bit specifies the table.
-news is the name of the table that we are pulling data from.
-WHERE signals a conditional check. This means the next bit specifies some criteria for which data we want to receive.
-newsid='$newsid' is the specification for the conditional check. newsid is the column name and the ='$newsid' means we want the current row to match the $newsid variable. In addition to =, we can use comparison operators such as < or >.
Now that we've covered the general concept behind databases, we can start looking at how injections play a role in everything. To make things easier, let's take a step back and look at the overall picture. The client sends that data (a.k.a. user input) to the application, the application processes that data, the application sends processed data to the database where the database sends the requested data back to the application. It is important to realize that the database isn't biased at all to how that data was sent to it. If a query is valid when it is sent to the database, the database will happily process it and send it back. The vulnerability associated with injections lies in that logic: if we can trick the application into sending a query that we have somehow altered to the database, then we have successfully performed an SQL injection.
Let me explain. If a web application does not properly process our user input, then it can leave open a point of injection. That is, a point for us to inject data that will be processed as part of the query. Our example above demonstrates this vulnerability very simply. Look carefully at the query:
Code: Select all
$sql_query = "SELECT title,article FROM news WHERE newsid='$newsid'";Code: Select all
$sql_query = "SELECT title,article FROM news WHERE newsid='10'";Code: Select all
$sql_query = "SELECT title,article FROM news WHERE newsid='libroot'";Code: Select all
$sql_query = "SELECT title,article FROM news WHERE newsid='10' OR newsid='1000'";While stealing people's publicly available news articles is fun, it's more useful for us to take a look out how malicious hackers utilize SQL injection for their advantage. Here are two examples that demonstrate how handy SQLi can be to an attacker.
Example #2: The login form
Sally is logging in to http://mysocialmediasite.net/login.php where `username` and `password` are submitted to the server through $_POST data. Here is a snippet of the PHP code that handles that request:
Code: Select all
<?php
//stuffs
$username = $_POST['username'];
$password = $_POST['password'];
$sql_query = "SELECT password,username FROM users WHERE username='$username'";
//process query and return the password in $real_password; return the username in $real_username
if($real_password == $password) {
//The given password matches the password from the database, so authenticate this session for the username
$login($real_username);
} else {
fail_login($real_username);
}
//other stuffs
Code: Select all
$sql_query = "SELECT password,username FROM users WHERE username='admin' OR username='Sally'";Example #3: The Jackpot
Sally is browsing a website that displays user profiles based on the $_GET parameter `user`. The profile allows us to view different data of the user depending on the `profile_column`. This is the page that Sally is viewing: viewuser.php?profile_column=biography&user=Sally. In this example, we're pulling the biography for Sally and the application is displaying that data. Here's some backend code:
Code: Select all
<?php
//stuffs
$profile_column = $_GET['profile_column'];
$user = $_GET['user'];
$sql_query = "SELECT $profile_column FROM user_table WHERE user='$user'";
//process query into $data_text
echo("<p>$data_text</p>"); //display data
Code: Select all
$sql_query = "SELECT biography FROM user_table WHERE user='Sally'";Code: Select all
$sql_query = "SELECT password FROM user_table WHERE user='admin'";Mitigation
All of these examples rely on one critical element: User input that was not properly processed. This is the key to SQLi (and many other vulnerabilities as well). You'll often hear a lot of people talk about sanitizing user input. What that means is that the developer is taking the necessary measures to ensure that a user can not alter the queries in the way that we did in the first two examples. MySQLi has some methods for doing this, primarily mysqli::real_escape_string($user_input). What this does is escapes the string for the database, and in effect, preventing users from using special characters to manipulate queries. Other methods for processing user input include data type casting or checking the type of user input, manually escaping, or utilizing security mods (such as Apache's mod_security). It's generally better to utilize proven methods, such as mysqli::real_escape_string(). Along with that, a lot of tools that web developers use have functions that automatically escape all variables in database queries. This is especially true for a lot of modern frameworks. The bottom line when it comes to securing against SQLi, properly handle all user input and ensure logic of the application is secure. Exactly how the data is processed should be dependent on the function of the application and further research should always be done to ensure the security of your applications.
Implications
To wrap up, it should be fairly easy to see just how powerful simple SQLi can be. These vulnerabilities are littered around the internet. If you poke around enough, you are certain to find a site that makes good use of poor security and leaves a couple SQLi holes (where there's one, there's two). I will be following this up with some more advanced SQLi techniques. Remember that knowledge is key in this game, the more you understand about something, the easier it will be to circumvent it.
*For those of you who are unfamiliar with how data is passed between clients and servers, example #1 utilized $_GET. $_GET, along with $_POST, are the two most common methods for a client (the browser) to send data to a server (the website). Both $_GET and $_POST are arrays that are sent in the request packet. In this case, we are passing the parameter `newsid` to the server with a value of 10. If we were to use PHP to grab the value of $_GET['newsid'], we'd get 10. $_POST is like $_GET, although $_POST is not passed through the URL.
