Author: Ben Martin
SQL injection attacks can allow hackers to execute arbitrary SQL commands on your database through your Web site. To avoid these attacks, every piece of data supplied by a user on a Web form, through HTTP Post or CGI parameters, or other means, must be validated to not contain information that is not expected. GreenSQL is a firewall for SQL — it sits between your Web site and MySQL database and decides which SQL statements should and should not be executed. At least that’s the idea — in execution, I found some open doors.
GreenSQL is designed to be used as a proxy for a MySQL database. Instead of connecting directly to your MySQL database, you Web site connects to GreenSQL. GreenSQL forwards legitimate SQL to the MySQL database and returns the results. If GreenSQL detects SQL that is not whitelisted and that includes nasty or suspicious SQL, it will block that SQL and return the empty result set without contacting the MySQL database. For an idea of the SQL injection attacks that GreenSQL blocks, see the online demo page.
GreenSQL is not in the distribution repositories for Fedora, openSUSE, or Ubuntu. It is available as a 1-Click install for opneSUSE 10.3, and for Fedora 8. The GreenSQL download page includes packages for Fedora 7, openSUSE 10.2, FreeBSD, and Ubuntu Feisty Fawn and older. In this article I’ll install from source on a 64-bit Fedora 9 machine using version 0.8.4 of greensql-fw.
Installation does not use autotools, and you have to set up things like configuration files, system users, MySQL configuration, log file setup, and /etc/init.d files manually. The procedure is well documented in install.txt. Better yet, by executing a few of the shell scripts in the scripts directory, you can complete much of the setup. To compile the application, simply execute make in the top directory as shown below. On Fedora 9 I got the following error soon after compilation started, which was due to libevent-devel not being installed.
$ tar xzf /.../greensql-fw-0.8.4.tar.gz $ cd greensql-fw-0.8.4/ $ make ... connection.hpp:29: error: field 'proxy_event' has incomplete type connection.hpp:30: error: field 'client_event' has incomplete type
Once I had libevent-devel installed, I found that I needed to modify /usr/include/event.h to include sys/types.h in order to compile event code.
vi /usr/include/event.h ... #include <sys/time.h> #include <sys/types.h> #include <stdint.h> #include <stdarg.h>
At this stage, make -k failed on a few files which were calling string functions such as strcasecmp without having included the string.h header file. Depending on which version of gcc you are using the below issues might not be present when you compile GreenSQL.
$ cd src $ vi mysql/mysql_con.cpp ... // License: GPL v2 (http://www.gnu.org/licenses/gpl.html) // #include <string.h> #include "mysql_con.hpp" ... $ vi config.hpp ... #ifndef GREEN_SQL_CONFIG_HPP #define GREEN_SQL_CONFIG_HPP #include <string.h> ... $ vi ../src/parser/expression.hpp ... #ifndef _SQL_EXPRESSION_HPP_ #define _SQL_EXPRESSION_HPP_ #include <string.h> ...
If you are building GreenSQL on a 64-bit distribution you will also have to modify the Makefile slightly so that the build checks lib64 instead of just the lib directory, as shown below:
$ vi src/Makefile ... LIBS:=-L/usr/local/lib -L/usr/local/lib/mysql -L/usr/lib64/mysql -lmysqlclient -levent -lpcre greensql-fw: $(OBJS) ... $ make
Shown below are the commands to finish installation once the daemon is compiled. The package’s install.txt file describes manual installation, which uses the same commands that the scripts do. Using the scripts is likely to be the better option because the installation process can be improved (the contents of the scripts) while the procedure remains the same. The installation instructions recommend setting up the MySQL database before the /etc/greensql directory, but if you do that the MySQL script invocation fails to find the configuration file and you must make the changes to /etc/greensql/greensql.conf manually. This is a bit of a chicken and egg problem but the only major change you must make to the greensql.conf file is to get the MySQL database parameters.
greensql-fw-0.8.4]# cd ./scripts/ # ./setup_user.sh done... # ./greensql-create-db.sh --------------------------------------------- The following settings will be used: MySQL admin user: [root] MySQL admin password: [] MySQL server address: [127.0.0.1] GreenSQL configuration DB name: [greendb] DB user to create: [green] Password to set: [pwd] Do you want to change anything? [y/N] y MySQL admin user [root]: MySQL admin password []: XXxxXXxxXXxx-FIXME MySQL server address (you can use ip:port string) [127.0.0.1]: GreenSQL config db name [greendb]: GreenSQL DB user name [green]: greendb GreenSQL DB user password [pwd]: greendbpass --------------------------------------------- The following settings will be used: Do you want to change anything? [y/N] Creating MySQL database... Adding MySQL user... Creating MySQL tables... GreenSQL configuration file is not writable!!! Check that [database] section contains the following settings in /etc/greensql/greensql.conf [database] dbhost=127.0.0.1 dbname=greendb dbuser=greendb dbpass=greendbpass # dbport=3306 ... # ./setup_conf.sh done... # ./setup_log.sh done... # ./setup_binary.sh done... # vi /etc/greensql/greensql.conf ... [database] dbhost=127.0.0.1 dbname=greendb dbuser=greendb dbpass=greendbpass ... # chkconfig --add greensql service greensql does not support chkconfig # /etc/init.d/greensql start
For testing purposes I created the test database and gave the user ben free access to it with the commands shown below:
# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or g. mysql> GRANT ALL ON test.* TO ben@"%"; mysql> FLUSH PRIVILEGES;
GreenSQL runs on port 3305 by default, which is one less than the default MySQL port of 3306. If you use the mysql console client and connect to the GreenSQL 3305 port you will not be able to create a new table, even though, as shown in the command below, when you connect directly to MySQL on port 3306 you are able to create a new table.
$ mysql --verbose -h 127.0.0.1 -P 3305 test mysql> create table foo ( id int ); -------------- create table foo ( id int ) -------------- Query OK, 0 rows affected (0.01 sec) mysql> insert into foo values ( 55 ); -------------- insert into foo values ( 55 ) -------------- ERROR 1146 (42S02): Table 'test.foo' doesn't exist $ mysql --verbose -h 127.0.0.1 -P 3306 test Welcome to the MySQL monitor. Commands end with ; or g. mysql> create table foo ( id int ); -------------- create table foo ( id int ) -------------- Query OK, 0 rows affected (0.01 sec) mysql> insert into foo values ( 55 ); -------------- insert into foo values ( 55 ) -------------- Query OK, 1 row affected (0.00 sec) mysql> insert into foo values ( 131 ); -------------- insert into foo values ( 131 ) -------------- Query OK, 1 row affected (0.00 sec) mysql> select * from foo; -------------- select * from foo -------------- +------+ | id | +------+ | 55 | | 131 | +------+ 2 rows in set (0.00 sec)
With the default GreenSQL configuration you cannot drop tables through the GreenSQL firewall. This is just as well, because table structure is not likely to change often and most likely will not change at all from the Web site interface.
$ mysql --verbose -h 127.0.0.1 -P 3305 test Welcome to the MySQL monitor. Commands end with ; or g. mysql> select * from foo; -------------- select * from foo -------------- +------+ | id | +------+ | 55 | | 131 | +------+ 2 rows in set (0.00 sec) mysql> drop table foo; -------------- drop table foo -------------- Query OK, 0 rows affected (0.00 sec) mysql> select * from foo; -------------- select * from foo -------------- +------+ | id | +------+ | 55 | | 131 | +------+ 2 rows in set (0.01 sec)
The injection tests did not seem to work as well as I had hoped. The first test was to delete tuples based on a condition that was always true. This wipes out all the data in a table and leave an empty table schema. By default this query worked through the firewall.
$ mysql --verbose -h 127.0.0.1 -P 3305 test mysql> delete from foo where 1=1; -------------- delete from foo where 1=1 -------------- Query OK, 2 rows affected (0.00 sec) mysql> select * from foo; -------------- select * from foo -------------- Empty set (0.00 sec)
For the above SQL delete command, the /var/log/greensql.log file contained the following information:
SQL_DEBUG: QUERY command[]: delete from foo where 1=1 SQL_DEBUG: AFTER NORM : delete from foo where ?=? SQL_DEBUG: RISK : 0
The /etc/greensql/greensql.conf file allows you to set how risky you think certain things are. For example, you can assign a weight
of 10 to the use of the union keyword or with direct variable comparison in a query (something like 1=2). The variables include block_level = 30 so any query with a risk above 30 will not be forwarded to the MySQL server. In an attempt to make GreenSQL flag the above query I increased risk_var_cmp_var and risk_always_true up to 150 from their default of 30. Unfortunately, the query remained seen as a zero-risk query.
Because the above SQL might be valid, I based the query shown in the below logs on the template described in the MySQL SQL Injection Web page of the GreenSQL Web site. This SQL injection relies on the Web site inserting data provided from the user into an SQL query string and submitting it to the database. It too was allowed through.
SQL_DEBUG: QUERY command[]: delete from foo where id=181 or 1=1 SQL_DEBUG: AFTER NORM : delete from foo where id=? or ?=? SQL_DEBUG: RISK : 0 SQL_DEBUG: QUERY command[]: delete from s where comment = 'whatever' or '1'='1' SQL_DEBUG: AFTER NORM : delete from s where comment = ? or ?=? SQL_DEBUG: RISK : 0
After much strace(1)ing and other prodding trying to work out why GreenSQL was flagging the above nasty query as zero risk it dawned on me to try a SELECT query. This was the key to getting GreenSQL to work as expected, blocking this nasty query, as the below portion of the log file shows.
SQL_DEBUG: QUERY command[]: select * from folks where name='sam' or '1'='1' SQL_DEBUG: AFTER NORM : select * from folks where name=? or ?=? DEBUG: Query has 'or' token DEBUG: Variable comparison only SQL_DEBUG: RISK : 35
Given that SQL injection in a select statement can allow a user to login to a Web site without a password, having GreenSQL inspecting your selects is a good thing. In future releases I hope the developers extend GreenSQL’s protection to delete statements, as an injection in one of those can clear an entire table.
Categories:
- Security
- Internet & WWW
- Databases
