Injection safe "SELECT FROM table WHERE column IN list;" using python3 and sqlite3

Question

I want to take a user input from an html form, and do a SELECT by matching a column to the user input, and be safe for injection. BUT I want the user input to be a comma separated list. For example, if the column is called "name" and user_input is "Alice,Bob,Carrol" I wan to execute the query

SELECT FROM table WHERE name IN ("Alice","Bob","Carrol");

Which means I have the same problem as in this question select from sqlite table where rowid in list using python sqlite3 — DB-API 2.0. But of course I do not want to do string concatenation myself to avoid injections. At the same time, because there could be any number of commas in user_input, I cannot do this:

db.execute('SELECT FROM table WHERE name IN (?,?,?)', user_input_splited)

I looked for a way to sanitize or escape the input by hand, to be able to do something like that:

db.execute('SELECT FROM table WHERE name IN ?', user_input_sanitized)

But I didn't find it. What's the best approach here?

Mike Robinson · Accepted Answer · 2021-03-10 19:11:23Z

1

Write your own code to take the user's input, split() it by comma, then iterate through that list. As you accept each value, push it onto one array, and push a literal "?" onto the other.

Of course, now verify that you have at least one acceptable value.

Now, construct your SQL statement by to include, say, join(", ", $qmarks_array) to automatically construct a string that looks like ?, ?, ? ... with an appropriate number of question-marks.(It won't insert any comma if there's only one element.) Then, having constructed the SQL statement in this way, supply the other array as input to the function which executes that query.

In this way, you supply each value as a parameter, which you should always do, and you allow the number of parameters to vary.

answered Mar 10, 2021 at 19:11

Mike Robinson

9,0516 gold badges33 silver badges50 bronze badges

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

1 Comment

amstramgram Over a year ago

Thanks. And now I realise this is also what that other page I linked actually says to do... String concatenation is unsafe but not in this case.

Zbigniew Lebiedz · Accepted Answer · 2023-12-06 23:52:07Z

The problem in sqlite is, you must give always the correct quantity of question marks in your inquiry.If you have a list or a tuple containg 3 items, you must ask for (?,?,?) items. If you ask only for one (?) you get an error like: sqlite3.ProgrammingError: Incorrect number of bindings supplied. The current statement uses 1, and there are 2 supplied. The solution is to find the quantity of items in the list and prepare a string with the correct quantity of qustion marks. Like in the code below:

    list1=[1,2,3,4,5]
    question_marks=len(list1)*'?,'
    selecting="select * from products where product_id in ("+question_marks[:-1]+")"
    curr.execute(selecting,list1)
    product_list=curr.fetchall()

Collectives™ on Stack Overflow

Injection safe "SELECT FROM table WHERE column IN list;" using python3 and sqlite3

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related