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:
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.
Just updated your iPhone to iOS 18? You'll find a ton of hot new features for some of your most-used Apple apps. Dive in and see for yourself:
6 Comments
This is something i've been looking all over for.. ^-^ Thank you!! You are officially my new best buddy :D
Hey i dont understand this part:
"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."
Because i dont undertsaned the 1' or '1' = '1 meaning and why he bypass the password
Look closely at the resulting query above. You can see that it's the same as the first, only instead of $username (a variable), you have 1' or '1' = '1 (the string passed in to the $username variable). Because the string passed in contains ticks (the ' symbol), it effectively closes the string being looked for in the username column, and adds an OR clause to the logic of the query that will always return true (because '1' is always equal to '1'). So, when the query runs, let's say we're comparing against the username "admin" in the username column. If the WHERE condition checks to see if 'admin' = '1', that will be false, but then because the injected OR clause is '1' = '1', which is always true, a false OR a true always evaluates as true. As a result, this query will return every single record in the Users table. Does that help make sense?
Edgar:
The statement becomes password = '1' OR '1' = '1'. The first part evaluates to false and the second part evaluates to true. With a logical OR, if either side is true then the whole statement evaluates to true.
Does that make sense?
OTW
Thnx for the help i understand it now
1.How do i extract the names of the tables, in your example you have used 'users' as the name for the user table but wat if the actual database has a diferent name for the user table.
Share Your Thoughts