Python/ SQL Statement - Use of parameters / injection protection?

Question

I have inherited some code similar to below. I understand the concept of passing values to make a query dynamic(in this case field_id) but I don't understand what the benefit of taking the passed-in field_id list and putting it into a dictionary parameters = {"logical_field_id": field_id} before accessing the newly created dictionary to build the SQL statement. Along the same line why return parameters=parameters rather than just listing parameters in the return? I assume this is all the make the request more secure but I would like to better understand of why/how as I need to take on a similar task on a slightly more complex query that is below

def get_related_art(self, field_id):                                               
    parameters = {"logical_field_id": field_id}
    sql = (
        "SELECT a.id AS id,"
        "  a.name AS name,"
        "  a.description AS description,"
        "  a.type AS type,"
        "  a.subtype AS subtype "
        " FROM ArtclTbl AS a INNER JOIN ("
        "   SELECT article_id AS id FROM LogFldArtclTbl"
        "     WHERE logical_field_id = %(logical_field_id)s"
        " ORDER BY a.name"
    )
    return self.query(sql, parameters=parameters)

My reason for asking this question is I was asked to parameterize this

 def get_group_fields(self, exbytes=None):

    parameters = {}
    where_clause = (
        f"WHERE eig_eb.ebyte in ({', '.join(str(e) for e in ebytes)})" if ebytes else ""
    )
    sql = (
        "SELECT l.id AS id, "
        "  eig_eb.ebyte AS ebyte, "
        "  eig.id AS instrument_group_id, "
        "  eig_lf.relationship_type AS relationship "
        ....
        f" {where_clause}"
    )

I started to modify code to iterate when setting the parameters and then accessing that value in the original location. This 'works' except now the query string returns ([ebyte1, ebyte2] instead of (ebyte1, ebyte2). I could modify the string to work around this but i really wanted to understand the why of this first.

    parameters = {"exbytes": ', '.join(str(e) for e in exbytes)}
    ...
    where_clause = (
        f"WHERE eig_eb.exbyte in " + str(exbytes) if exbytes else ""

Does this help?

snakecharmerb
– snakecharmerb

2022-12-07 14:28:00 +00:00
Commented Dec 7, 2022 at 14:28 — snakecharmerb
– snakecharmerb, Commented Dec 7, 2022 at 14:28

Bill Karwin · Accepted Answer · 2022-12-07 18:39:33Z

1

The benefit of using named parameter placeholders is so you can pass the parameter values as a dict, and you can add values to that dict in any order. There's no benefit in the first example you show, because you only have one entry in the dict.

There's no benefit in the second example either, because the parameters are part of an IN() list, and there are no other parameterized parts of the query. The order of values in an IN() list is irrelevant. So you could just use positional parameters instead of named parameters.

where_clause = (
    f"WHERE eig_eb.ebyte in ({', '.join('%s' for e in ebytes)})" if ebytes else ""
)

Then you don't need a dict at all, you can just pass the ebytes list as the parameters.

Using the syntax parameters=parameters looks like a usage of keyword arguments to a Python function. I don't know the function self.query() in your example, but I suppose it accepts keyword arguments to implement optional arguments. The fact that your local variable is the same name as the keyword argument name is a coincidence.

edited Dec 7, 2022 at 18:39

answered Dec 7, 2022 at 16:51

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

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

2 Comments

personalt Over a year ago

I think you are correct here. I figured out later that the 'benifit' is that the relevant security team that cares about this wants us to pass in any query variables as separate parameters rather then build it into the SQL string. So that is there reason for the extra hoops

Bill Karwin Over a year ago

I agree that SQL queries should be parameterized by default, not combined with string-concatenation or formatting. SQL injection is a real risk, and results in high-profile data breaches every month. Writing code securely should not be optional. There might be some exception cases where you really can't use query parameters, but they are rare, and this example is not an exception case.

Collectives™ on Stack Overflow

Python/ SQL Statement - Use of parameters / injection protection?

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related