Pyspark : Dynamically prepare pyspark-sql query using parameters

Question

What are the different ways to dynamicaly bind parameters and prepare pyspark-sql statament.

Example:

Dynamic Query

query = '''SELECT column1, column2
           FROM ${db_name}.${table_name}
           WHERE column1 = ${filter_value}'''

Above dynamic query have ${db_name}, ${table_name} and ${filter_value} variables, These variables will get values from run time parameters.

Parameter Details:

db_name = 'your_db_name'
table_name = 'your_table_name'
filter_value = 'some_value'

Expected Query after Binding Parameters in Dynamic Query

SELECT column1, column2
FROM your_db_name.your_table_name
WHERE column1 = some_value  

Answer 1

While this question was marked as "answered", I wanted to help prevent the spread of cut-and-paste insecure code that the answer provides.

The documentation for PySpark's SQL command shows that, starting in version 3.4, you can now add positional parameters:

spark.sql("SELECT column1, column2 FROM your_db_name.your_table_name WHERE column1 = ?", args=['some_value'])

Parameterized SQL does not allow for a way to replace database, table names, or column names. If you really need this, then the Spark API allows for a simple f-string style replacement by using kwargs:

spark.sql("SELECT column1, column2 FROM {db_name}.{table_name} WHERE column1 = ?", args=['some_value'], db_name='your_db_name', table_name='your_table_name')

However, this just uses simple Python f-string replacement, and does not prevent SQL injection attacks. If you really need this functionality, then you must ensure the db_name and table_name parameters have come from safe (i.e. non-user tamper-able) sources. As per the OWASP guide, "Manually escaping characters in input to SQL queries can help, but it will not make your application secure from SQL injection attacks."