Bind variables¶

Applications can accept data from users and use that data in SQL statements. For example, an application might ask a user to enter contact information, such as an address and phone number.

To specify this user input in a SQL statement, you can programmatically construct a string for the SQL statement by concatenating the user input with the other parts of the statement. Alternatively, you can use bind variables. To use bind variables, put one or more placeholders in the text of the SQL statement, then specify the variable (the value to be used) for each placeholder.

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');

Copy

The following SQL statement uses placeholders for the inserted values:

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

Copy

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.

Using bind variables in Javascript stored procedures¶

You can use Javascript to create stored procedures that execute 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 (?, ?)

Copy

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.

Using bind variables with Snowflake Scripting¶

You can use Snowflake Scripting to create procedural code that executes 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)

Copy

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).

Using 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 executes a SQL statement, you can use bind variables for values in the statement. For more information, see Using bind variables in a statement.

Using 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 (https://pkg.go.dev/github.com/snowflakedb/gosnowflake#hdr-Binding_Parameters)
JDBC
.NET (https://github.com/snowflakedb/snowflake-connector-net/blob/master/doc/QueryingData.md#bind-parameter)
Node.js
ODBC
Python

Note

The PHP driver does not support bind variables.

Using 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.”

Note

Snowflake supports other data loading methods that are recommended instead of using array binds. For more information, see Load data into Snowflake and Data loading and unloading commands.

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)

Copy

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 (https://pkg.go.dev/github.com/snowflakedb/gosnowflake#hdr-Batch_Inserts_and_Binding_Parameters)
JDBC
.NET (https://github.com/snowflakedb/snowflake-connector-net/blob/master/doc/QueryingData.md#bind-array-variables)
Node.js
ODBC
Python

Note

The PHP driver does not support array binds.

Limitations of using array binds¶

The following limitations apply to array binds:

Only INSERT INTO … VALUES statements can contain array bind variables.
The VALUES clause must be a single-row list of bind variables. For example, the following VALUES clause is not allowed:
```
VALUES (?,?), (?,?)
```
Copy

Inserting multiple rows without using array binds¶

An INSERT statement might use bind variables to insert multiple rows without using an array bind. The following example inserts values into two rows, but it doesn’t use an array bind.

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

Copy

For example, your application can specify a bind list that’s equivalent to the following values, in order, for the placeholders: [1,'String1',2,'String2']. Because the VALUES clause specifies more than one row, the statement only inserts the exact number of values (four in the example), rather than a dynamic number of rows.

Using 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 (?);

Copy

The following example queries the table:

SELECT * FROM t;

Copy

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 (?);

Copy

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.

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 following parameters:
- LIKE
- LIMIT
- STARTS WITH
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.