Exception in thread "main" java.sql.SQLException: near "s": syntax error

Question

I wrote a program that populates a database with a list of words. Problem is, it throws up the "Exception in thread "main" java.sql.SQLException: near "s": syntax error" every time I try to run the code. I realize similar questions have been asked on this forum, but in the context of MY code I am unable to rectify the error.

Thus I turn to you for help.

This is the code

import java.io.*;
import java.sql.*;
import java.util.Scanner;

public class db_populate {

    public static void main(String[] args) throws SQLException, IOException,
            ClassNotFoundException {
        Connection c = null;
        Statement stmt = null;
        Scanner in = null;
        String word;
        String wordcat;
        String j, sql;
        int i = 0;

        Class.forName("org.sqlite.JDBC");
        c = DriverManager.getConnection("jdbc:sqlite:dictionary.db");
        c.setAutoCommit(false);
        System.out.println("Opened database successfully");

        stmt = c.createStatement();

        in = new Scanner(new File("wordsEn.txt"));

        while (in.hasNext()) {
            word = in.nextLine();
            i++;
            j = Integer.toString(i);
            wordcat = word.substring(0, 2);
            sql = "INSERT INTO WORDS (ID,CAT,WORD) " + "VALUES(" + j + ",'"
                    + wordcat + "','" + word + "');";
            stmt.executeUpdate(sql);
        }

        stmt.close();
        c.commit();
        c.close();
        in.close();
        System.out.println("Records created successfully");
    }

}

and these are the errors I get when I run.

Opened database successfully
Exception in thread "main" java.sql.SQLException: near "s": syntax error
    at org.sqlite.core.NativeDB.throwex(NativeDB.java:397)
    at org.sqlite.core.NativeDB._exec(Native Method)
    at org.sqlite.jdbc3.JDBC3Statement.executeUpdate(JDBC3Statement.java:116)
    at db_populate.main(db_populate.java:34)

Can you print the sql string so we see what you are passing to stmt.executeUpdate() ? — Wes, Apr 18 '14 at 16:50
There is no validation when creating your connection. Are you certain that you have a good connection? Also, you should consider surrounding your statement execution with TRY/CATCH. — wyoskibum, Apr 18 '14 at 16:59
Why `j=Integer.toString(i);`? `j` is supposed to be an `int`. I think instead of `j` keepi9ng `i` in query could solve your issue. _PS:_ Moreover at the end of `sql` you dont need `;` — Smit, Apr 18 '14 at 16:59
One of the words that you're inserting contains an apostrophe e.g. `here's`, as a result the SQL statement contains a syntax error. You really should use a prepared statement, as your code is subject to SQL injection attacks. If you don't want to use a prepared statement, you need to [escape all single quotation marks with another one](http://stackoverflow.com/questions/603572/how-to-properly-escape-a-single-quote-for-a-sqlite-database) — Petesh, Apr 18 '14 at 17:00

score 6 · Accepted Answer · answered Apr 18 '14 at 16:49

6

Use PreparedStatement to avoid problems with erroneous input:

PreparedStatement p = c.prepare("INSERT INTO WORDS (ID,CAT,WORD) VALUES(?, ?, ?)");
p.setInt(1, i);
p.setString(2, wordcat);
p.setString(3, word);
p.execute();
//commit results if using InnoDB, etc

answered Apr 18 '14 at 16:49

Rogue

9,871
4
37
66

No downvote from me, but elaborate: like word being `"don't".` Escaping, formats (Date) and SQL injection: http://xkcd.com/327/ – Joop Eggen Apr 18 '14 at 17:21
Well, @Rogue and everyone else who answered, thanks...it worked! The PreparedStatement tip really helped, thanks for that! That INSERT string was a mess of quotes and this way is a lot neater. Thanks for all your help! – CaladanBrood Apr 18 '14 at 18:05
@JoopEggen from my understanding PreparedStatement lets the database handle the escaping of the parameters itself, so there's no need to escape in that form. – Rogue Apr 18 '14 at 19:21
@Rogue yes, I meant to mention the advantages _like escaping_ etcetera. (Sorry for the unclear formulation.) – Joop Eggen Apr 19 '14 at 07:07

score 0 · Answer 2 · answered Oct 30 '14 at 12:28

Thanks Rogue for a good code. I had an error in SQLite during INSERT operation, SQL string contained "'" inside a text. So, an escaping in this way helped to me. I've changed a code a bit.

PreparedStatement prst = conn.prepareStatement(
    "INSERT INTO test(id, name, type) "
    + "VALUES(?, ?, ?)");
prst.setLong(1, id);
prst.setString(2, title);
prst.setObject(3, type);
prst.execute();

So when I tried to insert a null value in INTEGER column, I used a method setObject().

Exception in thread "main" java.sql.SQLException: near "s": syntax error

2 Answers2

Linked

Related