Maxim Kazmin, 123RF

Maxim Kazmin, 123RF

Blocking SQL injections with GreenSQL

Safe House

Article from ADMIN 15/2013
By
SQL injection can strike at any moment. GreenSQL is an effective remedy that sits between the database and application and filters out suspicious queries.

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 [1] has been under development since 2009 by GreenSQL LTD in Israel. Until version 1.3, GreenSQL was released under an open source license [2]. 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.

Buy ADMIN Magazine

SINGLE ISSUES
 
SUBSCRIPTIONS
 
TABLET & SMARTPHONE APPS
Get it on Google Play

US / Canada

Get it on Google Play

UK / Australia

Related content

  • Stopping SQL Injection

    SQL injection can strike at any moment. GreenSQL is an effective remedy that sits between the database and application and filters out suspicious queries.

comments powered by Disqus