Skip to content

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

@brunobell

Description

@brunobell

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

Metadata

Metadata

Assignees

No one assigned

    Labels

    No labels
    No labels

    Type

    No type

    Projects

    No projects

    Milestone

    No milestone

    Relationships

    None yet

    Development

    No branches or pull requests

    Issue actions