Inserting email in SQLite database using JDBC

Question

I am trying to insert an email ID to a table in my SQLite3 Database. In my case it successfully creates the table but gives an error while inserting a record in it - "near "@gmail": syntax error". How can i resolve this ? Here is the code -

public void insertData(String emailId, double gtse, long receivedDate) throws ClassNotFoundException, SQLException{
    Class.forName("org.sqlite.JDBC");
    Connection connection = null;

    try
    {
      // create a database connection
      connection = DriverManager.getConnection("jdbc:sqlite:testdb.sqlite");
      Statement statement = connection.createStatement();
      statement.setQueryTimeout(30);  // set timeout to 30 sec.

      ResultSet result = statement.executeQuery("SELECT name FROM sqlite_master WHERE type='table' AND name='T1'");
      if(!result.next()){
          statement.executeUpdate("create table T1 (email TEXT, gtse REAL, receiveddate DATE)");

      statement.executeUpdate("insert into T1 values(" + emailId + ", "+ gtse +", "+ receivedDate +")");      
      }
      else{

      }

    }
    catch(SQLException e)
    {
      // if the error message is "out of memory", 
      // it probably means no database file is found
      System.err.println(e.getMessage());
    }
    finally
    {
      try
      {
        if(connection != null)
          connection.close();
      }
      catch(SQLException e)
      {
        // connection close failed.
        System.err.println(e);
      }
    }
}

score 3 · Accepted Answer · answered Apr 07 '13 at 14:35

Your core error is that for the insert query you are not enclosing the values to be inserted, in quotes. Your query, after construction, looks something like this:

insert into T1 values(whatever@gmail.com, emailtexthere,  04-07-2013)

When it should be something like this:

insert into T1 values('whatever@gmail.com', 'emailtexthere',  '04-07-2013')

The SQL parser chokes while trying to parse your current query, because the syntax is incorrect. The solution to this problem is not simply to enclose the values in quotes though, but rather to use prepared statements. This is because the way you are constructing your query right now is vulnerable to SQL injection attacks. Here is an example of using a prepared statement:

PreparedStatement pStmt = conn.prepareStatement(
    "INSERT INTO T1 VALUES(?, ?, ?)");
pStmt.setString(1, emailId);
pStmt.setString(2, gtse);
pStmt.setDate(3, receivedDate);
pStmt.execute();

My current query looks like this because i am inserting variables to the database. insert into T1 values("emailId, gtse, receivedDate"). emailId, gtse and receivedDate are variables passed onto this method — Dan, Apr 07 '13 at 14:50
Using a prepared statement will also insert values into your database, but its safer and less prone to the kind of errors your are encountering. — Perception, Apr 07 '13 at 14:52

Inserting email in SQLite database using JDBC

1 Answers1