sqlite like query causing crash

Question

Hi every one I am not much aware with the sqlite in ios. I am using a query for searching a word/sentence. Below is my sample code for retrieving record

        -(NSArray *)prepareSearchResultsForQuery:(NSString *)searchString{

        searchQueryString = searchString;

        sqlite3_stmt *searchResultsStmt;
        NSMutableArray *arrayOfSearchResults;
       NSString *searchResultsQuery = [NSString stringWithFormat:@"SELECT * FROM Search WHERE pageText LIKE '%%%@%%'",searchString];
        if (sqlite3_prepare_v2(bookDB,[searchResultsQuery UTF8String],-1,&searchResultsStmt,nil) == SQLITE_OK) {
            arrayOfSearchResults = [[NSMutableArray alloc]initWithCapacity:1];

            while (sqlite3_step(searchResultsStmt) == SQLITE_ROW) {
                XPathSearchResult *xpathObj = [[XPathSearchResult alloc] init];
                xpathObj.targetPageIndex = sqlite3_column_int(searchResultsStmt, 2);
                NSString *entirePageString = [[[NSString alloc] initWithUTF8String:(const char*)sqlite3_column_text(searchResultsStmt, 4)] autorelease];
                xpathObj.stringContainingSearchQuery = [self getDisplayStringFromPageString:entirePageString];

                [arrayOfSearchResults addObject:xpathObj];
                [xpathObj release];

            }
        }
        sqlite3_finalize(searchResultsStmt);
        return arrayOfSearchResults; 
    }
    }

The above code is working for me until there are words like it's what's earth's

if the word contains ' "" < character my method not working. And now I came across that we need to use the prepared statement to come out of from this issue.Can any one suggest me how to use prepared statement in this situation.Thanks in advance

If the searchString contains `'` it must be doubled. You can also try `@"SELECT * FROM Search WHERE pageText LIKE \"%%%@%%\""`. — ott--, Sep 05 '12 at 13:39

score 0 · Answer 1 · answered Sep 05 '12 at 13:33

0

The app is crashing because you have unescaped escape characters (in this case single quotes). Adding this line:

[sqlQuery stringByReplacingOccurrencesOfString:@"'" withString:@"\\'"];

should prevent crashes caused by single quotes. You could also create a regex to remove escape all such characters.

answered Sep 05 '12 at 13:33

James

2,272
1
21
31

is there a general method to do this, since sometimes it could be double quotation marks, and so on – LiangWang Mar 08 '13 at 10:38
create a regex to search for these characters and escape appropriately. See http://stackoverflow.com/questions/4353834/search-through-nsstring-using-regular-expression – James Mar 13 '13 at 22:05

sqlite like query causing crash

1 Answers1