Sqlite reading text with apostrophe

Question

I have a sqlite database which contains strings. I am reading those strings from my javafx interface. Everything is working smoothly, however my problem is when I am trying to read strings with apostrophe. My code for reading the strings is the following:

String sql = "select * from Questions where Subject = ? and Grade = ? and Level = ?  and questionId = ?";
PreparedStatement pst = gui.connectionQuestions.prepareStatement(sql);
pst.setString(1,gui.textSubjectQTest);
pst.setString(2,gui.showGradeLabel.getText());
pst.setString(3,gui.showCurrentLevelLabel.getText())
pst.setString(4,list.get(counter));
ResultSet rs = pst.executeQuery();
if(rs.next())
{
        String temp = rs.getString("Question");
        gui.question.setText(temp);
        ...
        sql = "Update Questions set Used ='"+1+"' where Question = '"+gui.question.getText().replaceAll("'", "/'")+"'";
        pst = gui.connectionQuestions.prepareStatement(sql);
        pst.execute();

In the above code I peform a query to return the question string and add it to a label gui.question. However due to the apostrophe I am receiving the following error (I got the error due to the last line):

[SQLITE_ERROR] SQL error or missing database (near "(": syntax error)

I tried to follow the solution from here, however my prob still remains. How can I solve the thing with the apostrophe?

EDIT: I tried to escape character using double apostrophe. This approach is working but it changes my string to double apostrophe, which is not useful.

Where are you getting the error, and what are the values here? — Jon Skeet, Sep 08 '16 at 08:53
I edit my question, I am receiving the error in the pst.execute(); — konstantin, Sep 08 '16 at 12:19
Ah - in the code that you hadn't posted at all before. Yes, you should use parameterized SQL in that as well. *Always* use parameterized SQL rather than including unknown values directly in the SQL string. It's unclear why you would use parameterized SQL for the first query, but then stop doing so for the second... — Jon Skeet, Sep 08 '16 at 12:26

score 1 · Accepted Answer · edited May 23 '17 at 12:15

1

The quote or apostrophe character is escaped by doubling it.

BTW You don't need the if (temp.contains("'")).

edited May 23 '17 at 12:15

Community

1
1

answered Sep 08 '16 at 08:56

SantiBailors

1,397
2
18
38

The double apostrophe is working, but it is a disaster for me since the questions is math questions and they change the meaning of the questions. The apostrophe is for derivatives. – konstantin Sep 08 '16 at 08:57
@konstantin Sorry, I have probably read your original question too quickly and misunderstood the problem. I'll try again later. BTW I recommend you to provide the info asked in the comment to your question. – SantiBailors Sep 08 '16 at 09:05
@konstantin I'm confused by your code example. Escaping quote chars is needed when creating an SQL statement. Instead you are escaping _after_ your SQL has been created and executed. So I don't get how any escaping or other changes made to your escaping code can affect the execution of your SQL. Your escaped string is only used to set a GUI field. You should really add the info asked in the comment to your question. – SantiBailors Sep 08 '16 at 10:44
The thing is that I have question in my database with apostrophe and when I am trying to read them using the select query I got problems. How can I read the questions proper, this is what I am asking. – konstantin Sep 08 '16 at 10:51
1

@konstantin _How can I read the questions proper, this is what I am asking._ Hard to tell until you disclose where are you getting the error and what are the values there. Having `'` characters in your database is very unlikely to create any "problem" whatsoever in reading the values, unless the SQL you use to do that has a `WHERE` condition containing a text value containing `'` (and that text value is the only possible place where it makes sense to talk about escaping). Your question is very unclear, it forces people to guess and probably give up. I recommend you to improve your question. – SantiBailors Sep 08 '16 at 11:46
Yes you are right, after my selsec I have an update with Question and there is where my problem rises. – konstantin Sep 08 '16 at 12:03
@konstantin You shouldn't escape the string before setting it into the GUI. In fact you shouldn't escape it anywhere at all except in the string containing the `UPDATE` statement. BTW you are using bind variables (the `?`) in your `SELECT`, why not doing the same in the `UPDATE` instead of embedding the variable values in the SQL ? – SantiBailors Sep 08 '16 at 12:27
I tried to do the escape in the update query ...sql = "Update Questions set Used ='"+1+"' where Question = '"+gui.question.getText().replaceAll("'", "/'")+"'"; and I got the same problem. – konstantin Sep 08 '16 at 12:42
1

@konstantin Probably because you are still escaping with the `/` instead of doubling the `'`. You didn't read the answer I linked in my answer. Do that; at least the documentation quote it contains. It seems to me that you are too eager to get a result and not too interested in understanding. Don't lean too much towards trial-and-error. And mind the suggestions people bothered to give you about using parameters instead of values put directly in the SQL. – SantiBailors Sep 08 '16 at 12:58
Yes you are right. Everything is okay now. Thanks. I think my question now seems more proper. – konstantin Sep 08 '16 at 13:23

Sqlite reading text with apostrophe

1 Answers1