Author: Mateusz Palichleb

MySQL is one of the most popular relational database management systems (RDBMS) around the world. Unfortunately, due to its popularity, people already know its weak points. A straight-out-of-the-box, freshly installed MySQL database can be vulnerable to many risks. To prevent attacks and security exploits, we must arm ourselves with knowledge about security improvements. So let’s start!

Chart 1. Database management systems according to their popularity.
September 2016, Source: http://db-engines.com/

You should be especially interested if you use the MySQL RDBMS. Don’t worry – I will lead you through the most important security cases step by step.

1. Always run MySQL as a user without root privileges

Running database service as a Root is a bad practice. Firstly, you should create a new user account – with only the necessary permissions (e.g. appropriate permissions to the data directory) to run the service. After that, you should always run the MySQL server through this new user.
Running MySQL as Root means that everything the server does is also done with root privileges. If you happen to make a mistake, this can cause problems:

• if you misconfigure the MySQL logfile to /etc/passwd, then that important file will probably be overwritten (a normal user can’t do that)
• Root usually has some amount of disk system reserved (per filesystem), therefore it’s more likely to fill the whole partition
• code injection: if you have scripts running (for backup of the now Root-owned files) then you need higher privileges there, and – again – any mistake might have more grievous consequences

2. Make the MySQL root user account “invisible”

After the installation, the database’s grant tables are populated with default MySQL accounts.
Some accounts have the username: root. These are superuser accounts which have all the privileges. Basically, they can do everything. If these root accounts have empty passwords, anyone can connect to the MySQL server as root without a password and be granted all privileges. We have to prevent this!
Firstly, assign a password to each MySQL root account that doesn’t have one.
Secondly, change the username of all root accounts to hard-to-guess names, because attackers have to provide the account name before trying to brute force the passwords.
This step will add an extra layer of security.

3. Delete all anonymous accounts

In addition to administrator accounts after installation, there are anonymous user accounts. They have no password, so anyone can use them to connect to the MySQL server. These accounts should be removed, because they give attackers an entry point in our database.

4. Recommended configuration tips

a) network access
Nowadays, we’re often in the situation where the database is running on the same server as a production application. This means we can connect to the MySQL server locally, without remote access. This remote option should be disabled to avoid the possibility of attacks from the outside.
We can do that by adding a skip-networking entry to the configuration file /etc/my.cnf (below the [mysqld] section).
Otherwise, when we’re running MySQL and application on separate servers, we should configure the MySQL server to give access only to allowed remote hosts. You can do this by making some changes in /etc/hosts.deny and /etc/hosts.allow files.

• Make sure to check if your MySQL daemon is listening on remote accessible port only when it is needed. Create an appropriate firewall rule to allow connection to MySQL port only from trusted hosts.
• In case you’re using Amazon RDS as a MySQL service, check if your instance is attached to the correct security group. It is recommended to carry out some tests of connections from various remote hosts to check if your configuration is secure.
• Set firewall rules with IP address instead of hostnames to protect yourself from DNS spoofing and similar attacks if you don’t trust your DNS. Don’t create firewall rules with wildcards.
• In case of emergency, when you need direct access to the database to fix some issue, it is better to use the SSH tunnel instead of opening port to network or change firewall rules.
• Check if your administrative accounts are accessible only from localhost. They should be.

 b) disable “LOAD DATA LOCAL INFILE” command
Executing this allows users to read local files. This could be used by attackers through several methods, for example SQL Injection. Furthermore, they can access other files in the operating system. This can be dangerous.
This command should be disabled by adding:

set-variable=local-infile=0

to the configuration file /etc/my.cnf (below the [mysqld] section).
 
c) do not grant FILE privilege to non-administrative users
This privilege is dangerous because it allows users to write file anywhere in filesystem with privileges of mysqld daemon. It allows to modify contents of MySQL’s data directory (including files that implements privilege table).
On the other hand, FILE privilege can be also used to execute LOAD DATA to read /etc/passwd or any other secret file to database table and then read it using SELECT.
I recommend to restrict access to the specified directory only by setting:

secure_file_priv=dirname

 d) do not grant PROCESS or SUPER privileges on non-administrative accounts
It is good to know the meaning of two dangerous privileges that should be set only for administrative accounts.
PROCESS privilege allows to display all currently executed SQL commands. These include vulnerable ones like setting new passwords to MYSQL users or setting new credentials by user in your web application. The list of processed queries is not limited to the currently logged user and is not encrypted.
SUPER privilege allows to change server system variables, terminate client connections, control replication servers and connect regardless of the connection limit.
 
e) disable or restrict a “SHOW DATABASES” command
The “SHOW DATABASES” command allows users to see names of databases in our MySQL server.
If attackers are able to get the database names, they can easily get access to the data.
This command should be disabled or restricted on production server by adding:

skip-show-database

to the configuration file /etc/my.cnf (below the [mysqld] section).
 
f) change default port
By default, our MySQL server will run on port 3306. If we don’t change this, attackers will try to use these initial values to get access to the database.
We should change this critical value immediately after installation to prevent dummy hacker attacks.

5. Delete the Test database

After installation, your server will have a ‘test’ database which was installed by MySQL Server package. This database can be accessed by all users by default. This is a security risk. It should be removed by running:

DROP DATABASE test;

6. Clear the MySQL history file and prevent logging to this file

This file contains historical details about initial configuration and installation. It may be exploited for getting passwords (for database users). Also, other data will be logged into this file while the MySQL server works. It’s located at ~/.mysql_history and should be deleted. We should also block logging to file by creating a softlink to the /dev/null device.

