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 column. ex: name column have following: %a a a%a aa% 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 Deepak _______________________________________________ sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users |
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, ie. sqlite3_prepare(db, "SELECT NAME FROM AUDIO WHERE NAME LIKE ?", -1, &stmt, NULL); sprintf(acGlobString, "%%%s%%", acInputString); sqlite3_bind_string(stmt, 1, acGlobString); sqlite3_step(stmt); 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 course. 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. -Rowan 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 > column. > > > ex: name column have following: > > %a > > a > > a%a > > aa% > > > 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 > > Deepak > _______________________________________________ > sqlite-users mailing list > [hidden email] > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users |
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 Rowan's method... "instr(UPPER(NAME), UPPER(?)) > 0" (Or LOWER(), if you so wish). _______________________________________________ sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users |
Hi,
Thanks a lot for the support. 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 Deepak ________________________________ 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 Rowan's method... "instr(UPPER(NAME), UPPER(?)) > 0" (Or LOWER(), if you so wish). _______________________________________________ sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users _______________________________________________ sqlite-users mailing list [hidden email] http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users |
Free forum by Nabble | Edit this page |