CODEWITHSUNDEEP
postgresqlsecuritypsycopg2parameterization
user26914090
user26914090

Reputation: 1

Programmatically Searching Multiple Columns for Value using Psycopg2

I'm working on a full-stack web project to database native plants in my area, and I'm using PostgreSQL and Python on my backend.

An important part of the tool is the ability to filter plants using numerous parameters on the frontend. One of these parameters is the plant's habit. There are 7 different value's I have in my database for possible plant habits (each plant can have more than one), and these are represented in my "plant_habits" table as a binary 1/0 in each column. The table looks something like this:

scientific_name herb shrub tree cactus_or_succulent grass_or_grass_like fern vine
plant 1 name 1 1 0 0 0 0 0
plant 2 name 0 0 1 0 0 0 0
...

My frontend is currently returning a list of selected habits. This is filter_params['selected_habits'] in the below chunk of code. Currently I am verifying that the selected_habits values are in a predetermined list of possible plant habits, and, if so, creating a WHERE statement to check each relevant column for a "1" value, which I eventually add to my complete SQL query, along with the rest of the filters.

if filter_params['selected_habits']:
    habit_statements = []
    for column_name in filter_params['habit']:
        # Sanitize inputs
        if column_name in ['herb', 'shrub', 'tree', 'cactus_or_succulent', 
                           'grass_or_grass_like', 'fern', 'vine']:
            habit_statements.append(f"""plant_habits.{column_name} = '1'""")
    where_statements.append('(' + ' OR '.join(habit_statements) + ')')

Although I see no security concerns with this approach, I also recognize that this isn't the "right" way to go about it when it comes to psycopg2's recommendations for parameterization.

I'm curious if there's a better way to go about this, or if this is a perfectly secure way to create this chunk of my query. Other sections of my query are built using the recommended %()s format for parameterization, and I'd love to clean up this section if there's a better way to do it.

I could create an "if" for each value, and insert a bespoke section of my query for each option eg:

if column_name == 'herb':
    habit_statements.append("plant_habits.herb = '1'")

but this quickly gets out of hand, especially if I choose to incorporate additional plant habit options later on. And in the end, this is exactly what the other chunk of code is doing.

It would be stellar if there were a "WHERE value IN [list_of_columns]" option in PostgreSQL, but I'm aware that there isn't, and I haven't been able to find another way to concisely search multiple columns for a value.

Feel free to tell me I need to reformat anything/everything, and thank you for the advice!

Upvotes: 0

Views: 26

Answers (0)

Related Questions