Dec 28, 20217 min read

Database Basics for Hackers, Part 1: Getting Started

Updated: Dec 29, 2021

Welcome back, my aspiring cyber warriors!

Although there is a multitude of different hacker types and hacking techniques, the one target they all share is the database. I often refer to the database as the hacker's Holy Grail, or the ultimate prize for an effective hack.

That's because the database—almost by definition—is the repository of all the "goodies" that the hacker is pursuing. These goodies might include credit card data, personally identifiable information (PII), usernames and passwords, and intellectual property. Each of these items can be sold for significant sums of money in the black market.

For instance, credit card numbers can be sold for between $5 to $50 depending upon the quality (gold and platinum cards are worth more than regular cards) and their credit limit. So, a hack that reaps 200,000 credit cards numbers is worth anywhere from $1 to $10 million!

Considering the importance of databases in infosec/cybersecurity, I thought it would be appropriate to begin a series on the basics of databases. Similar to my book, Linux Basics for Hackers, this series will develop the basics of databases including how to manipulate them and their weaknesses and vulnerabilities. It is not intended to make you a database administrator or developer but rather to provide a fundamental understanding of databases necessary to protect and attack them.

What is a Database?

A database is a system of software to store and retrieve information in a structured format. Early databases were flat files, kind of like a big Excel file. As databases got bigger and bigger, this simple structure proved inefficient.

As a result, a scientist at IBM, Dr. Codd, developed a structure that came to be known as the relational database model. It is this model that about 97% of all databases now use, and this includes all the major software companies.

The basics of the relational model is that data should be placed in separate tables with unique keys that link the tables to avoid data duplication and to ease the retrieval of this data.

The Structure of a Database

This relational database model links data from separate tables by using a shared column or "key". The diagram below is of a simple relational database model where all the tables are linked by the column "ID".

Major Vendors in the Database Market

The enterprise database market has multiple vendors offering products that can accomplish the same task, but in different ways. The major players in this market are:

Oracle. They are the behemoth in this market with nearly 50% market share. They own multiple different database software products, including their namesake and MySQL. Oracle is the dominant database software developer in the world and their flagship product is the database of choice among large corporations and financial institutions due to its scalability. You can download Oracle for free from their website.

Microsoft SQL Server. Microsoft entered this market in the early '90s by teaming up with Sybase to develop an enterprise database offering. As a result, MS SQL Server and Sybase still share many similarities. Originally, Microsoft was only a player in the small-business market, but is slowly gaining traction in the larger enterprise market. You can download and use a trial version for 180 days of Microsoft SQL server here.

MySQL/Maria. This is an open-source database that you will find behind so many web sites, in part, because it's free. It is also the database that was originally used in so many major websites such as Google, Amazon and Facebook (since MySQL is open source, each of these companies has enhanced and tailored MySQL to their needs). In recent years, the original developers of MySQL started a new database management system that shares many features with MySQL known as Maria. In most cases, these databases are interchangeable. MySQL/Maria is built into your Kali and is fully functional. You can start it by entering;

kali > sudo systemctl start mysql

IBM DB2. IBM was the original enterprise database provider and made many the major developments in database design, but like everything about IBM, it has been in decline in recent decades.

sqlite - sqlite is an open-source, lightweight relational database. Due to its lightweight and efficiency, it is being built into nearly every mobile device and browsers where data must be stored and retrieved, while using little power and CPU cycles. Although hacking these type of databases has not not been popular among hackers, I suspect as more and more data moves to our mobile devices, it will be come more popular and valuable.

postgresql

postgresql or sometimes referred to as simply postgres, is a free and open-source database management system. It is preferred by some for its enterprise level features such as ACID (Atomicity, Consistency, Isolation and Durability) properties and scalability. It is the default database on the MacOS Server but is also available for Windows, Linux, FreeBSD and OpenBSD. It is installed by default on nearly every Kali version. It can be started in Kali by entering;

kali > sudo systemctl start postgres

