How can I solve SQLSyntaxErrorException in hsqldb?

Question

I try to use HSQLDB embedded in program.

However, when I create table and insert data in the database, error occurs.

Following is part of error message.

java.sql.SQLSyntaxErrorException: unexpected token: MAR required: )
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.Util.sqlException(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)
    at org.hsqldb.jdbc.JDBCStatement.executeUpdate(Unknown Source)

It occurs when I insert some data in the database.

This is my CREATE statement.

stmt.execute("CREATE TABLE IF NOT EXISTS readability ( id INTEGER NOT NULL IDENTITY,"
  + "LOC INTEGER DEFAULT NULL, " + "numOfComments INTEGER DEFAULT NULL,"
  + "numOfBlankLines INTEGER DEFAULT NULL," + "numOfBitOperators INTEGER DEFAULT NULL,"
  + "readability double DEFAULT NULL," + "username varchar(255) DEFAULT NULL,"
  + "storedTime datetime DEFAULT NULL," + "methodname varchar(255) DEFAULT NULL,"
  + "classname varchar(255) DEFAULT NULL," + "patternrate double DEFAULT NULL,"
  + "maxNestedControl INTEGER DEFAULT NULL," + "programVolume double DEFAULT NULL,"
  + "entropy double DEFAULT NULL,"
  + "CONSTRAINT username FOREIGN KEY (username) REFERENCES user (username) ON DELETE NO ACTION ON UPDATE NO ACTION"
  + ");");

This is my INSERT statement.

stmt.executeUpdate(
  "INSERT INTO readability (LOC, numOfComments, numOfBlankLines, numOfBitOperators,"
  + " readability, username, storedTime, methodname, classname, patternRate, maxNestedControl, programVolume, entropy) VALUES("
  + readability.getLOC() + ", " + readability.getNumOfComments() + ", "
  + readability.getNumOfBlankLines() + ", " + readability.getNumOfBitOperators() + ", "
  + readability.getReadability() + ", '" + readability.getUser().getUsername() + "', "
  + readability.getStoredTime() + ", '" + readability.getMethodName() + "', '"
  + readability.getClassName() + "', " + readability.getPatternRate() + ", "
  + readability.getMaxNestedControl() + ", " + readability.getProgramVolume() + ", "
  + readability.getEntropy() + ")",
  Statement.RETURN_GENERATED_KEYS);

The object readability has all attributes used.

Answer 1

Do not concatenate values into a query string. Your code is vulnerable to SQL injection, and it is probably also the cause of the error. Use a prepared statement with parameter placeholders and set the values with the appropriate setters.

Your code would then become something like (leaving out a lot of columns for brevity):

try (PreparedStatement insert = connection.prepareStatement(
        "insert into readability (LOC, username) values (?, ?)", 
        Statement.RETURN_GENERATED_KEYS)) {
    insert.setInt(readability.getLOC();
    insert.setString(readibility.getUser().getUsername());

    insert.executeUpdate();

    // handle generated keys...
}

You might also want to consider using an ORM like Hibernate.