Re: [sqlite] Search % using sqlite
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 on behalf of R Smith Sent: Friday, January 5, 2018 3:41:35 PM To: sqlite-users@mailinglists.sqlite.org 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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Search % using sqlite
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.) < deep...@allgosystems.com> 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 > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Search % using sqlite
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 sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users