Escaping single quotes in SQLite
I’m working on storing all tweets viewed in DestroyTwitter into a local SQLite database to improve performance with filtering and paging. I’ve already been using databases in order for Groups to work, but to avoid characters that might be trouble for the SQLite queries, I would simply run the escape() method to URL-encode the string. Lately, I’ve been on this testing-high, making sure every bit of code I write is the most efficient to my knowledge. After testing escape() vs String.replace(), I realized that the latter is ten times faster. Of course, it makes perfect sense.
My lazy self months ago used escape() without thinking, but now that I know the incredible speed difference, I’ve created an addSlashes() method to use a regular expression to prefix each single quote with a slash (\). This resulted in an error. After a bit of research aka Googling, I came across a mail archive that said that SQLite escapes single quotes a bit differently. Instead of a slash, you must escape it with another single quote. In all honesty, this makes no sense to me, but it works. Here are examples of what works and what doesn’t:
// Wrong – unescaped apostrophe
INSERT INTO tablename (id, name, text) VALUES (1, ‘Jonnie’, ‘That’s what she said’);
// Wrong – incorrectly escaped apostrophe
INSERT INTO tablename (id, name, text) VALUES (1, ‘Jonnie’, ‘That\’s what she said’);
// Correct – unescaped apostrophe
INSERT INTO tablename (id, name, text) VALUES (1, ‘Jonnie’, ‘That”s what she said’);
Please note that the correct query above uses two separate single quotes, not one double quote.
