Re: [sqlite] Search % using sqlite

[Hegde, Deepakakumar (D.)]

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 <sqlite-users-boun...@mailinglists.sqlite.org> on behalf of 
R Smith <ryansmit...@gmail.com>
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

[R Smith]

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

[Rowan Worth]

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, ,
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