In this seventh episode of the MySQL Security series, we will see how MySQL Enterprise Firewall can help you to strengthen the protection of your data, in real-time, against cyber security threats including SQL Injection attacks by monitoring, alerting, and blocking unauthorized database activity without any changes to your applications.

Installing the MySQL Enterprise Firewall Plugin

MySQL Enterprise Firewall installation is an easy one-time operation that involves running a script (e.g. linux_install_firewall.sql in this blog post (Linux and similar systems that use .so as the file name suffix); win_install_firewall.sql for Windows systems that use .dll as the file name suffix) located in the share directory of your MySQL installation.

I’m using MySQL 5.7.21 Enterprise Edition :

MySQL 5.7.21 Enterprise Edition

MySQL

1

2

3

4

5

6

7

mysql>

SELECTversion();

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

|version()|

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

|5.7.21-enterprise-commercial-advanced-log|

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

Updated on 22nd of August 2018
Note: MySQL Enterprise Firewall works with MySQL 8.0 as well. In other words examples below could be done with MySQL8.0.12+

MySQL Enterprise Firewall does not work together with the MySQL Query Cache. Fortunately the query cache is disabled by default.

recording, the firewall adds the normalized statement to the account whitelist rules.

protecting, the firewall compares the normalized statement to the account whitelist rules. If there is a match, the statement passes and the server continues to process it. Otherwise, the server rejects the statement and returns an error to the client. The firewall also writes the rejected statement to the error log if the mysql_firewall_trace system variable is enabled.

detecting, the firewall matches statements as in protecting mode, but writes nonmatching statements to the error log without denying access.

Recording mode

Ok now we know our queries, let’s go back to the Firewall.

The basic and powerful idea of the MySQL Firewall is to deny SQL statement execution based on matching against a whitelist. In other words the Firewall learns acceptable statement patterns.

In order to create this whitelist, we’ll switch the Firewall in the RECORDING mode using sp_set_firewall_modestored procedure :

register the account with the firewall and place it in recording mode

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

mysql>

-- register the account with the firewall and place it in recording mode

CALLmysql.sp_set_firewall_mode('myApp@localhost','RECORDING');

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

|read_firewall_whitelist(arg_userhost,FW.rule)|

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

|Importedusers:0

Importedrules:0

|

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

1rowinset(0.00sec)

QueryOK,0rowsaffected(0.00sec)

We can know see what is the status of the Firewall for any user with INFORMATION_SCHEMA.MYSQL_FIREWALL_USERS table :

Check firewall status

MySQL

1

2

3

4

5

6

7

8

9

10

mysql>

-- Check firewall status

SELECTMODE

FROMINFORMATION_SCHEMA.MYSQL_FIREWALL_USERS

WHEREUSERHOST='myApp@localhost';

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

|MODE|

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

|RECORDING|

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

During the recording mode, we can run the application. The queries generated by the application will be recorded in the Firewall’s whitelist :

Authentication query

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

# Login failed (Mike / 0000)

mysql_myApp>

SELECTstaff_id,first_name,email,last_name,username,password

FROMsakila.staff

WHEREusername='Mike'ANDpassword=sha1(0000)\G

Emptyset(0.00sec)

# Login succeed (Mike / 12345)

SELECTstaff_id,first_name,email,last_name,username,password

FROMsakila.staff

WHEREusername='Mike'ANDpassword=sha1(12345)\G

***************************1.row***************************

staff_id:1

first_name:Mike

email:Mike.Hillyer@sakilastaff.com

last_name:Hillyer

username:Mike

password:8cb2237d0679ca88db6464eac60da96345513964

Other queries…

Regular queries

MySQL

1

2

3

4

5

6

7

8

9

10

11

mysql_myApp>

UPDATErentalSETreturn_date=NOW()WHERErental_id=1;

QueryOK,1rowaffected(0.00sec)

Rowsmatched:1Changed:1Warnings:0

SELECTget_customer_balance(1,NOW());

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

|get_customer_balance(1,NOW())|

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

|0.00|

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

And so on…

When the training is done switch the Firewall to protecting mode.

Protecting mode

Use the sp_set_firewall_mode stored procedure to switch the registered user to protecting mode:

Firewall in protecting mode

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

mysql>

-- Switch the Firewall in protecting mode

CALLmysql.sp_set_firewall_mode('myApp@localhost','PROTECTING');

QueryOK,3rowsaffected(0.00sec)

-- Check

SELECT*FROMINFORMATION_SCHEMA.MYSQL_FIREWALL_USERS;

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

|USERHOST|MODE|

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

|myApp@localhost|PROTECTING|

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

Firewall stores SQL statements on a normalized digest form. You can check the whitelist with INFORMATION_SCHEMA.MYSQL_FIREWALL_WHITELIST table :

NoteFor additional training you can switch back recording mode or even update (that is an UPDATE query) this table if necessary using the normalize_statement UDF.

In protecting mode, there are 2 kind of queries for the application point of view :

Acceptable

Acceptable and unacceptable statements

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

mysql_app>

UPDATErentalSETreturn_date=NOW()WHERErental_id=1;

QueryOK,1rowaffected(0.03sec)

Rowsmatched:1Changed:1Warnings:0

UPDATErentalSETreturn_date=NOW()WHERErental_id=42;

QueryOK,1rowaffected(0.03sec)

Rowsmatched:1Changed:1Warnings:0

SELECTget_customer_balance(5,NOW());

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

|get_customer_balance(5,NOW())|

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

|0.00|

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

1rowinset(0.01sec)

Unacceptable

Unacceptable statements

MySQL

1

2

3

4

5

6

7

mysql_app>

DROPTABLErental;

ERROR1045(28000):StatementwasblockedbyFirewall

UPDATErentalSETreturn_date=NOW();

ERROR1045(28000):StatementwasblockedbyFirewall

SQL injection

One of the big advantage of the MySQL Firewall is that it can help protect against SQL Injection attacks. In this post, I will not go into details of what is an SQL injection. However below a simplistic example to illustrate the overall principle.

Uninstall the Firewall

To remove MySQL Enterprise Firewall, execute the following statements :

Uninstall the firewall

MySQL

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

mysql>

USEmysql;

DROPTABLEmysql.firewall_whitelist;

DROPTABLEmysql.firewall_users;

UNINSTALL PLUGINmysql_firewall;

UNINSTALL PLUGINmysql_firewall_whitelist;

UNINSTALL PLUGINmysql_firewall_users;

DROP FUNCTIONset_firewall_mode;

DROP FUNCTIONnormalize_statement;

DROP FUNCTIONread_firewall_whitelist;

DROP FUNCTIONread_firewall_users;

DROP FUNCTIONmysql_firewall_flush_status;

DROPPROCEDUREmysql.sp_set_firewall_mode;

DROPPROCEDUREmysql.sp_reload_firewall_rules;

NoteYou may have to kill the application remaining connections (e.g. KILL CONNECTION) or reconnect the application user (e.g. mysql> connect)

MySQL Enterprise Edition

MySQL Enterprise Edition includes the most comprehensive set of advanced features, management tools and technical support to achieve the highest levels of MySQL scalability, security, reliability, and uptime.

It reduces the risk, cost, and complexity in developing, deploying, and managing business-critical MySQL applications.