CODEWITHSUNDEEP
javasqlsqlite
Builder_20
Builder_20

Reputation: 31

SQL Insert with '

i am trying to insert a String with sql into my database. This String may contain a " ' ".

When i now try to insert it into my database i get this error:

[SQLITE_ERROR] SQL error or missing database (near " characters after the ' ": syntax error)

Function call:

  Main.execute("INSERT INTO Projektname(projektname) VALUES('" + txtFd_name.getText() + "');");

Main.execute: 

public static void execute(String query){
        class.forName("org.sqlite.JDBC");
        Connection conn = DriverManager.getConnection("jdbc:sqlite:D:\\user\\eclipse-workspace\\S2LDB.db");
        Statement stat = conn.createStatement();
        stat.execute(query);
}

i tried it with org.apache.commons.text.StringEscapeUtils.escapeJava()

Upvotes: 1

Views: 253

Answers (3)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522719

You should absolutely be using a prepared statement here, which would handle the problem of properly escaping a single quote when doing an insert. Something like this:

String sql = "INSERT INTO Projektname(projektname) VALUES(?)";

try (Connection conn = this.connect();
    PreparedStatement pstmt = conn.prepareStatement(sql)) {
        pstmt.setString(1, txtFd_name.getText());
        pstmt.executeUpdate();
} catch (SQLException e) {
    System.out.println(e.getMessage());
}

If you must continue with your current approach, then you might able to try manually doubling up all the single quotes. So this might make your current insert work:

String name = txtFd_name.getText().replaceAll("'", "''");
String sql = "INSERT INTO Projektname(projektname) VALUES('" + name + "');");
Main.execute(sql);

But, I don't recommend doing this, because this could backfire depending on how many single quotes you already have. Using a prepared statement is the better way to go.

Upvotes: 9

Paplusc
Paplusc

Reputation: 1130

I fix this writing two ' insted of one. Something like this:

Main.execute("INSERT INTO Projektname(projektname) VALUES(''" + txtFd_name.getText() + "'');");

Upvotes: 0

Davide Piras
Davide Piras

Reputation: 44605

you should escape the ' with a double single quote, before using this value in your string composition

txtFd_name.getText()

assign the value to a string variable and then replace ' with '' ( twice ' ) and it should work.

see here for an example: Escape single quote character for use in an SQLite query

if it was sql server and .NET I would suggest to use query or command parameters not sure about java and sqlite

Upvotes: 0

Related Questions