TypeError: 'dict' object does not support indexing thrown on second instance of this query

Question

So I am building a query based on user input in flask with this code:

    if empty_indic_form.validate_on_submit():
    query='select name, year, value, display_name from literal inner join ent on ent_id=ent.id where display_name in ('
    for i in indic_form.indicators.data:
        query=query+'\''+i+'\','
    query=query[:-1]+') '
    query=query+'and name in ('
    for c in order_c:
        query=query+c+','
    query=query[:-1]+')'
    data_return=db.engine.execute(query).fetchall()

I have confirmed that query looks like what it is supposed to, and even had an earlier session where it was returning a list of rowproxy objects like I was expecting. But now I am getting this error no matter what I do!

I have set query to a variable in the templates so I can print it out, and here is what I get:

select name, year, value, display_name from literal inner join ent on ent_id=ent.id where display_name in ('Energy savings of primary energy (TJ)','Adolescent birth rate (women aged 15-19 years)','Net migration rate','Transmission and distribution losses (%)') and name in ('Burkina Faso', 'Ghana', 'Saudi Arabia', 'Pakistan')

I ran that directly on my Postgres DB and the result was grand.

In the error dump I notice that the data_return=db.engine.execute(query).fetchall() line is building with an empty dictionary as the parameters, which of course throws that error in the end. Can I force it not to do this? The query object looks like it does above, whats wrong with it now? Should I perhaps be killing the db session upon refreshing the page or going to the home page?

Outlying

dumping at the db.engine.execute stack trace line I get ` "query= u'select name, year, value, display_name from literal inner join ent on ent_id=ent.id where display_name in ('Old-age dependency ratio, ages 65 and older','Forest area (% of total land area)','Energy intensity of transportation sector (MJ/2011 USD PPP)','Divisia Decomposition Analysis - Energy Intensity component Index','HDI: Average annual growth rate, 2000-2013','GDI: Expected years of schooling, males') and name in ('Saudi Arabia', 'Pakistan', 'Finland', 'Jamaica')" " so Ill try coercing from unicode to str? – Outlying

Not really familiar, but for psycopg2 the normal query is using triple quoted literals. Have you tried that? – Hollingshead

Where the full traceback message? – Velez

Mom of little Robert wants to have a word with you. – Quadruplicate

got a great answer but can someone please explain why this was down voted? I am a very new coder and while I know sql injections are serious threats I didnt know string concatenation makes them this easy, not sure how I would have inferred that from the other questions. – Outlying

Answer 1

The fundamental error in your method is using string concatenation to build an SQL query. If indic_form.indicators.data or order_c is user provided data, for example from an HTTP request, you've probably opened yourself up for SQL injection. The error

TypeError: 'dict' object does not support indexing

is the result of this concatenation: your query string contains a rogue "%", which is part of the placeholder syntax of psycopg – the DB-API usually used with SQLAlchemy to talk to Postgresql. This is exactly the reason why manual concatenation should not be done. Getting escaping right can be hard.

Passing a list of values to IN operator is done using tuples adaptation in Psycopg2. In SQLAlchemy you'd use the in_ column operator, or an expanding bind param that were introduced in version 1.2.

Unfortunately in your particular case the SQLAlchemy wrapping, the engine, has a gotcha: as all your arguments would be tuples, the SQLAlchemy engine thinks that you're trying to pass it an iterable of argument tuples as multiparams and uses executemany() automatically. You can work around this by using the text() construct, which allows for DB-API agnostic bound parameter syntax and dictionaries as argument containers:

from sqlalchemy import text

...

if empty_indic_form.validate_on_submit():
    # Note that the placeholders shouldn't have enclosing parentheses
    query = """SELECT name, year, value, display_name
               FROM literal
               INNER JOIN ent ON ent_id = ent.id
               WHERE display_name IN :display_name
                 AND name IN :name"""

    data_return = db.engine.execute(
        text(query), {'display_name': tuple(indic_form.indicators.data),
                      'name': tuple(order_c)}).fetchall()

To recap: never build SQL queries using string concatenation or manual formatting. Always use placeholders / bound parameters.

Lewes

this is awsome, thanks! I am having a hard time navgating sql alchemy's documentation, where can I see more about Placeholders/bounds? – Outlying

Im also now getting this File "C:\Users\Vince\Google Drive\PeacefulMandE\Vasco de Data\Vasco\views.py", line 66, in show_avail {'display_name': tuple(indic_form.indicators.data), TypeError: 'NoneType' object is not iterable – Outlying

Prolly indic_form.indicators.data is None. Just handle that special case the way that suits you best. For more info on using textual SQL with SQLAlchemy, read docs.sqlalchemy.org/en/latest/core/… – Aphelion

Or if I run the code as copied "text is not defined" – Outlying

Remember to add the import. – Aphelion

Update on this many years later - can you use f strings/is that a good idea? – Outlying

Looks like you can with a little finesse? github.com/arogozhnikov/python3_with_pleasure/issues/3 – Outlying

It is not a good idea, as demonstrated in that issue. It is just another form of string concatenation/formatting. If by finesse you mean using !r, then that's neither safe nor practical: it will break with strings that contain a '. – Aphelion