Bind variables¶

Overview of bind variables¶

With bind variables, you replace literals in SQL statements with placeholders. For example, the following SQL statement uses literals for the inserted values:

INSERT INTO t (c1, c2) VALUES (1, 'Test string');

The following SQL statement uses placeholders for the inserted values:

INSERT INTO t (c1, c2) VALUES (?, ?);

Your application code binds data with each placeholder in the SQL statement. The technique for binding data with a placeholder depends on the programming language. The syntax of the placeholder also varies by programming language. It is either ?, :varname, or %varname.

Use bind variables in Javascript stored procedures¶

You can use Javascript to create stored procedures that run SQL.

To specify bind variables in Javascript code, use ? placeholders. For example, the following INSERT statement specifies bind variables for the values inserted into a table row:

INSERT INTO t (col1, col2) VALUES (?, ?)

In Javascript code, you can use bind variables for the values in most SQL statements. For information about limitations, see Limitations for bind variables.

For more information about using bind variables in Javascript, see Binding variables.

Use bind variables with Snowflake Scripting¶

You can use Snowflake Scripting to create procedural code that runs SQL, such as code blocks and stored procedures. To specify bind variables in Snowflake Scripting code, prefix the variable name with a colon. For example, the following INSERT statement specifies a bind variable named variable1:

INSERT INTO t (c1) VALUES (:variable1)

When you run SQL in an EXECUTE IMMEDIATE command or an OPEN command for a cursor, you can bind variables with the USING clause.

This example binds variables in an EXECUTE IMMEDIATE command with a USING clause:

EXECUTE IMMEDIATE :query USING (minimum_price, maximum_price);

For the full example that includes this code, see Executing a statement that contains bind variables.

When you declare a cursor, you can specify bind parameters (? characters) in a SELECT statement. You can then bind these parameters to variables in the USING clause when you open the cursor.

The following example declares a cursor and specifies bind parameters, then opens the cursor with the USING clause:

LET c1 CURSOR FOR SELECT id FROM invoices WHERE price > ? AND price < ?;
OPEN c1 USING (minimum_price, maximum_price);

Snowflake Scripting also supports numbering bind variables by position and reusing a bind variable in a SQL statement. For numbered bind variables, each variable declaration is assigned an index, and you can refer to the nth declared variable with :n. For example, the following Snowflake Scripting block specifies bind variable :1 for the i variable and :2 for the v variable, and it reuses the :1 bind variable in a SQL statement:

EXECUTE IMMEDIATE $$
DECLARE
  i INTEGER DEFAULT 1;
  v VARCHAR DEFAULT 'SnowFlake';
  r RESULTSET;
BEGIN
  CREATE OR REPLACE TABLE snowflake_scripting_bind_demo (id INTEGER, value VARCHAR);
  EXECUTE IMMEDIATE 'INSERT INTO snowflake_scripting_bind_demo (id, value)
    SELECT :1, (:2 || :1)' USING (i, v);
  r := (SELECT * FROM snowflake_scripting_bind_demo);
  RETURN TABLE(r);
END;
$$
;

+----+------------+
| ID | VALUE      |
|----+------------|
|  1 | SnowFlake1 |
+----+------------+

In Snowflake Scripting code, you can use bind variables for the values in most SQL statements. For information about limitations, see Limitations for bind variables.

For more information about using bind variables in Snowflake Scripting, see Using a variable in a SQL statement (binding) and Using an argument in a SQL statement (binding).

Use bind variables with the SQL API¶

You can use the Snowflake SQL API to access and update data in a Snowflake database. You can create applications that use the SQL API to submit SQL statements and manage deployments.

When you submit a request that runs a SQL statement, you can use bind variables for values in the statement. For more information, see Using bind variables in a statement.

Use bind variables with drivers¶

Using Snowflake drivers, you can write applications that perform operations on Snowflake. The drivers support programming languages such as Go, Java, and Python. For information about using bind variables in an application for a specific driver, follow the link for the driver:

• Go

• JDBC

• .NET

• Node.js

• ODBC

• Python

