Storing JTextArea from Swing in an Access 2007 database

Question

I am currently writing an application in Java that preforms input/output from/to Swing GUI components and stores/retrieves this data from a local Microsoft Access 2007 database. Everything is going well except when I try to update a record with input coming in from a JTextArea that is to be stored in a Text or Memo field. I can take in input from a JTextField fine, but I get a "SQLException: Syntax error in UPDATE statement." with a JTextArea.

Here's the code that is having the problem:

/* <in_some_method> */
myJTextArea.setText(someString); // the components can have the exact
myJTextField.setText(someString); // same string and still have problems

saveEdit(myTable, myColumn, myJTextField.getText(), myID); // this works fine
saveEdit(myTable, myColumn, myJTextArea.getText(), myID); // this throws an exception
/* </in_some_method> */

/* the update method */
public void saveEdit(String table, String column, String value, long id) {
    String query = "UPDATE " + table + " ";
    query += "SET " + column + " = '" + value + "', "
    query +=  "UpdatedAt = Now() ";
    query += "WHERE ID = " + id;

    try {
        // conn is a working connection to the database
        Statement s = conn.createStatement();

        // execute the query
        s.executeUpdate(query);

        // close open database handle
        s.close();

    } catch (Exception ex) {
        System.err.println(ex);
    }
}

Couple of things:

I don't think it exists with the data type of the field in the database; I've tried both Memo and Text for the type and both work with the JTextField and neither work with the JTextArea.
The exception, as already stated, is "SQLException: Syntax error in UPDATE statement." so I know my problem has nothing to do with the layout of the database table; the table and columns requested exist and can be accessed.

Anybody have any ideas? Any help will be greatly appreciated.

What's the value of myJTextArea.getText()? – Thinhbk Jun 28 '12 at 03:55 — Thinhbk, Jun 28 '12 at 03:55

score 1 · Accepted Answer · edited May 23 '17 at 10:09

I don't know what someString is, but I'm betting it contains newlines or such, so when setting into a JTextField it will be "flattened" while when setting to the JTextArea it will not. So, when getting, you'll have two different strings, one that will work fine and other that will cause a syntax error.

Whatever the case is, you should escape your strings before saving them in the database, or you run the risk of SQL injection. This will also ensure your method work fine for both components (though the exact string can be still different, for the reason stated above).

score 1 · Answer 2 · answered Jun 28 '12 at 04:04

I agree with mgibsonbr, that it is vunerable to SQL Injection. This is another tutorial on how to go about using them

However for your immediate problem, given that the string retrieved using the getText() of the textfield already works, have you perhaps tried storing the string retrieved using getText() from your JTextArea and checking what it is? or perhaps even tried to call the trim function on the getText method of your JTextArea so there are no empty spaces etc.

Perhaps that someString is not being set properly for the JTextArea?

Storing JTextArea from Swing in an Access 2007 database

2 Answers2