MySQL is the database component of the LAMP stack, being a common method for storing web application data, but its use isn’t limited to this. I’ve been working on a case management system that enables a security department to share malware information on an internal network, with a MySQL server providing a central data source. Here’s how I got the server running, created a database and prototyped a basic front-end UI in LibreOffice.

The default MySQL installation includes a set of command line tools for administrating the server and databases. To start and stop the MySQL server (mysqld):
$sudo /etc/init.d/mysql stop
$sudo /etc/init.d/mysql start

MySQL Workbench

There are also a number of GUI applications for working with MySQL servers. One of these is the default mysqlnavigator utility, but the common application is the freely-available MySQL Workbench. Later it becomes useful for sorting out replication, backups, performance and access management. In order to work with a server in MySQL Workbench, a ‘server instance’ must be created.

After that, we can start administrating the applications/user accounts registered with it, later sorting out the permissions and importing or exporting databases.

Setting Up the Database in the Command Line

It’s best to acquire some experience of working in the command line. Create the database by logging into the MySQL server: $mysql -u root -p Here ‘root’ is the database server (not the system) admin account. The best practice is to assign applications their own accounts to restrict access to specific databases, and to define the actions they could perform on the server. The ‘-p‘ option tells the server to prompt for the account password, if it’s been set.
In case you’ve experimented with MySQL before and lost the password as I did, Rackspace has outlined a process for resetting native user database.

Creating the database is simple as the following command: mysql> CREATE DATABASE nexus;
Notice that a semi-colon is appended to each statement, which marks the end of an instruction. Next, switch to the database and list the tables it contains (initially there are none):
mysql> USE nexus
mysql> SHOW TABLES;
Records are stored in tables, each table having columns and rows. A database can have multiple tables, and the next stage is to create one. For application development I strongly recommend designing it on paper first, making a note of the table and field names before actually implementing the design.
The following command writes a table (entities), the fields within the tables, their data types and the field lengths:
mysql> CREATE TABLE entities (Entity VARCHAR(60), Description VARCHAR(140), Category VARCHAR(20), EntryDate VARCHAR(20), Source VARCHAR(80), Target VARCHAR(80), RelatedTo VARCHAR(140), Outcome VARCHAR(160), Notes VARCHAR(160), Reference VARCHAR(160));
To confirm the table’s existence after this, use the ‘SHOW TABLES‘ command, and to view the table layout:
mysql> DESCRIBE entities;
Alternatively, the Workbench application can later be used to modify the database under the SQL Development panel:

We simply need to select ‘Open Connection to Start Querying‘ to start editing.

Adding Records

For the moment I’ll use the command line for this. Knowing the table columns is important here, as the INSERT statement will add the data from a one-dimensional array. e.g.
mysql> INSERT INTO entities (Entity, Description, Category, EntryDate, Source) -> VALUES ('Test Entity','Test Entity','Object','October 2014','localhost');

To view the records in the table:
mysql> SELECT * FROM entities;

MySQL and OpenOffice

Now we come to developing a front-end. This is an independent application that connects to the server (localhost, through the network interface), and presents a UI for users to manipulate records. The front-end and the MySQL server can also be on two different machines. Later I’ll make custom software for this, but for now I’m using LibreOffice Base. Since the whole front-end here was based on Java, I made sure the connection driver and OpenOffice MySQL Connector extension were installed:
#apt-get install libmysql-java Then enter the LibreOffice/OpenOffice Java settings, and change the connector settings.

The Oracle JRE makes sense, since MySQL seems heavily supported by Oracle. The Class Path here is /usr/share/java/mysql-connector-java.jar. Restart LibreOffice and you’re good to go. When LibreOffice/OpenOffice Base is next started, MySQL will be listed in the Database Wizard dialogue, in the drop-down menu for ‘Connect to an existing database’.

Enter the connection details (typically localhost on port 3306), and register the database in LibreOffice. If successful, a screen like the following will appear after saving the file, with our database listed.
Now it’s possible to create the UI for querying and updating records. In the Form Wizard dialogue, the available databases, tables and fields are listed.

Resolving Form Connector Problems

Now you might find that the new LibreOffice form won’t allow the modification of records. One of the fields must be defined as the primary key with the following: mysql>USE nexus
mysql>ALTER TABLE entities ADD PRIMARY KEY (Entity);

User Accounts and Security So far, I’ve been experimenting with MySQL using the root account as default. This account needed a stronger password, plus it’s bad practice to log into the server’s admin account from a client machine for a number of reasons. Remember this is not the host system’s root account: $mysqladmin -u root -p password
It will prompt for the current password and ask the user to confirm the new one. By examining the main mysql database where the user account details are stored, you’ll notice the account passwords are hashed and salted. As the account details are stored in its own database table, the system could potentially be scaled to accommodate many thousands of users.
Next, login to the MySQL server and use the ‘FLUSH PRIVILEGES;‘ command.

Since we don’t want anyone except the administrator to alter the server or the other databases, another shared account will be created for the application’s users. This could be done in the command line, but I’ve used MySQL Workbench, to ensure all the permissions were properly set. The New Schema Privilege Definition restricts the account to just one database (nexus). The settings under the Schema Privileges tab define whether the account can perform certain admin operations or modify the database in certain ways.