Use bind variables with arrays of values¶

You can bind an array of values to variables in SQL statements. Using this technique, you can improve performance by inserting multiple rows in a single batch, which avoids network round trips and compilations. The use of an array bind is also called a “bulk insert” or “batch insert.”

The following is an example of an array bind in Python code:

conn = snowflake.connector.connect( ... )
rows_to_insert = [('milk', 2), ('apple', 3), ('egg', 2)]
conn.cursor().executemany(
            "insert into grocery (item, quantity) values (?, ?)",
            rows_to_insert)

This example specifies the following bind list: [('milk', 2), ('apple', 3), ('egg', 2)]. The way an application specifies a bind list depends on the programming language.

This code inserts three rows into the table:

+-------+----+
| C1    | C2 |
|-------+----|
| milk  |  2 |
| apple |  3 |
| egg   |  2 |
+-------+----+

For information about using array binds in an application for a specific driver, follow the link for the driver:

• Go

• JDBC

• .NET

• Node.js

• ODBC

• Python

INSERT INTO t VALUES (?,?), (?,?);

Use bind variables with semi-structured data¶

To bind variables with semi-structured data, bind the variable as a string type, and use functions such as PARSE_JSON or ARRAY_CONSTRUCT.

The following example creates a table with one VARIANT column and then calls the PARSE_JSON function to insert semi-structured data into the table with a bind variable:

CREATE TABLE t (a VARIANT);
-- Code that supplies a bind value for ? of '{'a': 'abc', 'x': 'xyz'}'
INSERT INTO t SELECT PARSE_JSON(a) FROM VALUES (?);

The following example queries the table:

SELECT * FROM t;

The query returns the following output:

+---------------+
| A             |
|---------------|
| {             |
|   "a": "abc", |
|   "x": "xyz"  |
| }             |
+---------------+

The following statement calls the ARRAY_CONSTRUCT function to insert an array of semi-structured data into a VARIANT column with a bind variable:

INSERT INTO t SELECT ARRAY_CONSTRUCT(column1) FROM VALUES (?);

Both of these examples can insert a single row, or they can use an array bind to insert multiple rows in one batch. You can use this technique to insert any type of semi-structured data that is valid in a VARIANT column.

Retrieve bind variable values¶

To retrieve the values of bind variables in a query that has been executed, you can use the BIND_VALUES table function in the INFORMATION_SCHEMA schema. With this function, you can retrieve bind variable values from any code that supports bind variables, including Javascript and Snowflake Scripting code.

You can also access these bind variable values from the bind_values column in the output for the QUERY_HISTORY Account Usage view, the QUERY_HISTORY Organization Usage view, or the QUERY_HISTORY function.

To retrieve bind variable values using any of these methods, you must set the ALLOW_BIND_VALUES_ACCESS account-level parameter to TRUE.

You might want to retrieve bind variable values for the following cases:

• Troubleshooting queries - When you know the exact bind values used in queries, it’s easier to optimize the queries and debug the following types of issues:

  • A query fails to run.

  • A query’s performance is poor.

  • A query isn’t using caches or expected execution plans.

• Recreating queries for testing - Developers and DBAs can recreate user-generated queries with bind variable values to replicate problems and for stress testing.

• Auditing and compliance - For security and compliance purposes, organizations must audit the data that users are accessing. They can use bind variable values to determine the exact data retrieved by users.

SELECT * FROM TABLE(
  INFORMATION_SCHEMA.BIND_VALUES('<query_id_value>'));

SELECT bind_values
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE query_id = '<query_id_value>';

DECLARE
  name STRING;
  temperature FLOAT;
  res RESULTSET;
BEGIN
  name := 'Snowman';
  temperature := -20.14;
  res := (
    SELECT
      CONCAT('Hello ', :NAME, '!') as greeting,
      CONCAT('It is ', :TEMPERATURE, 'deg C today.') as weather
  );
  RETURN LAST_QUERY_ID();
END;

EXECUTE IMMEDIATE
$$
DECLARE
  name STRING;
  temperature FLOAT;
  res RESULTSET;
