Setting input parameter types in SQLAlchemy raw SQL

Question

I want to execute some raw SQL and take advantage of SQLAlchemy's SQL injection prevention.

My code is similar to this bit:

import sqlalchemy
from sqlalchemy.sql import text

DB_URI = '...'
engine = sqlalchemy.create_engine(DB_URI)

sql = text("SELECT * FROM some_table WHERE userid = :userid")
res = engine.execute(sql, userid=12345)
# do something with the result...

The problem is that userid in some_table is of type varchar. All I want to do is to tell SQLAlchemy to convert 12345 to a string before executing the statement. I know how I could do the conversion both in Python and in SQL. But I remember that I once used a explicit type definition in SQLAlchemy, I just can't find it anymore. Can someone point me to the right direction? (My actual question involves postgresql arrays of BIGINTs vs. INTs, but I tried to keep it simple.)

Thanks for any help!

asPlankBridge · Accepted Answer · 2017-01-24 09:03:10Z

6

The question is a bit old already, but knowing myself, I will probably come back for this in future. This was the bit of code I was looking for:

import sqlalchemy
from sqlalchemy.sql.expression import bindparam
from sqlalchemy.types import String
from sqlalchemy.dialects.postgresql import ARRAY

DB_URI = '...'
engine = sqlalchemy.create_engine(DB_URI)

sql = text("SELECT * FROM some_table WHERE userid = :userid").bindparams(bindparam("userid", String))
res = engine.execute(sql, userid=12345)

# in particular this bit is useful when you have a list of ids
sql = text("SELECT * FROM some_table WHERE userids = :userids").bindparams(bindparam("userids", ARRAY(String)))
res = engine.execute(sql, userids=[12345, 12346, 12347])

answered Jan 24, 2017 at 9:03

asPlankBridge

1,0821 gold badge18 silver badges30 bronze badges

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

Comments

Zaffer · Accepted Answer · 2022-05-24 06:30:22Z

-4

From what I see, there are two kind of "duh" ways to do it if I am not missing anything -

1st Way WARNING THIS IS SUSCEPTIBLE TO SQL INJECTION ATTACKS, DO NOT USE IN PRODUCTION

userid=12345 # defining here
sql = text("SELECT * FROM some_table WHERE userid = '"+userid+"'")
res = engine.execute(sql)

2nd Way

sql = text("SELECT * FROM some_table WHERE userid = :userid")
res = engine.execute(sql, userid=str(12345))

edited May 24, 2022 at 6:30

Zaffer

1,8882 gold badges20 silver badges39 bronze badges

answered Nov 30, 2016 at 3:48

Vivek Kalyanarangan

9,1011 gold badge27 silver badges42 bronze badges

3 Comments

asPlankBridge Over a year ago

Yeah - I knew both of these... (There is also the way of explicitly type casting in SQL). But my issue is that I have an postgresql array of integers that I want to compare against an array of BIGINTs. I know there are types in sqlalchemy and I seem to remember that there is a way of explicitly setting a type for a text() command. (btw. 1st way is a bad idea because of userid="';DROP TABLE some_table; --")

Vivek Kalyanarangan Over a year ago

can you please edit the question to include these details? Its very hard to narrow down a solution and we will only end up guessing stuff

LUser Over a year ago

The first answer appears to lead to SQL Injection. Such answers should be removed to prevent inexperienced users from copy pasting vulnerable code into production and reduce the amount of bad code here.

Collectives™ on Stack Overflow

Setting input parameter types in SQLAlchemy raw SQL

2 Answers 2

Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related