2

I want the user to be able to choose what order results are displayed e.g. by age), and I don't want to sort them after getting them from the database.

Obviously if the user is able to specify input that affects SQL commands, it needs to be sanitised, and I would normally use parameterisation, but pysqlite seems to ignore parameters for anything except values.

Example code is below showing parameterisation not working for ORDER BY, and also a workaround using string formatting, but that is vulnerable to SQL injection.

What is the recommended solution to allow user input to affect sort order without exposing SQLi vulnerabilities? Do I have to use string formatting and check every user input manually?

#!/user/bin/env python3

import sqlite3

con = sqlite3.connect(':memory:')
cur = con.cursor()
cur.execute('CREATE TABLE test (name, age)')
cur.execute('INSERT INTO test VALUES (:name, :age)', {'name': 'Aaron', 'age': 75})
cur.execute('INSERT INTO test VALUES (:name, :age)', {'name': 'Zebedee', 'age': 5})

cur.execute('SELECT * FROM test ORDER BY age ASC')
results = cur.fetchall()
print('\nGood, but hard coded:\n', results)
# Good, but hard coded:
#  [('Zebedee', 5), ('Aaron', 75)]

cur.execute('SELECT * FROM test ORDER BY :order_by ASC', {'order_by': 'age'})
results = cur.fetchall()
print('\norder_by parameter ignored:\n', results)
# order_by parameter ignored:
#  [('Aaron', 75), ('Zebedee', 5)]

cur.execute('SELECT * FROM test ORDER BY {order_by} ASC'.format(order_by='age'))
results = cur.fetchall()
print('\nRight order, but vulnerable to SQL injection:\n', results)
# Right order, but vulnerable to SQL injection:
#  [('Zebedee', 5), ('Aaron', 75)]

con.close()
Grezzo
  • 2,220
  • 2
  • 22
  • 39
  • BTW, I just tried using SQLi with this input `'age; DROP TABLE test;--'` to the third SQL command and pysqlite raised an exception saying `sqlite3.Warning: You can only execute one statement at a time.`, so maybe I shouldn't be worrying? It still seems to me like using string formatting is dangerous though... – Grezzo Jan 25 '17 at 11:28
  • Can the user affect the actual contents of the string in any way? (In the question, you're using the constant string `'age'`, which does not make sense.) – CL. Jan 25 '17 at 12:24
  • It's coming from an http request. If they are using the proper client (a web page), it should be any 1 of 6 column names, but if someone decides to be malicious, it could be anything they choose. – Grezzo Jan 25 '17 at 12:27

1 Answers1

3

SQL parameters are used only for values; anything else could change the meaning of the query. (For example, ORDER BY password could leave hints, as could ORDER BY (SELECT ... FROM OtherTable ...).)

To ensure that the column name from the client is valid, you could use a whitelist:

if order_by not in ['name', 'age']:
    raise ...
execute('... ORDER BY {}'.format(order_by))

But it is still a bad idea to integrate that string into the query, because the validation and the actual table could go out of sync, or you could forget the check. Better return a column index from the client, so that the actual string you're using is always your own, and any errors can be easily found during normal testing:

order_by = ['name', 'age'][order_index]
execute('... ORDER BY {}'.format(order_by))
CL.
  • 173,858
  • 17
  • 217
  • 259
  • Use a constant to give the values of `order_index` names (like `NAME_COLUMN = 0`). That way, you can easily add more columns or move them around plus you can see what kind of ordering you want. – Aaron Digulla Jan 25 '17 at 12:53
  • Also note that you can easily create more complex orderings like `name ASC, age DESC` by giving them their own index. – Aaron Digulla Jan 25 '17 at 12:54
  • Great answer, especially the example the ordering by columns could give password hints. Thanks. Whitelist is is then. – Grezzo Jan 25 '17 at 13:01