Block Intruders with GreenSQL

Databases are mission-critical for most companies. Many corporate database systems store information about customers and employees, not things you would want to see escape into the wild. Unfortunately, this happens all the time, and even to large companies, who have a large technical team and a sophisticated IT infrastructure. Small businesses with few employees are often more vulnerable because, in addition to the expensive computer pool, they also lack the necessary expertise.

Small and medium-sized companies are often home to servers that run web applications and database management systems in parallel. An error in the web application can compromise the server or allow unauthorized access to the database through SQL injection, especially if the company lacks the staff to identify vulnerabilities and remedy them in a timely manner. Although SQL proxies such GreenSQL offer no protection against hostile server takeovers, they can effectively prevent SQL injection.

GreenSQL has been under development since 2009 by GreenSQL LTD in Israel. Until version 1.3, GreenSQL was released under an open source license. Version 2 was released in 2012 under a proprietary license with a free Express version, which provides good base protection. The firewall solution is implemented in C++. Using the example of a Linux server with the free Koha library system, I will take a closer look at the GreenSQL Security variant. The Security version offers additional alerting and reporting functions (compared with Express), as well as caching. You can test it for two weeks free of charge.

GreenSQL sits between the application and the database management system and acts as a reverse proxy or database firewall. The Security version runs on Linux and Windows and can protect PostgreSQL, MariaDB, MySQL, and Microsoft SQL Server database management systems.

In firewall mode, only authorized queries are passed in to the database; GreenSQL responds to unauthorized queries with an empty result set. The commercial Activity Monitoring and Data Masking versions also monitor who has sent which queries to the database, or they mask certain data, such as employees or customers. In general, potential attackers will not notice that they are dealing with a proxy instead of the DBMS.

Commissioning

Ideally, you should have three computers: a web server, a dedicated server for GreenSQL, and a database server, although it is also possible to run all of these applications on a single computer.

To install GreenSQL, download the application, then (on Linux) run chmod 755 greensql.bin to make it executable and launch the installer by typing ./greensql.bin . The installer will create a greensql group and user, move all the files to /opt , create a startup script, and launch GreenSQL.

The administrative interface is then accessible in the web browser via https://Server:5000/ ; it prompts you for a product key. You need to request the key after the (mandatory) registration required for the download. If you did not download GreenSQL from the manufacturer’s site, you can catch up with this step anytime. Then you are prompted to change the admin password before being taken to the dashboard (Figure 1).

Figure 1: The dashboard provides a comprehensive overview of GreenSQL.

Start by setting up a connection to the database system before you activate GreenSQL as a proxy. This is done with the Databases menu item. Before the parameters are stored, you can press the Check Connection button to check whether the database is accessible.

In the next step, set up a proxy through which all future communication with the database will be routed. The proxy sits between the application and DBMS and checks each request before forwarding the request to the database or discarding it. The proxy must listen on a port that is different from the database port. If you already have many web applications in place before you deploy GreenSQL, it makes sense to use the database port for the proxy and instead assign a different port to the DBMS.

The database, in which you will later store queries and other data for the firewall rules, does not need to be created manually; GreenSQL will normally create it automatically. If you prefer to assign your own name from the beginning, you can, of course, do so.

Once you have connected with the database and created the proxy, the database applications might require some minor adjustments; for example, if the proxy uses a port that is different from the port used by the database. You just need to change the DBMS port and server in the configuration files for each application.

Be careful with the popular localhost on Linux systems if the proxy and the application are running on a single server. Unix sockets are often used for localhost, which GreenSQL does not take into account. In this case, the queries fly under the GreenSQL radar and are not checked. But if you replace localhost with 127.0.0.1, everything should work as expected.

Back to School

While the permitted queries are known in principle for in-house developed database applications, it is far more difficult to gain an overview for off-the-peg applications. A search for keywords would be possible, but in practice, queries are often spread over many files and assembled dynamically.

To compile the widest possible list of authorized or unauthorized queries quickly, GreenSQL can operate in learning mode in a secure environment. Below Database Security | Policy , create a new policy of the Learning Mode type and link it with the database at the same time if so desired.

The acquired database queries are stored in a query group during the learning process; you can create a group using a template. A query group can contain queries for all proxies and databases, or it can be limited to selected instances.

Source IP , Database User , and Application help stem the flood of requests. The Schedule feature lets admins limit the time in which a rule is valid. One option would be to allow database access only during business hours.

Next, enter a time period within which you want to teach GreenSQL queries. When your users start working with the protected application, the query group associated with the policy is populated with a basic set of permitted or prohibited requests that you can adjust later (Figure 2).

Figure 2: In an initial learning phase, GreenSQL is introduced to permitted or prohibited queries.

Guardian of the Database

To activate GreenSQL, you need policies of the type Database Firewall or Risk-based IDS/IPS . The policies are processed top-down, as with other firewall systems. Once a match is found, it is accepted, and the compare process is canceled. That means if you just want to prohibit a few requests, but otherwise accept everything, you should put the prohibition rule at the beginning. But if you prefer to deny everything and allow only a little, do this the other way around.

