HSQLDB .script file

Question

I start HSQLDB in file mode. It runs very well. When my Java code ends, I elegantly close the HSQLDB by issuing SHUTDOWN. This removes the temporary files that were created such as .lck and .log, etc.

However, even after SHUTDOWN two files are always present - .script and .properties.

I know .script is used if we want to restart the HSQLDB and connect to the already existed database. That's good. But this file contains the raw data and hence can easily be modified. This could be a security concern.

Can someone suggest the best feasible way to handle this? Should I encode the .script file? I would still prefer to connect to the already created database at a later stage because that is the only reason I am running file mode instead of in-memory mode. I don't want to use server (in-memory) mode.

I am using JDK 1.7.0_02 and HSQLDB 2.2.5 on Windows7.

Thanks.

Answer 1

Using this example here -> http://www.hsqldb.org/doc/1.8/guide/apb.html

I have made a tiny change to the code, which will encrypt the *.script file using the AES key.

Code

package com.gollahalli.main;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import org.hsqldb.Server;

public class Testdb {

    Connection conn;                                                //our connnection to the db - presist for life of program

    // we dont want this garbage collected until we are done
    public Testdb(String db_file_name_prefix) throws Exception {

        Class.forName("org.hsqldb.jdbcDriver");

        conn = DriverManager.getConnection("jdbc:hsqldb:"
                + db_file_name_prefix, // filenames
                "sa", // username
                "");                      // password
    }

    public void shutdown() throws SQLException {

        Statement st = conn.createStatement();

        st.execute("SHUTDOWN");
        conn.close();    // if there are no other open connection
    }

//use for SQL command SELECT
    public synchronized void query(String expression) throws SQLException {

        Statement st = null;
        ResultSet rs = null;

        st = conn.createStatement();         // statement objects can be reused with

        rs = st.executeQuery(expression);    // run the query

        dump(rs);
        st.close();    // NOTE!! if you close a statement the associated ResultSet is

    }

//use for SQL commands CREATE, DROP, INSERT and UPDATE
    public synchronized void update(String expression) throws SQLException {

        Statement st = null;

        st = conn.createStatement();    // statements

        int i = st.executeUpdate(expression);    // run the query

        if (i == -1) {
            System.out.println("db error : " + expression);
        }

        st.close();
    }    // void update()

    public static void dump(ResultSet rs) throws SQLException {

        ResultSetMetaData meta = rs.getMetaData();
        int colmax = meta.getColumnCount();
        int i;
        Object o = null;

        for (; rs.next();) {
            for (i = 0; i < colmax; ++i) {
                o = rs.getObject(i + 1);    // Is SQL the first column is indexed

                // with 1 not 0
                System.out.print(o.toString() + " ");
            }

            System.out.println(" ");
        }
    }                                       //void dump( ResultSet rs )

    public static void main(String[] args) {

        Server server = new Server();
        server.setDatabasePath(0, "file:./RemindMe;crypt_key=604a6105889da65326bf35790a923932;crypt_type=AES");
        server.setDatabaseName(0, "RemindMe");
        server.start();

        Testdb db = null;

        try {
            db = new Testdb("RemindMe");
        } catch (Exception ex1) {

            return;                   // bye bye
        }

        try {

            db.update(
                    "CREATE TABLE sample_table ( id INTEGER IDENTITY, str_col VARCHAR(256), num_col INTEGER)");
        } catch (SQLException ex2) {

        }

        try {

            db.update(
                    "INSERT INTO sample_table(str_col,num_col) VALUES('Ford', 100)");
            db.update(
                    "INSERT INTO sample_table(str_col,num_col) VALUES('Toyota', 200)");
            db.update(
                    "INSERT INTO sample_table(str_col,num_col) VALUES('Honda', 300)");
            db.update(
                    "INSERT INTO sample_table(str_col,num_col) VALUES('GM', 400)");

            // do a query
            db.query("SELECT * FROM sample_table WHERE num_col < 250");

            // at end of program
            db.shutdown();
        } catch (SQLException ex3) {
        }

        server.shutdown();
    }    // main()
}    // class Testdb

Take a look at the main method, which has these lines

Server server = new Server();
server.setDatabasePath(0, "file:./RemindMe;crypt_key=604a6105889da65326bf35790a923932;crypt_type=AES");
server.setDatabaseName(0, "RemindMe");
server.start();
.....
server.shutdown();

When the server is running HSQLDB will create a file with extension .lck, which will be there till the server is shutdown.

To generate an AES key you can use https://asecuritysite.com/encryption/keygen or use CALL CRYPT_KEY('cypher_text', null);

This should do the trick.

Answer 2

If you are afraid that someone sees the content the .script file directly you can encrypt it:

http://hsqldb.org/doc/2.0/guide/management-chapt.html#mtc_encrypted_database

That will prevent the user to see the real data in the file.

But that does not prevent the users from changing the file. If the user has physical access to the file, there is no way you can prevent that.