Firstly this code is extremely dangerous. What would happen if
acInputString contained this string?
';DROP TABLE AUDIO; SELECT '
It's best practice to use bound parameters to prevent this kind of problem,
sqlite3_prepare(db, "SELECT NAME FROM AUDIO WHERE NAME LIKE ?", -1, &stmt,
sprintf(acGlobString, "%%%s%%", acInputString);
sqlite3_bind_string(stmt, 1, acGlobString);
Which is still dangerous if acInputString is larger than can fit in
acGlobString -- snprintf is advised to avoid buffer overflow. And all
sqlite3 return codes should be checked to see if an error occurred, of
Ok now to the actual problem -- you can modify your query to read:
SELECT NAME FROM AUDIO WHERE NAME LIKE ? ESCAPE '!'
The ESCAPE clause defines a character which can be used to match a literal
% instead of % being treated as a wildcard. I've chosen ! as the escape
character, which means you'll have to prefix all !, %, and _ characters
with an ! to get a literal match.
For this simple search it's easier to replace "NAME LIKE ?" with
"instr(NAME, ?) > 0", unless you need case insensitive matching.
On 5 January 2018 at 15:49, Hegde, Deepakakumar (D.) <
[hidden email]> wrote:
> Hi All,
> We are implementing a wild card search feature. our query is as below:
> sprintf (acQstring,
> "SELECT NAME FROM AUDIO WHERE NAME LIKE '%%%s%%'", acInputString);
> In the input string is '%' then we are getting t all the entry in the
> ex: name column have following:
> we are expecting entry 2 which don't have % in it should not get as
> output. But it seems not the case, it is giving all the 4 entry as output.
> Please can we know is there any way of searching this? Thanks.
> Thanks and Regards
> sqlite-users mailing list
> [hidden email] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >
We are actually using sqlite3_snprintf() with the proper buffer size to make sure that overflow will not happen. and also bind operations.
INSTR() seems to be the best option. It seems to be fine. Thanks a lot.
Thanks and Regards
From: sqlite-users <[hidden email]> on behalf of R Smith <[hidden email]>
Sent: Friday, January 5, 2018 3:41:35 PM
To: [hidden email] Subject: Re: [sqlite] Search % using sqlite
On 2018/01/05 10:14 AM, Rowan Worth wrote:
> For this simple search it's easier to replace "NAME LIKE ?" with
> "instr(NAME, ?) > 0", unless you need case insensitive matching.
And in case you do wish for Case-Insensitive matching while using