Conclusion

These are the most popular cases. Of course, there are more security threats. Remember to keep your operating system environment secure. If the entire environment is unsafe, all things in it are vulnerable and can be easily exposed to attackers.
MySQL provides a simple command to help proceed with most important security settings. This command is called:

mysql_secure_installation

It guides you through setting password for root account, removing of anonymous users, disabling root remote access, removing test database with related access rules. At the end, it flushes the privilege table. It is a quick command that should be run right after mysql daemon installation.
If you want to know more about securing or configuring the MySQL server – check out the following links.
Sources and more information:
[1] http://db-engines.com/en/ranking_trend
[2] https://dev.mysql.com/doc/refman/5.5/en/default-privileges.html
[3] https://besthostingsearch.org/tutorial/secure-mysql-databases-against-attackers/
[4] http://technotif.com/hardening-mysql-security-server/
Authors:
Mateusz Palichleb, Filip Chmarzyński

Looking for something to speed up your work with tables? You can use a simple jQuery plugin named DataTables. It’s a highly flexible tool that adds advanced interaction controls to any HTML table. I’ll show you how to use it.

Problem: sometimes working with lists (such as product lists) just takes too long. Everything is done on the server side and the page needs to reload every time. What’s more, we often need to present data in an elegant way. Usually, developers build their own tables with additional functionalities like sorting, filtering or searching – processed mainly on server-side. However, building this from scratch takes some time.
Solution: With DataTables, it’s enough to write a module in the backend that will supply data to this plugin, and then the pagination, sorting, searches will be handled by the plugin in the background – no reloading. Furthermore, this plugin has a growing community and extensive documentation. Additionally, it can be used in another project, and the look and style of the plugin can be changed.
 
DataTables’ most interesting features :

• Instant search, pagination, multi-column ordering
• Supports many data sources like: Ajax, Javascript, JSON, Server-side processing
• Detection and building of own events
• Security module – for example, using CSRF-token in requests or prevention of XSS attacks
• Plenty of extensions and plugins like internationalization, API engine to fetch and manipulate columns and rows, type detection, rendering (transforming data into another format)

 
Let’s get to the practical part!
 

Requirements

DataTables has only one library dependency – jQuery. Being a jQuery plugin, DataTables makes use of many of features that jQuery provides.
 

Installation

Let’s start with installation. There are different ways to do it: through CDN, locally, using NPM or Bower. In this example, I chose Bower:

The first package is the core library of DataTables. The second one contains extensions and styling integration options.
 

HTML initial structure

Firstly, we must create a table: a well formatted HTML with a header and body, optionally with footer.

 

Initializing script

If we’ve got all the required libraries and files we need, the last thing to do is tell DataTables to work on this html table.

That’s all! In the result, we have a table that contains default styled rows, sortable headers, search field and pagination options.
Example below:

Using options

Now, we want to customize the way that DataTables presents its interface and features to the end user. We can use a huge range of options. In this example, we want to change the default ordering of columns, disable search, and display only numbers without “prev” and “next” buttons in the pagination:

Result below:

There are many more interesting options you can use to customize this table.
You can also load table data directly from files. Try it yourself with examples from the DataTables documentation.
 

Styling

Furthermore, we can change styles (DataTables stylesheet is written in SCSS) or enable embedded integration with Bootstrap, Foundation and jQuery UI.
There is also a online Theme Creator, making styles very easy to customise!
Example:

If you want to use nice-looking themes for DataTables, take a look at Theme Forest templates. These have DataTables support built in.
 

Server-side processing

What would be a good table plugin that doesn’t fetch data from the server? A very important issue of DataTables is the possibility to use Server-side processing.
Let me show you how to use it.
First, we have to know how the Request and Response looks. We can find it in the documentation:
Sent parameters
Returned data (JSON)
The request parameters will be prepared through DataTables, depending on the page, search field, sort headers and more. All you have to do is set the ‘serverSide’ flag as ‘true’ and choose URL to the server. Optionally – type of request, ‘POST’ method in this case.

After that, whenever you use any of the control buttons (pagination, etc.) the DataTable will send a request to the server and expect a JSON response with data.
We can also customize sending parameters and data returned though the use of ajax.dataFilter option. It may be useful when we don’t want to (or can’t) change the server-side.
Example below:

 

Internationalization

There are two solutions for loading language information into DataTable:
– As a initialization option, “language”, which could be object with all sentences we want to translate (example here)
– As a remote file loaded via Ajax (list of all available languages here)
Example with Polish (pl_PL.json)

 

Security

As I told you at the beginning, there are some security tools embedded into DataTables.
For example, we can prevent two attack types:
Cross-Site Request Forgery (CSRF)
Cross-Site Scripting (XSS)
To prevent the second one (XSS) on browser-side you can use a rendering function to block or replace all writes that contain harmful data before generating the table. These functions can be used by assigning them to columns.render option.

To prevent the first one (CSRF) most server-side systems use a token, which has to be submitted on each data request to ensure that the end-user is authenticated.
In DataTables, we can attach this token to headers, data or global Ajax header. There are many ways the server-side can expect the CSRF token.
 

Method 1 – token is attached to data:

 

Method 2 – token is attached to global header:

 

Method 3 – token is attached to data headers:

 

A similar plugin to do it the Angular way

If you’re using the Angular framework, there may be some problems with DataTables integration. Fortunately, there is a solution!
A interesting plugin for Angular 2 was created by our programmer Sebastian. It’s named ‘Angular2 Easy Table‘ with similar functionalities to jQuery DataTables. Check it out!