0

I have a an SQLite query I'm attempting to run however it is force closing due to an apostrophe in my title string. I've attempted to resolve this by using title.replace("'", "''") and title.replace("'", "\\") as well as title.replaceAll("'", "\"); however it is still force closing due to the apostrophe - and when I debug - the apostrophe still exists in title - can anyone point on what I may have done wrong here?

Source Snippet:

  String title = info.get(MeetingInfo.MEETING_TITLE);

  String selectionClause = Events.DTSTART + " = '" + startTime + "' AND "
                    + Events.DTEND + " = '" + endTime + "' AND "
                    + Events.TITLE + " = '" + title.replace("'", "\\") + "'";
Christopher Hammond
  • 31
  • 6
  • `title.replace("'", "''")` should be working fine. Can you provide code with actual data of input and output which suggest that it doesn't work as you need? BTW manually appending parameters to SQL string template is bad idea. If you want to prevent SQL-injection you should be using PreperedStatement. – Pshemo Dec 05 '14 at 21:35
  • Does this help? :) http://pastebin.com/85dddrYq – Christopher Hammond Dec 05 '14 at 21:37
  • 1
    Building SQL statements from strings like this was the #1 source of security issues in 2013 (according to OWASP). Use [prepared statements](http://stackoverflow.com/questions/433392/how-do-i-use-prepared-statements-in-sqlite-in-android) instead. – that other guy Dec 05 '14 at 21:39
  • you have `title.replaceAll("'", "")` there instead of `title = title.replaceAll("'", "")` – Ruslan Ostafiichuk Dec 05 '14 at 21:39
  • @RuslanOstafiychuk - I attempted to implement it as you suggested however I still get a ' in my title String and it is still force closing: pastebin.com/ZqY1tyFK – Christopher Hammond Dec 05 '14 at 21:50
  • "Does this help?" yes, it shows exactly where your problem is :) From your question it may seem that you are asking about `+ title.replace("'", "\\") + "'";` part of your query, but problem is before it: you are calling `title.replaceAll("'", "");` but you don't store anywhere result of it (remember that Strings are immutable so methods called on them are not modifying original string, but create new one with changes you wanted) so you probably wanted `title = title.replaceAll("'", "");`. But again, **don't create your own SQL injection safety mechanism. Use PreparedStatement**. – Pshemo Dec 05 '14 at 21:52
  • I attempted that as @RuslanOstafiychuk and yourself suggested - pastebin.com/ZqY1tyFK but I still have the same issue - any ideas what may be wrong now that I've updated it as such? – Christopher Hammond Dec 05 '14 at 21:56
  • any ideas? I fail to understand why it does not replace or remove the apostrophe in this example: pastebin.com/ZqY1tyFK – Christopher Hammond Dec 05 '14 at 22:36

1 Answers1

3

You should use parametrized commands:

Cursor res = 
    db.rawQuery("SELECT * FROM Events WHERE DTSTART = ? AND DTEND = ? AND TITLE = ?;",
                new String[]{ startTime, endTime, title });

This help to avoid SQL injections and frees you from having to format the parameters the right way. I.e., you don't have to format dates as SQLite expects them, you don't have to care about apostrophes, you don't have to care about culture specific number formattings etc. and of course it is easier to write, read and maintain.

CL.
  • 158,085
  • 15
  • 181
  • 214
Olivier Jacot-Descombes
  • 86,431
  • 10
  • 121
  • 160
  • Ok. Thanks!! Does this seem correct to you? Original Source: http://pastebin.com/FRsEud8Z After implementing your suggestion: http://pastebin.com/p6sZwSHF – Christopher Hammond Dec 05 '14 at 22:07
  • I am not a java android guy, so I can't test it or give you more details, sorry. What happens doesn't it work? Are you getting an exception? – Olivier Jacot-Descombes Dec 05 '14 at 22:15
  • Yes - android.database.sqlite.SQLiteException: near "s": syntax error (code 1): , while compiling: SELECT _id FROM view_events WHERE (lastSynced = 0 AND (dtstart = '1417815000000' AND dtend = '1417818600000' AND title = 'User's')) – Christopher Hammond Dec 05 '14 at 22:30
  • @ChristopherHammond: This is what I meant yesterday, while talking about **bound parameters**. But you closed the question... – Phantômaxx Dec 06 '14 at 11:10
  • See: [Here](http://stackoverflow.com/a/12901086/880990) they use just `query` instead of `rawQuery`. I never used the Android Java framework, so I cannot really tell. [Here](http://stackoverflow.com/a/12561278/880990) `rawQuery` is suggested again. [Here](http://stackoverflow.com/a/25365773/880990) too and the comment says "If the error still shows the old query, it's not from the new code.". – Olivier Jacot-Descombes Dec 06 '14 at 15:51