A symbol ' gets an unexpected error

Question

I get unexpected error while executing next java code

String sql = "INSERT INTO fake_advert (content) VALUES ('" + content + "');";
System.out.println("sql:"+sql);
stmt.executeUpdate(sql);

output

sql:INSERT INTO fake_advert (content) VALUES ('Winni's');
org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (near "s": syntax error)

How to protect from such error while inserting values?

Answer 1

Escape the single quote

The single quote in the string "Winni's" is breaking the query. A simple solution is to escape the special character in your content string.

String modifiedContent = content.replace("'", "''");
String sql = "INSERT INTO fake_advert (content) VALUES 
('" + modifiedContent + "');";

In SQL, single quotes will be escaped by using double single quotes.

Using Prepared Statements

Prepared statements are preferred over executing a SQL string directly. The code would change to something like

String insertStr = "INSERT INTO fake_advert (content) VALUES (?)";
PreparedStatement insertFakeAdvert = con.prepareStatement(insertStr);

Then the values are supplied in place of the question mark placeholders (if there are any) before you can execute a PreparedStatement object like this

insertFakeAdvert.setString(1, content);