The Universe of Discord

Only a Dream [ 2009-11-28 05:33:49 UTC ]

I actually had this dream. Mostly.

Hands-On Database Security [ 2009-11-07 18:41:34 UTC ]

I wrote these notes for a student-led presentation I gave to UAF's Pacific Rim CCDC team. This information should be doubly useful should UAF host its own capture the flag event, but hopefully more on that later.

Overview

These notes list some important security issues that people working with databases should be aware of. Hands-on exercises and examples are provided because playing with real code is much more fun and educational than merely discussing these vulnerabilities.

What do databases do?

Relational databases are everywhere. They keep track of your health records, your online sock puppet purchasing history, your favorite government agency's clandestine domestic spying program, and your state or province's electoral process even if you don't use an electronic voting machine. Despite or perhaps because of all these uses, databases are often exploited by malicious parties in an increasingly lucrative black market for personal information, trade secrets, and government intelligence.

Relational databases pretty much all use some form or another of SQL. You can learn more about SQL and play with an SQL Sandbox if you haven't used it before or don't remember it very well.

Database Connections

The default configurations of many databases will pass queries unencrypted over the network. To demonstrate with a default installation of MySQL 5.1 on Debian GNU/Linux,

 
sudo tshark -Tfields -e mysql.user -e mysql.passwd -i lo -R mysql.user

 
mysql -h 127.0.0.1 -u root -p

after entering the password into the mysql client produces the output

 
Running as user "root" and group "root". This could be dangerous.
Capturing on lo
root    \x7fr\xe9ArY?7w4\xa5\xb2\x8e>\xa3\xff\x03j\x99\xc3

which thankfully is hashed, but the username is sent unencrypted over the network. The queries themselves are not encrypted by default, as this next example demonstrates.

For this session,

 
mysql -h 127.0.0.1 -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 52
Server version: 5.1.37-2 (Debian)
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> use ocelot;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
 
Database changed
mysql> insert into friends (id, name, description) values (1337, 'turtle', 'c/,,\\');
Query OK, 1 row affected (0.12 sec)
 
mysql> select * from friends;
+------+--------+-------------+
| id   | name   | description |
+------+--------+-------------+
|    0 | cow    | it says moo | 
| 1337 | turtle | c/,,\       | 
+------+--------+-------------+
2 rows in set (0.02 sec)
 
mysql>

Wireshark is able to capture

 
sudo tshark -Tfields -e mysql.query -i lo -R 'mysql.query'
$ sudo tshark -Tfields -e mysql.query -i lo -R 'mysql.query'
Running as user "root" and group "root". This could be dangerous.
Capturing on lo
select @@version_comment limit 1
SELECT DATABASE()
show databases
show tables
insert into friends (id, name, description) values (1337, 'turtle', 'c/,,\\\\')
select * from friends

Admittedly, for an attacker to capture this information, they would need to control a system between the database and the client. However, if a database is storing confidential information such as credit card numbers (which should only be stored by the credit card companies but I digress), employment information, or health records, basing security of the databases upon the security of the network sees unwise when it's not too much work to configure the database use SSL in most cases.

Here's how to configure MySQL with SSL and since version 8, PostgreSQL enables SSL by default. You can double-check that your postgres database is using ssl by looking for "ssl = true" in "/etc/postgresql/$PG_VERSION/main/postgresql.conf" (or a similar place depending on how postgres was installed).

If your database and the applications that use it are on the same system, consider disabling networking altogether and using a UNIX socket instead.

For more information about wireshark filters, consult the manual.

Unsafe Functions

Some database servers, notably Microsoft SQL Server when poorly configured, expose interfaces to unsafe system calls from SQL in order to write more powerful stored procedures. Functions such as "xp_cmdshell" are particularly infamous.

MySQL also allows select statements to load data into and out of files on disk. Other databases come with their own security caveats that programmers should be aware of.

Roles

For databases that support it, setting up roles with the lowest permissions necessary to get the job done is important so that a compromise of one system limits the damage or disclosure of the intrusion.

To list the roles and users on a system,

In a PostgreSQL shell:
- \du
- select * from pg_roles;
- select * from pg_user;
In a MySQL shell:
- show grants;
- select * from mysql.user;
- select * from mysql.tables_priv;

To grant or revoke roles, consult the MySQL and PostgreSQL documentation. There is some good information at this site too.

SQL Injection

malicious user attacks but is denied in a cliché full-screen error message

