CODEWITHSUNDEEP
pythonsqlsqlalchemyprepared-statement
H. Tao
H. Tao

Reputation: 237

Parameterized query binding table name as parameter gives error

I am using a parametrized query utilizing Text object in SQLAlchemy and am getting an error. Working example:

import sqlalchemy as sqlal
from sqlalchemy.sql import text

    db_table = 'Cars'
    id_cars = 8
    query = text("""SELECT * 
                    FROM Cars 
                    WHERE idCars = :p2
                 """)
    self.engine.execute(query, {'p2': id_cars})

Example that produces sqlalchemy.exc.ProgrammingError: (pymysql.err.ProgrammingError) (1064, "You have an error in your SQL syntax)

import sqlalchemy as sqlal
from sqlalchemy.sql import text

    db_table = 'Cars'
    id_cars = 8
    query = text("""SELECT * 
                    FROM :p1 
                    WHERE idCars = :p2
                 """)
    self.engine.execute(query, {'p1': db_table, 'p2': id_cars})

How I can run the query with a dynamic table name that is also protected from SQL injection?

Upvotes: 19

Views: 5547

Answers (3)

snakecharmerb
snakecharmerb

Reputation: 55669

If you are not absolutely required to use the complete text query you can use a table object to define the from clause and SQLAlchemy will quote the table name if required. There where clause (and order by etc.) can be left as text, although they must be placed within the appropriate method calls.

import sqlalchemy as sa

...

query = sa.select(sa.text('*')).select_from(sa.table(db_table)).where('idCars = :p2')

with engine.connect() as conn:
    rows = conn.execute(query, {'p2': id_cars})

If you can afford the upfront cost of table reflection you can use a Table object instead, which will give you an exception at reflection time if there is no table with the provided name.

...

cars = sa.Table(db_table, sa.MetaData(), autoload_with=engine)

query = sa.select(cars).where(sa.text(idCars = :p2))
# or

query = sa.select(cars).filter_by(**{
...

Once you have a Table instance you have access to the full power of the SQLAlchemy (Core) API, should you want to use it.

Upvotes: 1

maciek
maciek

Reputation: 3354

I use PostgreSQL and psycopg2 backend. I was able to do it using:

from psycopg2 import sql
from sqlalchemy import engine

connection: sqlalchemy.engine.Connection
connection.connection.cursor().execute(
    sql.SQL('SELECT * FROM {} where idCars = %s').format(sql.Identifier(db_table)),
    (id_cars, )
)

In PyMySQL it's not supported.

Upvotes: 9

RenauV
RenauV

Reputation: 383

You could just use the benefits of writing in python:

Library to use:

import sqlalchemy
from sqlalchemy import create_engine, MetaData, Table, func, event
from sqlalchemy.sql import text
from urllib.parse import quote_plus

connection (that I did not see in your example - here connection with sql azure):

params = urllib.parse.quote_plus(r'...')
conn_str = 'mssql+pyodbc:///?odbc_connect={}'.format(params)
engine_azure = create_engine(conn_str, echo=True)

Your example:

db_table = 'Cars'
id_cars = 8
query = text('SELECT * FROM ' + db_table + 'WHERE idCars = ' + id_cars)
connection = engine.connect()
connection.execute(query)
connection.close()

Upvotes: -5

Related Questions