Python Forum
SQLALCHEMY - Column doesn't exist - Printable Version

+- Python Forum (https://python-forum.io)
+-- Forum: Python Coding (https://python-forum.io/forum-7.html)
+--- Forum: General Coding Help (https://python-forum.io/forum-8.html)
+--- Thread: SQLALCHEMY - Column doesn't exist (/thread-35421.html)

SQLALCHEMY - Column doesn't exist - jamesaarr - Nov-01-2021

Hi all,

Very simple issue - code in question is:

for data in engine.execute('select style_no from data where style_size_no_in = "' +myVariable[0] + '"'):
    print(x)
The below error is being flagged:
Error:
sqlalchemy.exc.ProgrammingError: (psycopg2.errors.UndefinedColumn) column "style_no" does not exist
The problem is that this column definitely exists as I've got PGAdmin4 open on another screen. If I change the style_no to * then it says that style_size_no_in doesn't exist either.

Please help.

James

RE: SQLALCHEMY - Column doesn't exist - jamesaarr - Nov-01-2021

Figured out a fix - if I put the column names in quotes then it works fine.

Thanks,
James

RE: SQLALCHEMY - Column doesn't exist - ndc85430 - Nov-01-2021

You also shouldn't be concatenating strings to build SQL statements, as that's vulnerable to SQL injection. Bobby Tables can educate you on this.

RE: SQLALCHEMY - Column doesn't exist - jamesaarr - Nov-01-2021

(Nov-01-2021, 12:45 PM)ndc85430 Wrote: You also shouldn't be concatenating strings to build SQL statements, as that's vulnerable to SQL injection. Bobby Tables can educate you on this.

Hi there,

I understand. My workaround (on flask) is to pull the data from sql, use that to build an html string, close the connection and then return the html string.

That way nothing on the user's end relates directly to the db.

RE: SQLALCHEMY - Column doesn't exist - buran - Nov-01-2021

The vulnerability that @ndc85430 mentions has nothing to do with closing connection before returning anything to frontend.
Assuming you run query based on some query parameters that you receive from user you are exposed to SQL injection (also on Wikipedia)

Also, why do you construct html string at the backend, don't you use templates?

RE: SQLALCHEMY - Column doesn't exist - jamesaarr - Nov-02-2021

(Nov-01-2021, 02:00 PM)buran Wrote: The vulnerability that @ndc85430 mentions has nothing to do with closing connection before returning anything to frontend.
Assuming you run query based on some query parameters that you receive from user you are exposed to SQL injection (also on Wikipedia)

Also, why do you construct html string at the backend, don't you use templates?

Hi,

No, the website itself doesn't need to look nice, just to display data. There isn't any query being constructed by user input, the page's role is to dynamically display data.

Thanks,
James

RE: SQLALCHEMY - Column doesn't exist - ndc85430 - Nov-02-2021

Still, there's no downside to constructing queries correctly and it doesn't take much effort.

RE: SQLALCHEMY - Column doesn't exist - buran - Nov-02-2021

(Nov-02-2021, 08:43 AM)jamesaarr Wrote: the page's role is to dynamically display data.
I still think you don't understand. How does it display data dynamically, without query parameters? E.g. where myVariable[0] value comes from?

RE: SQLALCHEMY - Column doesn't exist - jamesaarr - Nov-03-2021

(Nov-02-2021, 11:20 AM)buran Wrote:
(Nov-02-2021, 08:43 AM)jamesaarr Wrote: the page's role is to dynamically display data.
I still think you don't understand. How does it display data dynamically, without query parameters? E.g. where myVariable[0] value comes from?

Hi mate,

The query uses variable parameters from seperate tables. It runs a query for all on one table, then searches the other tables for data using a list. These are fixed, there is no user input on this page, and the connection is closed before the HTML is returned in flask.

Thanks,
James

RE: SQLALCHEMY - Column doesn't exist - ndc85430 - Nov-04-2021

I don't understand the aversion to doing the correct thing, especially when it isn't complicated.