How To: The Essential Newbie's Guide to SQL Injections and Manipulating Data in a MySQL Database

No doubt you've seen some of the hack logs being released. One part that stands out over and over again is the heavy database usage. It used to be early on that virus and hackers would destroy data, usually just for lulz. However, with the explosive commercial growth of the Internet, the real target is turning into data theft. You should learn how this happens so you can protect yourself accordingly. Let's take a look at what makes this possible and dare I say, easy.

Structured Query Language?

SQL (Structured Query Language) is a very powerful and diverse programming language used to create and query databases. As SQL is only a language, there are several flavors of software you can use to operate and manage the database. Together, these are called Relation Database Management Systems and are simply software that is used to manipulate the database.

Common RDBMS include:

• Microsoft SQL server
• MySQL
• PostgreSQL
• SQL Lite

It's worth noting that each of these have slight variations on syntax, and with MySQL being the most common, we'll focus on that in this article.

Now, an RDBMS is called a relational database system because the data is stored in tables. Before this, data was stored in long data files with entries delimited with special characters. This made searching and retrieving data harder then it should have been. As you can see from the fictional example below, tables are made of rows and columns.

In a relational database, you can quickly compare data and stored values because of the arrangement of data in columns. This model takes advantage of this uniformity to build completely new tables out of required information from existing tables. In other words, it uses the relationship of similar data to increase the speed and versatility of the database.

Knowing what a database is is all good and well, but useless if you don't know the commands and the syntax, right? SQL is simple starting out and like most other programming languages, expands the deeper you go. Let's take a few examples of some of the MySQL code you might encounter. Again, we choose MySQL here because it is the most common database server you will find in the wild. These can be used in a command line and more commonly embedded into code like PHP.

SELECT

SELECT * FROM table_name ;

Returns all rows and all columns from table_name. The wild card (*) means all rows and all columns. This is an easy way to view the entire contents of a table. Switching the wild card with a search string allows you to select only the entries you are looking for.

INSERT

INSERT INTO table_name ( col1, col2, col3) VALUES ( 'col1_data', 'col2_data', 'col3_data') ;

Inserts a row into the table using the data defined in the VALUES section.

As you can see, the column names are established within the first set of parenthesis, the order of the data in the second set of parenthesis must match the order of the column names defined in the first set of parentheses.

DELETE

DELETE FROM table_name WHERE column_name='search_data' ;

The Delete command does just what you might expect it to do, it deletes entries. In its most simple syntax:

DELETE FROM table_name ;

This statement clears the contents of the table, but leaves the actual table there. The WHERE clause is letting you specify what parts should be deleted. If you had a table named nullbyte and a row named users, you could remove Allen from the table with:

DELETE FROM nullbyte WHERE users = 'Allen' ;

DROP TABLE

DROP TABLE tablename1 ;

The Drop Table command deletes a table from the working database in the RDBMS. Again, not to be confused with DELETE, which will only empty the table.

In fact, if we could not do it this way, we might be faced with a maintenance nightmare. You will see this command often in hacklogs at the end.

SQL Operators

There are two type of operators, namely comparison operators and logical operators. These operators are used mainly in the WHERE clause to filter the data to be selected.

Comparison operators are used to compare the column data with specific values in a condition. This functions just like standard programming and math. For the non-coders reading this, below is a list.

• = equal to
• != is not equal to
• < less than
• > greater than
• >= greater than or equal to
• <= less than or equal to

Examples

Let's say you just rooted a web server. We'll assume you know that the root password for the MySQL databases are the same as the machine you just compromised. What do you do?

First, you want to log into MySQL and see what goodies are waiting for you. You do this by:

$ mysql -u root -p

The -u flag is telling MySQL you are logging in as root. From this point, it is loot and pillage time. We want to see what we are working with, so we type:

mysql> show databases ;

Notice how we add a semicolon at the end to let SQL know that's the end of our statement. This is very common in most programming languages.

Now what do we have here? A database named user_accounts? This looks like something we might want to take a look at. First we need to connect to the database by:

mysql> use user_accounts ;

Once we have that connection made, we want to look at the tables with:

mysql> show tables ;

Let's peek at the table now. Remember the SELECT statement from above?

mysql> SELECT * FROM user_accounts ;

Obviously, these are made up names and addresses and such, but this could be a very real table on a very real database. As you can see, this attacker just hit the money.

SQL Injection

SQL Injection happens when a server accepts user input that is directly placed into an SQL statement and doesn't properly filter out dangerous characters. This can allow an attacker to not only steal data from a database, but also modify and delete it. Certain SQL servers such as Microsoft SQL Server also contain stored and extended procedures (database server functions). If an attacker can obtain access to these procedures, it may be possible to compromise the entire machine.

Attackers commonly insert single quotes into a URL query string, or into a form's input field to test for SQL Injection. If an attacker receives a syntax error message, there is a good chance that the application is vulnerable to SQL Injection.

That being said, injection is one of the most common vectors used to attack a server hosting an SQL database. This is because web applications are typically deployed as Internet-facing and if written in-house, their code will probably not have been subject to the same stringent security auditing as commercial software. If the user input is allowed to be passed directly to the database, then special control characters can be typed in. If this happens, an attacker can turn what was a harmless search for a password into tricking the server to dumping the contents of the entire database. Yeah, whoa. 

For example, imagine this line of code:

SELECT * FROM Users WHERE Username='$username' AND Password='$password' ;

Which is designed to show all records from the table users for a username and password supplied by a user. Using a web interface, when prompted for his username and password, an attacker might enter:

1' or '1' = '1

1' or '1' = '1

Resulting in the query:

SELECT * FROM Users WHERE Username='1' OR '1' = '1' AND Password='1' OR '1' = '1' ;

The attacker has effectively injected a whole OR condition into the authentication process. Worse, the condition '1' = '1' is always true, so this SQL query will always result in the authentication process being bypassed.

Headshot. Boom. Tango down.

Debian/Ubuntu Users Read This!

Note that the Debian/Ubuntu distribution will have an additional file /etc/mysql/debian.cnf. This file holds a password for the user debian-sys-maint, which is used by the install tool dpkg to perform database upgrades. This can also be used in emergencies if you forget the root password. It is also a security hole if the file is available to others!

In Closing

There are many kinds of code injections, not only SQL. There are also many types of SQL injection, not only the examples above. Most of the time, this issue derives from lazy administrators and programmers who should know better. Other times, this is a highly complicated vector that takes a little knowledge to pull off. Some mitigating factors include keeping all SQL scripts defined within stored procedures and managing permissions accordingly so one compromise does not sink the entire ship. That being said, most of the time—simply a programmer error.

Now you know what to watch out for and what to look for.

Questions? Concerns? Do you know other injection examples you would like to share? Leave us a comment or visit our forum.