SQL Insert with '

Question

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()

With a `PreparedStatement` you can use [`setString`](https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html#setString(int,%20java.lang.String)), and you will be protected from SQL injection. You should not be trying to glue input directly into your query. — khelwood, Jul 13 '18 at 11:49
Yes, `escapeJava` is unnecessary if you're using statement parameters properly. — khelwood, Jul 13 '18 at 11:57

Tim Biegeleisen · Accepted Answer · 2018-07-13T11:53:00.897

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.

@Codo It's a miracle we don't all make more mistakes here than we already do :-) — Tim Biegeleisen, Jul 13 '18 at 11:53

score 0 · Answer 2 · answered Jul 13 '18 at 11:50

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

score 0 · Answer 3 · answered Jul 13 '18 at 11:52

0

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

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

answered Jul 13 '18 at 11:52

Paplusc

1,080
1
12
24

SQL Insert with '

3 Answers3