Joining strings using logic conditions(for sql queries)

Question

I want to create an SQL query by concatenating strings but with conditions.

qs_str= 'Select *, from items ...'

The conditions:

if active:
            qs_str = ' '.join([qs_str, f'WHERE active=true'])
if is_parent:
          qs_str = ' '.join([qs_str, f'WHERE parent=true'])
if limit:
            qs_str = ' '.join([qs_str, f'WHERE LIMIT={limit}'])
.....

There is not really a fixed limit on how many conditions will be.

The issue is the WHERE clause, should be added only once(or not if there is no condition) and how to add AND, to know if a condition is already in place.

Because there is no theoretically limit for the number of conditions I can't using combinatorics ifs like(if is_active and is_parent) is not a solution.

Dont Create Sql Statements Using String Concat - ever. use parametrized queries. Google Sql Injection, view Exploits of a mum + understand its explanation for Exploits of a mum — Patrick Artner
– Patrick Artner, Commented Nov 3, 2018 at 11:06
Even if you had full control over your strings - using parametrized queries will make them faster - the SQL-Query-Optimizer can cache execution plans - if you provide a query with params it can reuse the execution plan for that query given other params. If you provide a strings w/o params multiple times, always adding other parameters into your string - it most probably won't reuse anything. — Patrick Artner
– Patrick Artner, Commented Nov 3, 2018 at 11:12
in this case the fields are boolean and number, besides having control, so I don't know how an sql injection can occur; but if you can provide some help to using parametrized query for what I need I will use — user3541631
– user3541631, Commented Nov 3, 2018 at 11:16

Odysseas · Accepted Answer · 2018-11-03 11:16:18Z

1

You can collect the conditions in a list, and then only append the "where" clause if the list is non-empty.

Use ' and '.join(conditions) to easily handle adding "and" exactly as needed.

def create_item_query(active=True, is_parent=True, limit=10):
    qs_str= 'select * from items'

    conditions = []

    if active:
        conditions.append('active=true')
    if is_parent:
        conditions.append('parent=true')
    if limit:
        conditions.append('LIMIT={limit:d}'.format(limit=limit))


    if conditions:
        qs_str += 'where ' + ' and '.join(conditions)

    return qs_str

print(create_item_query(True, True, 10))
print(create_item_query(True, False, None))
print(create_item_query(False, False, None))

Edit: I should point out that, as Patrick Artner mentioned in his comment, that it's not actually a good practice to build SQL queries as a string, due to the danger of SQL injection. The precise way on how to create your queries would then depend on the exact SQL library that you're using (a popular tool is SQLAlchemy).

edited Nov 3, 2018 at 11:16

answered Nov 3, 2018 at 11:03

Odysseas

1,08011 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user3541631 Over a year ago

the backend is Django, but I'm forced to use a raw query, because is more complex besides the conditions

Collectives™ on Stack Overflow

Joining strings using logic conditions(for sql queries)

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related