CODEWITHSUNDEEP
pythonsqlitenullsql-injection
Bentaiba Miled Basma
Bentaiba Miled Basma

Reputation: 587

Dynamically search for null in sqlite select query using python

I'm new to python and I want to do a similar query to this one:

_c.execute('select * from cases where bi = ? and age = ? and 
            shape = ? and margin = ? and density = ?',(obj['bi'], 
            obj['age'], obj['margin'], obj['density']))

When some of the parameters are None, for example obj['bi'] = None, the query searches for the row when bi = 'None'. But I want it to search for the row when: 'bi is NULL'

A possible solution is to verify the values of the parameters one by one in a sequence of if-elses. For example:

query = 'select * from cases where'

if obj['bi'] is None:
   query += ' bi is null'
else:
   query += ' bi = ' + str(obj['bi']) + ' and '
    ...
# do the same if-else for the other parameters
    ...

_c.execute(query)

But, it doesn't seem to me as the best solution. The question is, what is the best solution to the given problem and how to avoid SQL injections.

Upvotes: 3

Views: 2816

Answers (1)

Shawn
Shawn

Reputation: 52409

Okay, after firing up a python REPL and playing around with it a bit, it's simpler than I thought. The Python sqlite bindings turn a Python None into a SQL NULL, not into a string 'None' like it sounded like from your question. In SQL, = doesn't match NULL values, but IS will. So...

Given a table foo looking like:

a     | b
--------------
NULL  | 1
Dog   | 2

Doing:

c = conn.cursor()
c.execute('SELECT * FROM foo WHERE a IS ?', (None,))
print(c.fetchone())

will return the (NULL, 1) row, and

c.execute('SELECT * FROM foo WHERE a IS ?', ('Dog',))
print(c.fetchone())

will return the ('Dog', 2) row.

In other words, use IS not = in your query.

Upvotes: 4

Related Questions