Sqlalchemy core - dynamic SQL query and escaping values

Question

I’m creating a dynamic Insert query.

 sql = "INSERT INTO `" + self.db_name + "` (" sql += ','.join(e.db_name for e in self.fields) sql += ") VALUES (" sql += ','.join(("'" + e.value + "'") for e in self.fields) sql += ");" result = s.execute(sql)

It works fine, except the inserted value is not safe of special characters and SQL injection.

I cant use the SqlAlchemy text() mechanism because i don’t know the names or quantity of fields in table.

I tryed MySQLdb.escape_string(), but its not working with Unicode.

How can i make a dynamic sql Insert query while escaping special characters in unicode value?

This is wrong approach if you indent to use SQLAlchemy. Do you have SQLAlchemy model classes? You can use them dynamically. If you don't you can also generate SQLAlchemy models in-fly: docs.sqlalchemy.org/en/rel_1_0/orm/extensions/automap.html — Mikko Ohtamaa
– Mikko Ohtamaa, Commented Aug 6, 2015 at 13:44
You'd possibly use table() and column(), the light weight analogues of Table and Column, but this reeks of an XY problem. Why would one not know the names of their tables etc.? — Ilja Everilä
– Ilja Everilä, Commented Apr 3, 2018 at 11:43

enf644 · Accepted Answer · 2020-01-13 08:32:35Z

5

The answer I wanted to hear 4 years ago -

sql = 'INSERT INTO some_table (guid, name) VALUES (:guid_val, :name_val)' db_session.execute(sql, { "guid_val": uuid.uuid4(), "name_val": "Hello world" })

answered Jan 13, 2020 at 8:32

enf644

5846 silver badges14 bronze badges

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

2 Comments

c8999c 3f964f64 Over a year ago

Can you elaborate why this is the correct approach? Is this like prepared statements for Sqlalchemy? Is this the same as values = {"guid_val": uuid.uuid4(), "name_val": "Hello World"} sql = some_table.insert() sql.insert({**values}) using sqlalchemy core?

enf644 Over a year ago

If you want to make simple INSERT - using sql.insert() is better then my answer. My question 4 years ago was about constructing safe custom queries with many unknowns. The main problem is SQL injection. Using parameterized execution is far safer than just constructing a SQL string with values and executing it.

Collectives™ on Stack Overflow

Sqlalchemy core - dynamic SQL query and escaping values

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related