Note: This site is still fairly new. Please feel free to contact us with any issues you may run into.
rewt
Site Admin
Posts: 18
Joined: Sun Nov 29, 2015 10:20 pm
Contact: Website

SQL Injection Tutorial - Part 1 Introduction to SQL

Sat Feb 06, 2016 6:14 am

SQL Injection - Own the DB

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 />"); } }
As you can see, that is a very basic, yet working, example of how an application might use SQL to get data from a MySQL database. Let's break down some of that SQL query.
    -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'";
Notice that the variable $newsid comes from $_GET['newsid'] which is a variable that we specified in the URL: viewnews.php?newsid=10. After the variable is accounted for, that query looks like this:

Code: Select all

$sql_query = "SELECT title,article FROM news WHERE newsid='10'";
In this example, anything that we specify as newsid will be read into the SQL query as the variable $newsid. If we specify viewnews.php?newsid=libroot, that query will reflect:

Code: Select all

$sql_query = "SELECT title,article FROM news WHERE newsid='libroot'";
At the very least, one can see why this would be an issue. `newsid` is supposed to be an integer and a string is inputted. In that example, the database will return null. Let's look at a more useful example. viewnews.php?newsid=10' OR newsid='1000.

Code: Select all

$sql_query = "SELECT title,article FROM news WHERE newsid='10' OR newsid='1000'";
Now instead of displaying just the news where newsid=10, the application will additionally display the news where newsid=1000. We were able to do this by using a quotation mark to simulate the end of the conditional check in the query (something that only the web application should be doing). This is a prime example of SQL injection as we can very easily specify any criteria for which the news to be loaded. With this, we could pull all of the news with one request by specifying viewnews.php?newsid=10' or newsid > '0. And depending on how many news rows there are and the power of the server, that query could potentially slow the server's response time or, at best, crash the database.

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
A normal request would look like password=SecretP@$$word&username=Sally (keep in mind that we're sending with $_POST, so we're not seeing this data in the URI). However, a malicious request could look something like this password=SecretP@$$word&username=admin' OR username='Sally. Now the query will reflect this:

Code: Select all

$sql_query = "SELECT password,username FROM users WHERE username='admin' OR username='Sally'";
In this example, we're pulling usernames and passwords for both admin and Sally with our injection. Depending on how the result of the MySQLi query is processed, we could use that query to have the application check if Sally's password matches the password given (in this case yes, since Sally knows her own password) and then return the admin username for authentication (logging in as admin).

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
Sally's above query would reflect this in the SQL query:

Code: Select all

$sql_query = "SELECT biography FROM user_table WHERE user='Sally'";
Now, a more exciting query could look like viewuser.php?profile_column=password&user=admin. Check out how PHP sees that query now:

Code: Select all

$sql_query = "SELECT password FROM user_table WHERE user='admin'";
And as easy as that, we're able to take data that was very obviously not meant for us. And as you can see with this example, we didn't have to alter anything by using quotation marks or any other characters. We just realized that we had an injection point with the profile_column in that we could completely change the function of the application.

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.
Valar Morghulis
-libroot founder

User avatar
Disclosure
Posts: 2
Joined: Tue Dec 29, 2015 4:13 am

Re: SQL Injection Tutorial - Part 1 Introduction to SQL

Mon Feb 08, 2016 11:08 am

This is definitely a solid introduction to SQL injection. Probably the best article for beginner SQLi that I have ever read. I'll be waiting on future articles related to this (:
Image

mitch3ls
Posts: 6
Joined: Sat Feb 13, 2016 7:29 pm

Re: SQL Injection Tutorial - Part 1 Introduction to SQL

Sat Feb 13, 2016 8:45 pm

I think I've fallen in love with this site. If the future articles can maintain that quality, I'm more than impressed :D
my 127.0.0.1 is my castle

rewt
Site Admin
Posts: 18
Joined: Sun Nov 29, 2015 10:20 pm
Contact: Website

Re: SQL Injection Tutorial - Part 1 Introduction to SQL

Sat Feb 13, 2016 11:53 pm

I think I've fallen in love with this site. If the future articles can maintain that quality, I'm more than impressed :D
I'm glad to hear it! I'm currently working on the second article among a few other things. Right now, I'm doing some reworking on the backend system, adding laravel to the mix next to phpBB to allow for some more core functionality other than just the forums. That, plus the article, and real life obligations is what's going on right now.
Valar Morghulis
-libroot founder

Estingabyz
Posts: 1
Joined: Sun Mar 06, 2016 8:17 am

SQL Injection Tutorial Part 1 Introduction to SQL

Mon Mar 07, 2016 10:47 pm

Another thing I would like is a tutorial where, depending on where the cursor is in relation to the centre point, the speed of the rotation increase, decrease or change direction.

Does anybody tutorial like this?

mitch3ls
Posts: 6
Joined: Sat Feb 13, 2016 7:29 pm

Re: SQL Injection Tutorial Part 1 Introduction to SQL

Fri Mar 18, 2016 6:52 pm

Another thing I would like is a tutorial where, depending on where the cursor is in relation to the centre point, the speed of the rotation increase, decrease or change direction.

Does anybody tutorial like this?
Your post doesn't quite belong to here. Programming related questions and suggestions should be posted in the Programming Forum (viewforum.php?f=7), where the chances of someone reading and picking up your topic are way higher. :)
my 127.0.0.1 is my castle

Return to “Web-related”