BEGIN
  name := 'Snowman';
  temperature := -20.14;
  res := (
    SELECT
      CONCAT('Hello ', :NAME, '!') as greeting,
      CONCAT('It is ', :TEMPERATURE, 'deg C today.') as weather
  );
  RETURN LAST_QUERY_ID();
END;
$$
;

+--------------------------------------+
| anonymous block                      |
|--------------------------------------|
| 01bbe3d6-0109-0863-0000-a99502ffa062 |
+--------------------------------------+

SELECT * FROM TABLE(
  INFORMATION_SCHEMA.BIND_VALUES('01bbe3d6-0109-0863-0000-a99502ffa062'));

+--------------------------------------+----------+-------------+------+---------+
| QUERY_ID                             | POSITION | NAME        | TYPE | VALUE   |
|--------------------------------------+----------+-------------+------+---------|
| 01bbe3d6-0109-0863-0000-a99502ffa062 |     NULL | TEMPERATURE | REAL | -20.14  |
| 01bbe3d6-0109-0863-0000-a99502ffa062 |     NULL | NAME        | TEXT | Snowman |
+--------------------------------------+----------+-------------+------+---------+

cursor = conn.cursor()
print(cursor.execute(
          """
          SELECT
              CONCAT('Hello ', ?, '!') as greeting,
              CONCAT('It is ', ?, 'deg C today.') as weather
          """,
          params=["Snowman", -20.14],
      ).fetch_pandas_all())

query_id = cursor.sfqid
print(f"Bind values for query {query_id} are:")
print(cursor.execute("SELECT * FROM TABLE(INFORMATION_SCHEMA.BIND_VALUES(?))", params=[query_id]).fetch_pandas_all())

        GREETING                   WEATHER
0  Hello Snowman!  It is -20.14deg C today.

Bind values for query 01bbe918-0200-0001-0000-000000101145 are:

                               QUERY_ID POSITION  NAME  TYPE    VALUE
0  01bbe918-0200-0001-0000-000000101145        1  None  TEXT  Snowman
1  01bbe918-0200-0001-0000-000000101145        2  None  REAL   -20.14

Limitations for bind variables¶

The following limitations apply to bind variables:

• Limitations for SELECT statements:

  • Bind variables can’t replace numbers that are part of a data type definition (for example, NUMBER(?)) or collation specification (for example, COLLATE ?).

  • Bind variables can’t be used for the source in a SELECT statement that queries files on a stage.

• Limitations for DDL commands:

  • Bind variables can’t be used in the following DDL commands:

    • CREATE/ALTER INTEGRATION

    • CREATE/ALTER REPLICATION GROUP

    • CREATE/ALTER PIPE

    • CREATE TABLE … USING TEMPLATE

  • Bind variables can’t be used in the following clauses:

    • ALTER COLUMN

    • COMMENT ON CONSTRAINT

  • In CREATE/ALTER commands, bind variables can’t be used for the values of the following parameters:

    • CREDENTIALS

    • DIRECTORY

    • ENCRYPTION

    • IMPORTS

    • PACKAGES

    • REFRESH

    • TAG

    • Parameters that are specific to external tables

  • Bind variables can’t be used for properties that are part of a FILE FORMAT value.

• In COPY INTO commands, bind variables can’t be used for the values of the following parameters:

  • CREDENTIALS

  • ENCRYPTION

  • FILE_FORMAT

• In SHOW commands, bind variables can’t be used in the STARTS WITH parameter.

• Bind variables can’t be used in an EXECUTE IMMEDIATE FROM command.

• Bind variable values can’t be converted automatically from one data type to another when bind variables are used in:

  • Snowflake Scripting code that specifies the data type explicitly

  • DDL statements

  • Stage names

Security considerations for bind variables¶

Bind variables don’t mask sensitive data in all cases. For example, the values of bind variables might appear in error messages and other artifacts.

Bind variables can help to prevent SQL injection attacks when you construct SQL statements with user input. However, bind variables can present potential security risks. If inputs to SQL statements come from external sources, make sure they are validated. For more information, see SQL injection.