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. Bind variables can prevent SQL injection attacks when you construct SQL statements with user input.

You can use bind variables in the following ways with Snowflake:

Overview

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 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:

Note

The PHP driver does not support bind 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. 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

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

Using bind variables with the SQL API

You can use the Snowflake SQL REST 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.

Binding arrays of values to variables

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:

Note

The PHP driver does not support array binds.

Limitations for 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

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.

Language: English