All too often, as statistics and daily headlines show, badly written applications continue to expose an organizations sensitive data to malicious attackers. These vulnerabilities are continuously being exploited to steal personal, confidential information such as login credentials, credit card numbers and social security numbers.

The most common web application attack is SQL Injection, which according to security reports like the Verizon Data Breach Investigation Report, OWASP lists and others, is the result of poorly coded applications. Without going into too much detail, it is true that SQL injection can be prevented by properly coding an application. However we cannot guarantee that all applications are coded correctly to prevent an SQL Injection attack and even the best security oriented developer can make mistakes.

Plus, there is continuous pressure to get new applications to market quickly, which conflicts with taking the necessary time and effort to ensure applications are secure and free of vulnerabilities.

And it not just your code you need to be concerned about. There are plenty of web-frameworks out there, from rigid and complex frameworks to simple ones that carry a set of security issues, including SQL injection and other attack vulnerabilities. Or maybe you’re running other third party software which can have its own set of security vulnerabilities.

To overcome SQL Injection vulnerabilities, some would say just use prepared statements, but even this helpful API often seems to be a hurdle for developers under pressure to get their applications to market ASAP. And, when it comes to development pace, nothing is quicker and easier than to use than simple string concatenation and direct SQL statements. So, given time constraints and the push for getting applications to market immediately, many applications are ripe for SQL-injection style attacks, and the actual statistics show this.

So how can you overcome these challenges? Put in another layer of protection. One such effective approach to managing and reducing the risks associated with SQL-injection attacks is to introduce a query sanitizer at the database level which sorts out all good SQL (and let it run) from the bad SQL (which is rejected). This concept is referred to as an SQL firewall.

Installing the Firewall

The MySQL Enterprise Firewall plugin is bundled with the MySQL 5.6.24 (and later) Enterprise Server binaries, and is easily installed using the provided SQL file:

Firewall Installation

Shell

1

2

3

4

5

::on Windows

dos>mysql-uroot-pmysql<share\win_install_firewall.sql

# on UNIX/Linux

shell>mysql-uroot-pmysql<share/linux_install_firewall.sql

The MySQL Enterprise Firewall is delivered as a plugin that can be easily enabled and used. Technically its composed of three user-defined functions (UDFs) and an information schema plugin. Management of the in-memory Firewall rules and persistent settings is handled by a regular stored procedure. All of these pieces are installed and set up when you load the firewall by running the [win|linux]_install_firewall.sql file which is located in the under MySQLs directory in the share directory.

If you examine the the [win|linux]_install_firewall.sql file, you’ll see that much of the management is done using regular SQL (except for the UDFs), and thus you can modify it to your heart’s content if you feel you want to improve upon it or customize it in any way. For example, here’s the stored procedure used to manage the Firewall modes:

Configuring the Firewall

After successfully installing you need to “teach” the firewall which type of SQL queries you consider safe. The easiest way to do this is to set the firewall into a recording mode.

Let’s imagine that you’ve got a WordPress installation which you want to protect, and the application is using the ‘wpuser@localhost’ account in your supporting MySQL database instance.

You first register this account with the Firewall. You do this by calling the stored proceedure we created earlier:mysql>CALL sp_set_firewall_mode('wpuser@localhost','RECORDING');

Now you can go into wordpress and click on the links and perform acceptible operations – as opposed to things are hacker would try. The Firewall will record these SQL statements as templates which are somewhat similar to what prepared statements look like.

When you think you are done – you’ve covered all the test cases for normal usage – you then turn the Firewall into a PROTECTING mode and the firewall will now reject unwanted queries:mysql>CALL sp_set_firewall_mode('wpuser@localhost','PROTECTING');

The rules listed in the table can alternatively be created by using the normalize_statement() UDF to generate the statement digest. In MySQL all queries which pass through the parser are tokenized. Our MySQL Performance Schema already uses this feature to produce SQL digests. Our firewall takes those digests and compares them against an in-memory hash. Matching a query against a whitelist of course adds a little extra processing and our preliminary testing has shown under concurrent stress level loads only a 2-3% performance impact added by running the firewall. That’s hardly noticeable especially under normal loads. And for most a very small cost for improved security that is likely well worth it.

Firewall Status

All registered accounts and their corresponding operational mode are listed in the information_schema.mysql_firewall_users table:

Firewall_Users I_S Table

MySQL

1

2

3

4

5

6

7

8

mysql>SELECT*FROMinformation_schema.mysql_firewall_users;

+------------------+------------+

|USERHOST|MODE|

+------------------+------------+

|wpuser@localhost|PROTECTING|

|root@localhost|OFF|

+------------------+------------+

2rowsinset(0,01sec)

When a user account is under Firewall protection, failure to match the incoming query with a whitelisted digest will result in an error. For example:

Error Examples

MySQL

1

2

3

4

mysql>showtables;

ERROR1045(28000):StatementwasblockedbyFirewall

mysql>droptablewp_posts;

ERROR1045(28000):StatementwasblockedbyFirewall

Such failures can be audited in the MySQL Server error log. For example:

You can also monitor the Firewall operations by looking at the related Server status counters:

Firewall Status Counters

MySQL

1

2

3

4

5

6

7

8

9

mysql>SHOWSTATUSLIKE'Firewall%';

+-------------------------+-------+

|Variable_name|Value|

+-------------------------+-------+

|Firewall_access_denied|32|

|Firewall_access_granted|138|

|Firewall_cached_entries|39|

+-------------------------+-------+

3rowsinset(0,00sec)

Adding and Removing Rules

Using the bundled stored procedure you can easily remove and add new rules. To delete all of the rules for a user, you can simply use the RESET option:mysql>CALL sp_set_firewall_mode('u1@10.0.0.1','RESET');

The RESET option will clear the whitelist rules for the user and then turn the Firewall protection OFF for them as well. Users with the OFF mode set will be completely ignored by the Firewall (no firewalling). An empty Firewall list is not allowed in PROTECTING mode–at least one whitelist rule must exist first. This is a safety precaution that prevents you from accidentally locking yourself out of the system. Should that happen anyway, you can simply restart the server with the mysql_firewall_mode=OFF Server option set.

Also say you’ve updated your software or for some reason you need to add a rule you can simply switch the mode to RECORDING withsp_set_firewall_mode and then execute all of the SQL statements – either directly as SQL or indirectly (from the software) – that you want to be added to the whitelist, or alternatively you can also INSERT new rows directly into the ‘mysql.firewall_whitelist’ table if you prefer (not recommended). You could also easily move a list from a staged to a production system for example. When using thesp_set_firewall_mode stored procedure, the new rules will be added to any existing rules when you subsequently switch the mode back from RECORDING to PROTECTING.

We look forward to your feedback on this new feature! You can leave a comment here on the blog post or in a support ticket. If you feel that you encountered any related bugs, please do let us know via a bug report.

Database servers take the incoming SQL query and run it through a parser resulting in a parse tree. Then they turn the tree into a plan and execute the plan.

The essence of injection is that the parser produces a tree different from the one intended by the programmer.

So the fix is to be able to detect unusual parse trees. Walk the tree after parsing and produce a string in canonical form minus the data values. Compute a digest hash of the string. Keep a table of known hashes for the application/database user. Warn or abort if the server sees an unknown hash.

Obviously, there is a startup problem. So the programmer would have to run the application in a testing mode, extract the hashes after exhaustive testing, and the load the server with the hashes on application startup. Then turn on abort/prevent and SQL injection should not be possible.