How to Securely Pass Parameters to SQLAlchemy's `connection.execute` Method?

Passing Parameters in SQLAlchemy's connection.execute Method

The provided code snippet retrieves data from a SQL query using the connection.execute method and converts the result to an array of maps. However, parameterization is currently handled by string formatting, which poses a security risk.

To resolve this issue, SQLAlchemy's text() function can be employed to generate parametrized SQL queries. This function takes a SQL string as an argument and enables the use of keyword parameters to specify values during execution.

Here's an updated version of the code:

def __sql_to_data(sql, values):
    result = []
    connection = engine.connect()
    try:
        # Convert SQL to parametrized SQL
        sql_text = sql.text(sql)
        rows = connection.execute(sql_text, values)

        for row in rows:
            result_row = {}
            for col in row.keys():
                result_row[str(col)] = str(row[col])
            result.append(result_row)
    finally:
        connection.close()
    return result

In this updated code:

1. The SQL string (sql) is passed to text() to create a parametrized SQL object (sql_text).
2. Keyword parameters (values) are provided to the execute method to replace placeholders in the SQL string.

Now, you can parameterize your SQL without sacrificing security by using __sql_to_data(sql, values):

sql = 'SELECT ... WHERE user_id = :user_id'
values = { 'user_id' : 3 }
results = __sql_to_data(sql, values)

The above is the detailed content of How to Securely Pass Parameters to SQLAlchemy's `connection.execute` Method?. For more information, please follow other related articles on the PHP Chinese website!