Let’s make database connections more secure

As you know, the users of SQL databases are only allowed to establish local connections by default. This ensures that data are not transferred over external networks. When using the default configuration, Zone also uses its internal network between the database and the web server to transfer data – so your data cannot “escape” in any way. If, however, the user wants to establish a connection to the SQL database from outside, we recommend enabling the TLS protocol, that encrypts all data traffic between the client application and the database server.

At Zone, the customers have for some time had the possibility to configure their applications to use secure TLS connections. As databases tend to contain information that must not leak and also includes sensitive personal data, special attention must be paid to the security of handling these data. For this reason, using encrypted connections can now be made mandatory for the users in Zone. Read more about this below.

Why do I need encrypted SQL connections?

As a rule, secure TLS connections must be used for external connections, especially when the application is located outside the Zone system. For example, when an external service located in the cloud of another service provider has been connected to the SQL server. In such a situation, it may be sufficient if the application administrator knows that an SSL connection must be configured beforehand. However, such external connections may still involve situations where such one-time setup will not guarantee a secure connection.

In such cases, particular care must be taken when access has been granted to a user connecting to the database via a desktop application, for example, Sequel Pro that is very commonly used by developers, or the less well-known Beekeeper Studio – when such applications are used, one should always ensure in advance that the connection is encrypted using the TLS protocol. Of course, there may be situations where the administrator has no control over this. To prevent this, we have now introduced the possibility to specify that a database user can only access the database over TLS connections. To do this, you must open the database user management and set the “Require TLS connection from user” value.

But why is it needed anyway? Sometimes, when performing its tasks the software needs to ask the developer some questions like “Please check what is happening with this order in the database?”. As working remotely is very common today, the developer could well check the database, sitting in a café and using its public WiFi. If TLS connections are not used in such situations, it is safe to say that all personal data associated with the orders have in principle already passed through the hands of third parties.

In the case of external connections, it is also important to ensure that connection paths are restricted to whitelisted IP addresses. This must also be configured in the My Zone management interface under the settings for a specific database user. It is important to know that using encrypted connections is possible starting from version 1.2 of the TLS protocol. However, we would like to point out that this could create issues for older applications and database clients. It is therefore also worth updating these whenever possible.

Using TLS in client applications

For desktop clients, you only need to mark the right checkbox when configuring the connection. For configuring a server application, see the examples provided below.

For WordPress applications, it is sufficient to add one line to the file wp-config.php:

<strong>define</strong>( 'MYSQL_CLIENT_FLAGS', MYSQLI_CLIENT_SSL );Code language: HTML, XML (xml)

When using the PDO connection in a PHP script, you must set the MYSQL_ATTR_SSL_CA value. On the Zone server, for example:

try {
$db = new PDO(
'mysql:host=dXXX.mysql.zonevs.eu;dbname=$database;port=3306',
'{database_username}',
'{database_password}',
[
PDO::MYSQL_ATTR_SSL_CA =>
'/etc/ssl/zse/infernal/cacrt.d/MYSQL.ZONEVS.EU.crt'
]
);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo "Connected successfully";
} catch (PDOException $error) {
echo $error->getMessage();
}Code language: PHP (php)

Please note! TLS 1.2 is supported starting from PHP version 7.2!

To establish the connection from the command line on a server using MariaDB, the –ssl parameter must be used:

mysql -h dXXX.mysql.zonevs.eu -u {database_username} --ssl -p

To establish the connection from the command line on a server using MySQL 8.0 the –ssl-mode= parameter must be used:

mysql -h dXXX.mysql.zonevs.eu -u {database_username} --ssl-mode=REQUIRED -p

The parameters provided above are useful if you want to use the mysqldump functionality, for example if you want to create backup copies of your MySQL outside the Zone network.

How can I check if my connection is encrypted?

Regardless of the method you use, you can check the functioning of the SSL connection with the following SQL statement:

SHOW STATUS LIKE 'Ssl_cipher';Code language: JavaScript (javascript)

For a TLS connection the value of Ssl_cipher should be e.g. TLS_AES_256_GCM_SHA384. If this value is empty, the TLS connection is not used.