Spark SQL Guide

Parameter markers

Description

Parameter markers are named or unnamed typed placeholder variables used to supply values from the API invoking the SQL statement.

Using parameter markers protects your code from SQL injection attacks since it clearly separates provided values from the SQL statements.

You cannot mix named and unnamed parameter markers in the same SQL statement.

Parameter markers can be used wherever literal values are allowed in the SQL syntax.

The marker syntax (:name or ?) does not declare a type. The type is the type of the bound value:

To convert after binding, wrap the marker in the SQL text: CAST(:p AS CHAR(2)) or CAST(? AS INT). By default those CHAR/VARCHAR casts are STRING; they stay CHAR/VARCHAR when types are first-class. See Character String Types.

To parameterize identifiers (such as table or column names), use the IDENTIFIER clause.

Parameter markers can be provided by:

Named parameter markers

Named parameter markers are typed placeholder variables. The API invoking the SQL statement must supply name-value pairs to associate each parameter marker with a value.

Syntax
 :parameter_name
Parameters
Notes

You can reference the same parameter marker multiple times within the same SQL Statement. If no value has been bound to the parameter marker an UNBOUND_SQL_PARAMETER error is raised. You are not required to reference all supplied parameter markers.

The mandatory preceding : (colon) differentiates the namespace of named parameter markers from that of column names and SQL parameters.

Type examples
> EXECUTE IMMEDIATE 'SELECT TYPEOF(:p)' USING 'ab' AS p;
  string

> SET spark.sql.charVarchar.standardSemantics.enabled = true;
> EXECUTE IMMEDIATE 'SELECT TYPEOF(:p)' USING CAST('ab' AS CHAR(2)) AS p;
  string

> EXECUTE IMMEDIATE 'SELECT TYPEOF(:p)' USING CAST('ab' AS CHAR(2) COLLATE UTF8_LCASE) AS p;
  char(2) collate UTF8_LCASE

> EXECUTE IMMEDIATE 'SELECT TYPEOF(CAST(:p AS CHAR(2)))' USING 'ab' AS p;
  char(2)

spark.sql("SELECT TYPEOF(:p)", args={"p": "ab"}).first()[0]
# 'string'

spark.sql("SET spark.sql.charVarchar.standardSemantics.enabled = true")
spark.sql("SELECT TYPEOF(CAST(:p AS CHAR(2)))", args={"p": "ab"}).first()[0]
# 'char(2)'
Examples

The following example defines two parameter markers:

x is referenced multiple times, while later is referenced once.

Unnamed parameter markers

Unnamed parameter markers are typed placeholder variables. The API invoking the SQL statement must supply an array of arguments to associate each parameter marker with a value in the order in which they appear.

Syntax
 ?
Parameters
Notes

Each occurrence of an unnamed parameter marker consumes a value provided by the API invoking the SQL statement in order. If no value has been bound to the parameter marker, an UNBOUND_SQL_PARAMETER error is raised. You are not required to consume all provided values.

The type rules are the same as for named markers: bind a typed USING expression, or CAST the ? in the statement. A CHAR or VARCHAR USING value without an explicit collation currently binds as STRING; an explicitly collated CHAR or VARCHAR value keeps that type. Host spark.sql(..., args) values must be literal-like constants; they cannot be a cast Column.

> EXECUTE IMMEDIATE 'SELECT TYPEOF(?)' USING 'ab';
  string

> SET spark.sql.charVarchar.standardSemantics.enabled = true;
> EXECUTE IMMEDIATE 'SELECT TYPEOF(?)' USING CAST('ab' AS CHAR(2));
  string

> EXECUTE IMMEDIATE 'SELECT TYPEOF(?)' USING CAST('ab' AS CHAR(2) COLLATE UTF8_LCASE);
  char(2) collate UTF8_LCASE

> EXECUTE IMMEDIATE 'SELECT TYPEOF(CAST(? AS CHAR(2)))' USING 'ab';
  char(2)

Examples

The following example defines three parameter markers:

Since the parameters are unnamed each provided value is consumed by at most one parameter.