SnowPro Associate: Platform Question 91
Single answer● Python variable substitutionA data engineer is using the Snowflake Connector for Python to run a query that filters rows by a customer ID provided at runtime. They want to avoid SQL injection and ensure the value is passed correctly as a bind variable. Which approach should they use?
- A
Build the SQL string with a Python f-string, for example: f"SELECT * FROM orders WHERE customer_id = {customer_id}"
- B
Use Python string concatenation, for example: "SELECT * FROM orders WHERE customer_id = " + customer_id
- C
Use a parameterized query with execute(), for example: cursor.execute("SELECT * FROM orders WHERE customer_id = %s", (customer_id,))
- D
Set a Snowflake SQL session variable with Python, then reference it as &customer_id in the SQL statement
Show answer and explanation
Correct answer: C
Explanation
In Python, values that must be supplied at runtime should be passed to Snowflake using bind variables through the Snowflake Connector for Python, typically with cursor.execute(sql, params). This is the safest and most maintainable approach because it reduces SQL injection risk and avoids manual quoting and escaping issues. A common misconception is to confuse Python parameter binding with SnowSQL variable substitution. SnowSQL supports substitution variables such as &name, but that mechanism does not apply to Python connector code. Another common mistake is building SQL with f-strings or concatenation, which may appear convenient but is not a best practice for dynamic input. Snowflake documentation for the Python connector describes parameter binding and recommends using bind variables for values supplied by applications.
- A. Incorrect.
Incorrect. Using a Python f-string injects the value directly into the SQL text before it is sent to Snowflake. Although this may work syntactically for some values, it is not the recommended approach for runtime input because it increases the risk of SQL injection and can lead to quoting/escaping errors.
- B. Incorrect.
Incorrect. String concatenation has the same core problem as f-strings: it manually inserts user-supplied data into the SQL statement. This is error-prone and insecure, especially when the variable contains quotes or unexpected characters. It also makes type handling harder.
- C. Correct.
Correct. The Snowflake Connector for Python supports bind variables through parameterized execution. Passing the SQL text separately from the parameter values lets the connector handle binding safely and correctly. This is the recommended way to substitute runtime values from Python into SQL statements.
- D. Incorrect.
Incorrect. The &variable syntax is associated with client-side variable substitution in SnowSQL, not Python code executed through the Snowflake Connector for Python. In Python, the correct pattern is to use parameter binding with execute() rather than relying on SnowSQL-style substitution syntax.