Suppose a website uses usernames and passwords to do authentication. A hapless web developer might write a PHP script that queries a database like this:

 
    $username = $_POST['username'];
    $password = $_POST['password'];
    $result = sqlite_query($db, "
        select * from users where
            username = '$username' and password = '$password'
    ");
    if ($row = sqlite_fetch_array($result)) {
        echo "Welcome " . $row[0] . "!";
    }
    else {
        echo "Authentication failure";
    }

For a username of "caligula" and a password of "tyranny", the query would be

 
select * from users where username = 'caligula' and password = 'tyranny'

But if a malicious user enters a username of "caligula' --" and doesn't enter a password, the query becomes

 
select * from users where username = 'caligula' --' and password = ''

which for databases where "--" is configured as a comment will successfully authenticate as the user "caligula" without the malicious user needing to know the password.

Worse still, the password could be retreived by submitting a username of

 
' union select password from users where username='caligula' --

which would perform an SQL union on the database, allowing an attacker to see any tables and columns that the configured database user can see.

 
select * from users where
    username = '' union select password from users where username='caligula' --'
    and password = ''

The SQL union operator combines two or more select statements with the same number of columns into one row-set. If the attacker doesn't know how many columns the query had originally, they can just use database constants such as numbers instead of column names in their union until the query succeeds. By using different numbers, they can even see where in the output the columns that they want to see will show up at.

The attacker might not know the names of the columns or table names, but this information can be obtained through the database schema which is stored in the database itself. In both MySQL and PostgreSQL, this information is contained in the information_schema database. Of particular interest are the "tables" and "columns" tables. In SQLite this information is available in the sqlite_master table.

Re-visiting the previous example, setting the username to

 
' union select sql from sqlite_master --

gives back the result

 
Welcome CREATE TABLE users (username text, password text)!

Protecting Against SQL Injection

There are several approaches for protecting against SQL injection. Escaping the user input manually is one option. From the authentication example from earlier in the document:

 
$username = sqlite_escape_string($_POST['username']);
$password = sqlite_escape_string($_POST['password']);

A cleaner way to protect against SQL injection is by using placeholders, which bind parameters to a query and handle all the string escaping. The authentication example can be made to use placeholders:


$sth = $db->prepare("select username from users
    where username = '?' and password = '?'
");
$sth->execute($_POST["username"], $_POST["password"])
  or die("Error: " . sqlite_error_string(sqlite_last_error($db)));
       
if ($row = $sth->fetch()) {
    echo "Welcome " . $row[0] . "!";
}
else {
    echo "Authentication failure";
}

For more information about placeholders and prepared statements, see

Object-relational mappers provide a more elegant way to protect against SQL injection, since the actual queries are abstracted behind objects instead of written directly.

ORM libraries in PHP
Comparison of perl ORM modules
ORM solutions for python
Ruby has ActiveRecord, DataMapper, and Sequel, to name a few

Exercises

Try to exploit these vulnerable example scripts. There is a single md5 sum hiding somewhere in each SQLite database. You shouldn't need to look at the source or the database for any of these to exploit them, but the source especially comes in handy if you are stuck. There are also writeups provided if you like spoilers.

auth [index.php] [db.sqlite] [writeup.txt]
This is the example from the previous section. There is a hash hidden somewhere too.
quotes [index.pl] [db.sqlite3] [writeup.txt]
This script shows quotes. The hash is in a quote that isn't listed.
articles [index.pl] [db.sqlite3] [writeup.txt]
This script shows articles. The hash is in a secret table with a secret column name.

Dijkstra Folk Wisdom [ 2009-10-25 21:05:30 UTC ]

[svg]

Happstack on Dreamhost Notes [ 2009-10-25 09:59:47 UTC ]

I had been meaning to give happstack a closer look for far too long and decided the best way to force myself to get around to putting together something interesting would be to get some cheap webhosting.

I've installed the ghc and cabal toolchain in non-root user accounts before, so I figured anything that offers shell accounts would be sufficient for my purposes. On IRC, pkrumins suggested Dreamhost, which he uses for catonmat.net. Dreamhost has shell accounts and looked cheap enough, so I registered substack.net under the cheapest "happy hosting" account.

Once I got a shell, I downloaded the binary build of ghc for x86_64 into my home directory, extracted it, and configured it


./configure --prefix=$HOME/prefix && make install

Unfortunately, the system strip command used by the installer was incompatible with the binaries. Downloading and installing the latest version of GNU binutils into my home directory with the strip in my $PATH, I tried to install ghc again with exactly the same errors as before.

Frustratingly, ghc's installer hard-codes "/usr/bin/strip" instead of checking $PATH for it. I replaced every hard-coded /usr/bin/strip occurence to the location of my own version of strip from binutils with this one-liner:


perl -pi -e's{/usr/bin/strip}{$ENV{HOME}/prefix/bin/strip}g' \
    `grep '/usr/bin/strip' -lR .`

`make install` succeeded after this.

I downloaded and extracted cabal-install, ran bootstrap.sh, and had cabal up and running. Unfortunately, while installing many packages with cabal, Dreamhost's memory checker would kill the process for using too much memory. Whenever this happened, I would go into the directory of the troublesome package in ~/.cabal/packages/hackage.haskell.org/, then I would


tar xzf $PKG.tar.gz
cd $PKG
ghc --make Setup.hs && ./Setup configure --user && ./Setup build && ./Setup install

If the compile was killed, I just typed ./Setup build again until it finally finished all the way through. In this manner I was able to get most of the modules necessary to run happstack installed.

To install packages which depended on libraries and include files, I modified ~/.cabal/config by uncommenting and setting:


extra-include-dirs: $PREFIX/include
extra-lib-dirs: $PREFIX/lib

Not all modules will actually respect these directives, however. I had to hack away at some cabal files to get all the modules I needed running.

FileManip was super annoying to get installed. I finally went into its source and replaced "Control.Exception" with "Control.OldException", which did the trick.

Running happstack applications in the usual way where the service provides its own http server would only work on a Dreamhost Private Server account, which costs more money. Dreamhost does support CGI and FastCGI on the cheapest kind of account, however. I recommend using the plain CGI interface that Happstack.Server.FastCGI provides through Network.FastCGI, since errors show up properly in error.log and process management is much simpler.

While non-trivial to get running, this web experiment proves that it is at the very least possible to run Happstack applications on cheap hosting providers such as Dreamhost with little more than a shell account and CGI support. The source to substack.net is available on github, if you'd like to poke around!

newer