Other major vendors include Sybase, SAS, and many others. Generally, like any hack, we need to do good recon to determine the software and version to be successful, as most of the database hacks are vendor specific.

Ports Used by DBMS's

One of the key ways to determine whether a system or network contains a database is to do a port scan with tool such as nmap. Each database management system uses a different port by default. If the default port is open on the system, it likely contains the database. I say "likely" because databases--like any application--can run on any port. Most administrators leave the default port out of convenience. Of the major database management systems, these are there default ports.

Oracle	1521,1830
MS SQL Server	1433, 1434
MySQL/Maria	3306
Postgresql	5432

If you scan a vulnerable Windows system with nmap, you might see results similar to that below indicating it is running mysql on port 3306.

GUI Interfaces

Nearly every user and developer in these database systems uses a GUI for accessing these databases. Nearly every database management system has their own GUI from Oracle's SQL Developer to Microsoft's SQL Server Management Studio (SSMS) to MySQL Workbench and a few others. The GUI interface for SQLite is built into Kali by default.

There are, however, a few general purpose GUI interfaces such as TOAD and DB Beaver. TOAD is an excellent tool for working in various DBMS's but is bit pricey. DB Beaver is nearly as good, works with all the major DBMS's and is open source and free.

We will be using DB Beaver with mysql in this series and you can obtain it from the Kali repository by entering

kali > sudo apt install dbeaver

Structured Query Language (SQL)

When IBM developed the early databases, they also developed a programming language for managing and manipulation this data. They called it "Structured Query Language," or as it is generally known, SQL.

This is a simple language that uses English words in similar ways that humans who speak English use them. For instance...

SELECT means "select some data from columns in a table"
FROM means "get the data from this table"
WHERE means select the data that meets this condition (lastname = 'smith').

Furthermore, words such as UPDATE, INSERT, and DROP mean in SQL exactly what you would expect them to mean.

SQL is not picky about syntax, but it is picky about logic. Although best practice is to CAPITALIZE all keywords (SELECT, FROM, WHERE), it's not required. In addition, white space is ignored. All but Microsoft, though, require that a SQL statement to end in a semicolon (;). On Microsoft products, it's optional.

SQL is standardized by ANSI, but this standardization only includes about 80% of the language or the core of SQL. Software publishers are free to add additional commands and features that are not part of the standard. This can sometimes make it difficult to transport SQL code between DBMS. It also makes it critical to do good reconnaissance on the database to know the manufacturer and the version before attacking as the attacks are often specific to the manufacturer and the version.

Each of the DBMS can be used from a command line, but each has its own GUI. Recently, MySQL released a new GUI called Workbench as seen in the previous section.

Oracle, Microsoft, and the others have similar GUIs that allow the administrator to access their systems.

Basic SQL Query

When we need to extract data from the database, it's said that we are "querying" the database. As databases are repositories for data, the ability to extract or query data is among the most important functions. As a hacker, we want to extract data, so the query is critical to getting the goods.

The basic structure of the query looks like this:

SELECT <columns>

FROM <table>

WHERE <conditions>

This statement says "give me the data in the columns listed in the SELECT statement from the table that comes after the FROM keyword, but only give me the rows that meet the conditions that come after the WHERE keyword."

So, if we wanted to get first name, last name, username, and password from the staff table for employees with the last name of "Hillyer," we could construct a statement like this:

SELECT first_name, last_name, username, password

FROM staff

WHERE last_name = 'Hillyer";

As you can see in the screenshot below, we have extracted Mike Hillyer's record from the staff table with his username and password (the password is actually a hash of his password).

UNION Statements

It is possible to stack SQL queries in what is known as UNION statement. A UNION statement adds two SQL queries together. They both must query the same columns. UNION statements are a key element in some SQL injection attacks because their error messages can be used to enumerate the column names in the table.

A UNION statement looks like this;

SELECT <col 1>, <col 2>, <col 3>

FROM <table 1>

WHERE <condition>

UNION