Fillo library shows Invalid query, if the query has ' single quote in where condition value or while updating any cell

Question

Running below query:

String query = "Select * from Messages Where message='Let's hangout somewhere.'";

Above query gives error saying, Invalid query.

Please let me know if any of you guys have came around this and share some fix or any work arounds.

Maven dependency :

<!-- https://mvnrepository.com/artifact/com.codoid.products/fillo -->
<dependency>
    <groupId>com.codoid.products</groupId>
    <artifactId>fillo</artifactId>
    <version>1.22</version>
</dependency>

https://codoid.com/products/fillo/

Thanks in advance.

On removing the single quote ' from "Let's hangout somewhere." which makes it: "Lets hangout somewhere." and this is working completely fine.

Tried escaping the ' single quote, but still getting same error.

Answer 1

The better way is to use a prepared statement so that you don't have to worry about handling special characters.

    String message = "Let's hangout somewhere.";
    String query = "Select * from Messages Where message=?";
    Fillo fillo = new Fillo();
    Connection connection = fillo.getConnection("<excel_file_path>");
    PreparedStatement preparedStatement = connection.prepareStatement(query);
    preparedStatement.setString(1, message);
    ResultSet resultSet = preparedStatement.executeQuery();

If for some reason you can't use PreparedStatement then you use double single quotes for escaping it.

    String message = "Let''s hangout somewhere.";
    String query = "Select * from Messages Where message='" + message + "'";
    Fillo fillo = new Fillo();
    Connection connection = fillo.getConnection("<excel_file_path>");
    ResultSet resultSet = connection.executeQuery(query);