CODEWITHSUNDEEP
pythonsqlite
rwolst
rwolst

Reputation: 13682

Python and Sqlite3, concatenate passed strings

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?

Upvotes: 0

Views: 760

Answers (1)

Martijn Pieters
Martijn Pieters

Reputation: 1123400

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]))

Upvotes: 4

Related Questions