Python and Sqlite3, concatenate passed strings

Question

The following is a generalisation of my problem

Col_Index = 'Foo'
data = SQLfunctions.fetchSQL("SELECT Col_" + Col_Index + "FROM MyTable)

Where fetchSQL is a function that returns the values of the SQL statement. I tried to re-write this so as to pass the Col_Index using ? and concatenate the strings i.e.

data = SQLfunctions.fetchSQL("SELECT Col_ || ? FROM MyTable", [Col_Index])

But this did not work as it did not seem to perform the || with the ? (error something like column Col_ does not exist).

How do I change this to make it work?

Martijn Pieters · Accepted Answer · 2014-02-06 15:28:28Z

You cannot use SQL parameters for anything other than values.

You cannot use them for table names or columns or SQL functions or any other part of the SQL grammar, precisely because they are designed to prevent such use.

In other words, if SQL parameters could be used to produce SQL code (including object names), they would be useless for one of their main uses: to prevent SQL injection attacks.

You are stuck with generating that part of your query, I am afraid.

Do be very, very careful with user-provided data, don't ever take Col_Index from external sources without sanitizing and/or severely restricting the range of values it can hold.

You could look at SQLAlchemy to generate the SQL for you, as it ensures that any object names are properly escaped as well:

from sqlalchemy.sql import table, literal_column, select

tbl = table('MyTable')
column = literal_column('Col_' + Col_Index)
session.execute(select([column], '', [tbl]))

Collectives™ on Stack Overflow

Python and Sqlite3, concatenate passed strings

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related