tortoise-orm icon indicating copy to clipboard operation
tortoise-orm copied to clipboard

Fuzzy filter function translation to sql not escaping "%" in `LIKE` clause for `update` operations

Open brunobell opened this issue 3 years ago • 0 comments

Describe the bug Fuzzy filter function translation (like contains, icontains, startswith, istartswith, endswith, iendswith) to raw sql is not escaping % in the LIKE clause for update operations. This leads to error "not enough arguments for format string".

To Reproduce Down below is a demo to reproduce this bug:

from tortoise import Tortoise, fields, run_async
from tortoise.expressions import Q
from tortoise.models import Model


class Person(Model):
    id = fields.IntField(pk=True)
    id_num = fields.CharField(max_length=18, unique=True, index=True)
    name = fields.CharField(max_length=30, index=True)
    occupation = fields.CharField(max_length=40, index=True)
    alive = fields.BooleanField(index=True, default=True)


async def run():
    await Tortoise.init(db_url="mysql://localhost:3306/", modules={"models": ["__main__"]})

    sql = Person.filter(
        Q(name__startswith="John") &
        Q(id_num__contains="123") &
        Q(occupation__endswith="Doctor")
    ).update(alive=False).sql()
    print(sql)


if __name__ == "__main__":
    run_async(run())

The raw sql string is as below: 'UPDATE `person` SET `alive`=%s WHERE CAST(`name` AS CHAR) LIKE \'John%\' AND CAST(`id_num` AS CHAR) LIKE \'%123%\' AND CAST(`occupation` AS CHAR) LIKE \'%Doctor\'' The printed output of the above code is as below: UPDATE `person` SET `alive`=%s WHERE CAST(`name` AS CHAR) LIKE 'John%' AND CAST(`id_num` AS CHAR) LIKE '%123%' AND CAST(`occupation` AS CHAR) LIKE '%Doctor'

Tortoise-orm will automatically format the sql with value of alive field, but there are other unescaped % characters.

Expected behavior The % sign in above fuzzy filter translation in update operations is escaped correctly.

Additional context None

brunobell avatar Aug 21 '22 02:08 brunobell