Search % using sqlite

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

Search % using sqlite

Deepak Hegde
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
Reply | Threaded
Open this post in threaded view
|

Re: Search % using sqlite

Rowan Worth-2
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
Reply | Threaded
Open this post in threaded view
|

Re: Search % using sqlite

R Smith-2

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
Reply | Threaded
Open this post in threaded view
|

Re: Search % using sqlite

Deepak Hegde
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