How to delete error [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'date=19apr2015.'

Question

Please tell me how to solve the error from the code. I want to get the details from the ms access table. I have used data and description as columns. date is the primary key in ms access. so please let me help me with reading the data from ms access table.

---

try{   
    connect();     
    stmt = (Statement) conn.createStatement();
    String sql, ks = " ";
    ks = JOptionPane.showInputDialog("enter the date of which you want to read");      
    String jk = " where date=" + ks; 


    sql = "SELECT [date],[description]  FROM  Table2" + jk;
    System.out.println("1");
    rs = ((java.sql.Statement) stmt).executeQuery(sql);

    if(rs.next())
    {
        String date1="hello",description1="hii";
        date1 = rs.getString("date");
        description1=rs.getString("description");
        JOptionPane.showMessageDialog(null,"Date:"+date1+"\n"+description1);
    }
    else
    {
        JOptionPane.showMessageDialog(null,"Sorry the record does not exist");
        try
        {
            close();
        }
        catch(Exception ea)
        {
            JOptionPane.showMessageDialog(null, "Error:"+ea.getMessage());
        }
    }
}
catch(Exception ew)
{
    JOptionPane.showMessageDialog(null, "Unable to fetch Data");
    JOptionPane.showMessageDialog(null,""+ew.getMessage());
    System.out.println(""+ew);
}

---

Thanks

Answer 1

Your error message says that there is a syntax error in your SQL query. In your example, it seems to be:

SELECT [date],[description] FROM Table2  where date=19apr2015.

This is wrong as the date (19apr2015) does not have the correct format.

A correct SQL query would be:

SELECT [date],[description] FROM Table2  where date=#4/19/2015#

So you will have to parse the user input and convert it into the correct form, or request the user to enter the date in the correct form.

SQL injection

In both cases it is adviced to use a PreparedStatement with the date as parameter instead of putting together the SQL statement using string concatenation, as the latter is dangerous because of an evil thing called SQL injection! For example, imagine, the user wants to do any harm and instead of entering a valid date, he/she enters

#1/1/2000#; DELETE * FROM Table2

So the following SQL statement would be executed by your database:

SELECT [date],[description] FROM Table2 where date=#1/1/2000#; DELETE * FROM Table2

The second statement would simple delete all your data! (To be exact, the statement above would fail as MS Access does not support to chain multiple statements, but other database systems do support this, so this is a security leak that is sleeping until you migrate your database. Apart from that, there are other ways to do SQL injection in MS Access, e.g. by using Subqueries.)

By using a PreparedStatement you can avoid this issue. Apart from that it is more convenient to program:

Date date = /* java.sql.Date-object created from user input */

try (PreparedStatement stmt = conn.prepareStatement(
        "SELECT [date],[description] FROM Table2 where date=?")
    ) {

    // set first (and only) parameter value
    stmt.setDate(1, date);

    // execute statement
    try (ResultSet result = stmt.executeQuery()) {
        // process result as usual
    }
}