This is a read-only archive. Find the latest Linux articles, documentation, and answers at the new Linux.com!

Linux.com

Feature: Security

Protecting your MySQL database from SQL injection attacks with GreenSQL

By Ben Martin on August 25, 2008 (4:00:00 PM)

Share    Print    Comments   

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.

Ben Martin has been working on filesystems for more than 10 years. He completed his Ph.D. and now offers consulting services focused on libferris, filesystems, and search solutions.

Share    Print    Comments   

Comments

on Protecting your MySQL database from SQL injection attacks with GreenSQL

Note: Comments are owned by the poster. We are not responsible for their content.

I like the ideea but I got a few questions

Posted by: Anonymous [ip: 79.114.180.69] on August 25, 2008 06:14 PM
1. What's the overhead(yes, I'm talking performance) of GreenSQL?
2. Nowadays, people use an abstraction layer either way(PDO, ActiveRecord, etc), why should I use this?
3. Is GreenSQL being used in production (a somewhat larger site)?

#

Re: I like the ideea but I got a few questions

Posted by: Anonymous [ip: 204.213.74.28] on August 26, 2008 05:55 AM
Am I missing something. Why not just use stored procedures (MySQL 5.0 +) and be done with it.

#

Re(1): I like the ideea but I got a few questions

Posted by: Anonymous [ip: 208.46.0.26] on August 26, 2008 01:14 PM
because stored procs still execute as query strings on the server. This forwards all attempts at any kind of query through an intermediate, nothing like stored procs...

#

Protecting your MySQL database from SQL injection attacks with GreenSQL

Posted by: Anonymous [ip: 90.202.61.113] on August 25, 2008 07:21 PM
better yet, encode the bloody data before you shove it in there

#

Fedora, openSUSE, or Ubuntu

Posted by: Anonymous [ip: 76.6.236.210] on August 26, 2008 12:21 AM
Packages are also available for Debian 3.1 (sarge) and 4 (etch) on the GreenSQL download page.

#

What a waste of time

Posted by: Anonymous [ip: 118.93.92.200] on August 26, 2008 04:32 AM
Do you honestly think that anybody who doesn't know how to use simple, foolproof SQL-quoting functions is really going to be able to figure out how to correctly set up a package like this?

Lawrence D'Oliveiro

#

Perhaps a good tool for ISPs but I wouldn't use it

Posted by: Anonymous [ip: 123.200.129.163] on August 26, 2008 05:00 AM
I can see the merit in such a tool, especially for ISPs who have users of wide ranging expertise in PHP, but I would never use it personally, unless the performance hit was basically negligible.

#

Re: Perhaps a good tool for ISPs but I wouldn't use it

Posted by: Anonymous [ip: 208.46.0.26] on August 26, 2008 01:19 PM
BINGO, looks like it could be a nice little extra protection for some ISPs who don't do the actual coding but want to offer a little protection from SQL injection for their customers...

#

Protecting your MySQL database from SQL injection attacks with GreenSQL

Posted by: Anonymous [ip: 76.19.119.197] on August 26, 2008 05:41 AM
Why don't you try to actually learn to secure your code instead of being a lazy (or completely unskilled) administrator? Surely mysql_real_escape_string() isn't too hard to incorporate?

#

Protecting your MySQL database from SQL injection attacks with GreenSQL

Posted by: Anonymous [ip: 192.168.118.196] on August 26, 2008 06:40 AM
Great idea! But it looks like adding complexity to the existing bloated set up! Why not make it a policy that every input which lands into the database should be validated well!

#

Re: Protecting your MySQL database from SQL injection attacks with GreenSQL

Posted by: Anonymous [ip: 76.9.35.223] on August 26, 2008 07:57 AM
Why does it appear that you are originating inside their network?

#

Protecting your MySQL database from SQL injection attacks with GreenSQL

Posted by: Anonymous [ip: 76.9.35.223] on August 26, 2008 07:54 AM
"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."

Why not just have the database user not have delete privileges?

#

Protecting your MySQL database from SQL injection attacks with GreenSQL

Posted by: Anonymous [ip: 121.45.130.243] on August 26, 2008 10:04 AM
GOSH! NOT NORMAL!

#

Protecting your MySQL database from SQL injection attacks with GreenSQL

Posted by: Anonymous [ip: 206.248.124.246] on August 26, 2008 01:10 PM
Ben, mind if I borrow this article for my blog at http://databases2go.bloggin.info? If you do agree I already have enough for the author part but any site you want listed or just this blog?

#

Protecting your MySQL database from SQL injection attacks with GreenSQL

Posted by: Anonymous [ip: 89.229.6.117] on August 26, 2008 02:53 PM
good reason for providers? i think , mod_security for apache is better. have the same function

#

Protecting your MySQL database from SQL injection attacks with GreenSQL

Posted by: Anonymous [ip: 80.216.225.141] on August 26, 2008 04:29 PM
Hey, let's fix a major design flaw in PHP's main database interface by adding a proxy layer, yay! \o/
Great idea!

Stop using PHP if you worry about performance, maintainability or security. Languages that recommend using simple string concatenation to make SQL queries are not very good languages. The PHP team realized this later on, making their mysqli version with prepared statements, which lets you programmatically bind variables instead (but it's oh-so-confusing.)

By the way, this CAPTCHA could be beaten by any general purpose OCR out there.

#

Protecting your MySQL database from SQL injection attacks with GreenSQL

Posted by: Anonymous [ip: 98.207.197.227] on August 26, 2008 05:39 PM
Probably useful to some but if you have control of your app code, you better fix/secure your code. You would not want an extra layer that increase both overhead and latency.

Son Nguyen

#

you are my man Yuli

Posted by: Anonymous [ip: 190.17.61.62] on August 26, 2008 06:18 PM
Yuli (developer of GreenSql) you are my Security Ninja !!

greetings from Argentina !


juan

#

Protecting your MySQL database from SQL injection attacks with GreenSQL

Posted by: Anonymous [ip: 192.168.13.180] on August 28, 2008 12:52 AM
Well, The SQL injection should be prevented inside the Data Access Layer of your application. Developers should escape all the user input data before sending it to database. Solution like GreenSQL can be used as a quick fix for application which is developed without security in mind until developers fix the actual issue.
Behrang
http://www.beyondng.com

#

This story has been archived. Comments can no longer be posted.



 
Tableless layout Validate XHTML 1.0 Strict Validate CSS Powered by Xaraya