CODEWITHSUNDEEP
pythonmysqlsql-injectionpymysql
Stian
Stian

Reputation: 1391

Optional WHERE conditions in pymysql execute

I have a mysql SELECT query I want to execute with python using pymysql. To avoid duplication I want the method to take some optional arguments that should define whether or not the SELECT should use them as WHERE conditions.

Minimal example:

def select_from_db(arg1 = None, arg2 = None):
    db_connection = connect_to_db()
    cursor = db_connection.cursor()
    where_condition_one = ' and arg1 = %s'.format(arg1) if arg1 else ''
    where_condition_two = ' and arg2 = %s'.format(arg2) if arg2 else ''
    cursor.execute('SELECT * FROM tableName WHERE 1=1 ',(where_condition_one, where_condition_two)) 
    #Handle result...

The issue is that the resulting query with the call select_from_db() is: 

SELECT * FROM tableName WHERE 1=1 '' ''

While I want it to be:

SELECT * FROM tableName WHERE 1=1

I have tried using None instead of '', but then I get: 

SELECT * FROM tableName WHERE 1=1 None None

Is there any elegant way I can achive my goal with one execute statement without leaving myself open to sql-injection?

Upvotes: 0

Views: 1080

Answers (1)

Alex Hall
Alex Hall

Reputation: 36043

sql = 'SELECT * FROM tableName WHERE 1=1 '
args = []
if arg1:
    sql += ' and arg1 = %s'
    args.append(arg1)
if arg2:
    sql += ' and arg2 = %s'
    args.append(arg2)
cursor.execute(sql, args)

Or with less duplication:

sql = ['SELECT * FROM tableName WHERE 1=1']
args = []

def add_arg(name, value):
    if value:
        sql.append('and {} = %s'.format(name))
        args.append(value)

add_arg('arg1', arg1)
add_arg('arg2', arg2)

cursor.execute(' '.join(sql), args)

Upvotes: 4

Related Questions