[Flask] Problem with sqlite3 LIKE clause

[Anthony Ford]

Glad to help!

Personally, it looks like a weird implementation issue. % and _ are clearly
constructs in SQLite (and SQL in general), so the parsing should be able to
handle wildcards near the substitution character, but it apparently can't.

The sheer number of people asking that question on Stack Overflow makes me
feel like it's something that should be considered a bug, but it's
apparently been that way for a while, so I doubt that's gonna change.

Good luck with Flask!

Anthony Ford,
KF5IBN,
ford.anthonyj at gmail.com

On Tue, Jan 10, 2017 at 12:10 PM, Tom via Flask <flask at python.org> wrote:

> Thanks Anthony! A good 'Doh' moment there. Spent so much time trying to
> get the parameter substitution bent to my will it looks like I was even
> trying to throw 'C' at it in the end!
> Tom
>
> On 10/01/17 16:30, Anthony Ford wrote:
>
> I think the issue is it's not seeing the % as the wildcard. If you add the
> wildcard chars to the search string before you supply it to execute, it
> works.
>
> There's a few Stack Overflow questions on this same topic:
> <http://stackoverflow.com/questions/3105249/python-sqlite-parameter-substitution-with-wildcards-in-like>
> http://stackoverflow.com/questions/3105249/python-
> sqlite-parameter-substitution-with-wildcards-in-like
>
> Try:
>
> searchstring = '%'+request.form['searchstr']+'%'
>
> cur = g.db.execute('select * from entries where description like (?) order
> by id desc',(searchstring,))
>
> The only change is adding the '%' surrounding your search string.
>
>
> Anthony Ford,
> KF5IBN,
> ford.anthonyj at gmail.com
>
> On Tue, Jan 10, 2017 at 5:37 AM, Tom via Flask <flask at python.org> wrote:
>
>> I'm trying to modify the flaskr tutorial to make a full blown wiki using
>> ckeditor and its mostly working ok - bar the search.
>> There is a field 'description' which is meant to contain words and
>> phrases for searching.
>> I wish to search a text field in the database but cannot get anything
>> other than the following to parse:
>>
>>     searchstring=request.form['searchstr'];
>>     cur = g.db.execute('select * from entries where description like (?)
>> order by id desc',(searchstring,))
>>
>> any attempts to add %'s etc seem lead to failure. I can build the sql
>> string myself but would like to make it safe.
>> Any ideas how to do this?
>> Tom
>> _______________________________________________
>> Flask mailing list
>> Flask at python.org
>> https://mail.python.org/mailman/listinfo/flask
>>
>
>
>
> _______________________________________________
> Flask mailing list
> Flask at python.org
> https://mail.python.org/mailman/listinfo/flask
>
>
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://mail.python.org/pipermail/flask/attachments/20170110/ab7476de/attachment.html>