Even if there was an SQLi attack, proper configuration should minimise the impact. For example, it shouldn’t be possible to use SQLi within the application to modify the users table in the mysql database, as the application itself wouldn’t have access to it.
The new account setup can be tested from the LibreOffice front-end (see my last post). Only the application database and its tables should appear.

While on the subject of security, it’s important to check that port 3306 is closed on the perimeter firewall. The server should never be directly accessible from outside the network.

Deploying Client Applications

I had to make do with the LibreOffice .odb file to simulate the deployed front-end application on the client machine for now, as I’m still working on the Java UI. The .odb file can be ported to other hosts on the network and configured for a remote connection to the MySQL server.

Initially I had problems getting a connction from wihin LibreOffice, so I checked that libreoffice-mysql-connector, JDBC and ODBC were installed. These are available in the package manager. When the connection attempts were still unsuccessful, I decided to troubleshoot this methodically. The problem was something outside LibreOffice.

Running ping showed the server is reachable, so it wasn’t a routing problem. The next obvious thing is the firewall, so I also checked that iptables was disabled temporarily.
The next layer to check is MySQL itself, on both the client and server, using mysql-navigator. I eventually nailed the problem down to an entry in the configuration file /etc/mysql/my.cnf, which tells the MySQL clients to bind only to localhost by default. Comment out the line in the config files on both machines, then restart them.

Checking again with mysql-navigator, the client should now be accessing the database on the server. Permissions and privileges are good here also.

Now, returning to the LibreOffice front-end, this time with the underlying connectivity issue sorted, it should work. The database can now be accessed remotely from the .odb file.

Performance

A finishing touch here, but important later if I build a malware tracking system with multiple data sources and clients. In the MySQL Workbench on the server, there are options for configuring the server performance. Again, this is all managed from the Server Administration window.

Most the options under the Performance tab are for memory usage, cache and disk writes. This only needs looking at if there are thousands of users and stuff’s really getting maxed out. Also, the networking equipment between the server and client machines is a design consideration, as that could become a major cause of packet drops.
We also have more general security options. Some of them make the server more secure, but might cause compatibility issues.

Java Clients and Remote Connection

Things work a little differently with Java than with Python, but we still require a library to enable a program to comunicate with a database server. Here the library/class is a MySQL ‘connector’ (mysql-connector-java-5.1.33-bin.jar).
With this installed, a new database connection can be created, and it’s listed under the Services window on the left.

From here, the database properties are available. So far, so good. I had a database connection to work with. The next stage is to create a Java UI for this connection, but before getting to that, NetBeans’ GUI designer won’t load properly on some Debian-based systems, because it has problems finding a component for this. Use the following to define its location when starting NetBeans in the command line:
$netbeans -cp:a /usr/share/java/xercesImpl.jar
Now the quickest way of getting a basic GUI is by creating a Java Desktop Application project, and selecting the Database Application shell in the dialogue.

Here I checked the ‘Use Dedicated Folder for Storing Libraries‘ option, just in case it was required for portability. After this, I specified the database connection and the table.

NetBeans is supposed to display a ready-made GUI form to work with, but initially it didn’t. What I encountered was an error message stating that a load of components were missing. Since I’m still fairly new to Java and had been hacking the NetBeans installation over the last month, there was a good chance I broke something fundamental. I removed the IDE and all its configuration files before re-installing it.
The same problem occurred, so it was definitely a dependecy problem. I went through the package manager, downloading all the components that NetBeans might have been trying to load. When that didn’t work, I finally resorted to actually reading the error messages.

Now, to cut a longer story short, what should have been a simple tutorial program required ‘javax.persistence.Exception‘, which apparently was missing from the available libraries, and it was something native to Java EE (the ‘enterprise’ edition of Java).
What I needed was included with an open source alternative to Java EE called ‘GlassFish’. Install this in the package manager, then select the following in NetBeans plugin installer: These weren’t listed in the plugin catalogue before, for some reason. Connecting to the plugin server was also problematic, and various solutions have been suggested on the web. In my case, the plugin server itself was actually down for several hours.
When the plugins were finally installed and the IDE restarted, the form loaded, compiled and ran perfectly. I made a few changes here.

Client Portability

Just a few other things before wrapping up. The compile libraries under the Project Properties should include the mysql-connector-java and MySQL JDBC Driver libraries, and probably the TopLink library also.

The source tree might look a bit complicated, but we can forget about the files under Libraries, META-INF and META-INF.services from this point, since I’ve already demonstrated a form that interacts with the server. Everything that needs to be modified to rebuild the UI exists in just two of the .java source files.

Now for the real test of whether the application can do the same on another machine. The persistence.xml appears to be where the MySQL server address is defined for the whole project, so I replaced ‘localhost‘ here with the server’s known address before compiling the source – this could also be the static address of an Internet-facing MySQL server.

Cleaning and building the project (in other words compiling it) produces the /dist direcory, which contains the application and its dependencies. This is what I copied over to the other host, and it ran perfectly.