The firewall allows or blocks access to the database on the basis of query- or table-based rules. The queries can be collected, for example, while GreenSQL is running in learning mode, but they can also be entered manually or copied from the log.

You can also add firewall and IDS/IPS policies for additional restrictions. If a server serves a number of computers, you can exclude IPs or entire IP ranges. The same goes for users, applications, and times in which a rule applies. The entries for this are made in Database Security | Objects .

While there is not much to consider for permitted queries, you can decide for yourself how GreenSQL should respond to requests you want to block. Optionally, an empty result set is returned, the connection to the database is closed, or an SQL error is issued. An empty result set is least likely to confuse normal users (Figure 3).

Figure 3: In case of unauthorized queries, GreenSQL returns an empty result set, closes the connection to the database, or returns an error message.

GreenSQL as an IDS

GreenSQL can operate in combination with a risk profile as an IDS or IPS. The queries themselves do not determine whether a request is allowed but, instead, result in an action taken. Depending on the configuration, the IDS blocks the database connection and informs an administrator of the alleged break-in attempt.

What IDS and IPS systems have in common is that they can detect SQL injections. To do so, they reference a database of known attack patterns compiled by GreenSQL. Additionally, all suspicious inquiries are considered to be SQL injection. In the case of a false positive, you assign the query to the Injection Patterns query group or some other group that GreenSQL accepts as legitimate requests.

Granular risk profiles are also created in Database Security . These profiles are not limited to individual queries but can refer to database operations that relate to servers, security, or databases. The profiles include querying active processes or logs, changing passwords and escalating privileges, outputting databases and tables, or retrieving information on database schemas (Figure 4).

Figure 4: Risk profiles give administrators a granular approach to defining which database operations are allowed and which are not.

Logging and Reports

GreenSQL can notify the administrator for various events. Examples include changes to the system settings, intrusion attempts, or violations of firewall rules. To enable this functionality, you need to set up contact lists and SMTP servers in the Alerts menu. Notifications themselves are just as easy to configure as the rest of the firewall solution. In addition to a descriptive name, you need to specify the alert type and an interval. System , for example, notifies you of newly added or changed policies, Traffic of blocked queries, and Intrusion of all queries suspected of being break-in attempts.

Because many mail messages can accrue very quickly, GreenSQL allows you to group messages for sending, and this is where the interval enters the scene: Instead of sending several messages in short succession, this setting sends a group of messages every few minutes or even hours. If you check the Verbose box, GreenSQL sends you both the alert message and the SQL query that triggered it.

Thanks to extensive logging capabilities, you can quickly find the root cause of any problem. In the case of the Koha library system, GreenSQL had difficulties with the

set autocommit = 1

statement that led to Koha quitting the service with an error message. The solution was to evaluate the log and teach GreenSQL the appropriate query. There are two approaches to manual teaching. Either add the query using the Database Security | Query Groups | Learned Patterns | Create New item, or click on the relevant entry in the log and assign the pattern to a query group.

GreenSQL doesn’t just log traffic and intrusion attempts. The software also outputs information about efficiency statistics, system events, or the most popular queries. In addition to its own logfiles, it can, if needed, send selected messages to the syslog daemon; this lets you evaluate them in a monitoring system.

A sophisticated reporting function lets you conveniently process information from the logs. Reports can be used, for example, to document developments or to support decisions. GreenSQL comes with many types of reports in place so that administrators can immediately output which queries take the longest to process, which are requested or blocked most frequently, and from which IP addresses the most intrusion attempts originated. The reports can be exported into PDF or Excel format.

Once GreenSQL is set up, you will want to back up the configuration. You can quickly create a backup with the corresponding menu items in System | Backup & Restore . The backup password is requested only once. It is useful to try a restore immediately after the backup, in this case to make sure no typos have crept into the password. This simple precaution avoids nasty surprises in an emergency.

Conclusions

SQL and code injection are some of the most critical vulnerabilities in web applications (see the OWASP Top Ten 2013 list). GreenSQL gives administrators a tool that is especially useful for protecting mission-critical web applications that (still) have not been intensely scrutinized for vulnerabilities.

GreenSQL is available in a number of variants. Even the Express version provides good basic protection, which is sufficient to protect a company blog or moderately busy shop. The commercial versions offer additional features, such as alerts and reports, keep an eye on who connects to a database, and conceal privileged information.

The GreenSQL security solution is easy to install and put into operation within a short time. Extensive documentation helps if something is unclear. The ability to learn queries automatically saves administrators much work and prevents them from forgetting important requests later on. This minimizes trouble and complaints from users at very early stages. The developers have ensured from the outset that only users from the associated network can connect to the GreenSQL server. It is possible to restrict GreenSQL, without further firewall rules, to a single IP address or localhost. Certificates help to verify the authenticity of the server.

Although GreenSQL cannot protect against all the uncertainties of the World Wide Web, it is a useful security solution that can put an end to attacks at the database level and meaningfully supplement other security measures in the enterprise.