Add application logic to an application package¶

Add application code securely¶

All stored procedures and UDFs within a Snowflake Native App run as the application and have access to all objects within the installed Snowflake Native App. This can lead to SQL injection attacks.

When developing procedures and functions for use within a Snowflake Native App, Snowflake recommends that all SQL commands requiring input from users be run using bound parameters. This includes input provided through procedure arguments.

See Creating a Stored Procedure for more information.

About caller’s rights and owner’s rights¶

All procedures created by the setup script or that run within the installed Snowflake Native App must be run with the rights of the owner (EXECUTE AS OWNER).

This restriction exists because if the Snowflake Native App were to run with caller’s rights (EXECUTE AS CALLER) in a procedure that the Snowflake Native App does not own, the procedure would run as the Snowflake Native App itself and allow a consumer to create code to view or modify the contents of the Snowflake Native App and shared data content.

See Understanding Caller’s Rights and Owner’s Rights Stored Procedures for more information.

Limitations when calling context functions from the setup script¶

Context functions provide information about the context in which a statement is run. Within the context of the Snowflake Native App Framework, some context functions are not available. Context functions that are not available are either blocked and return an error or always return a null value.

In general, you must use caution when using context functions in policies applied to shared data content within a Snowflake Native App. Some functions, for example CURRENT_IP_ADDRESS, behave differently in the context of a Snowflake Native App.

Note that when using context functions that depend on the namespace within the client organization there may be conflicts with functions in other namespaces. For example, a row access policy using CURRENT_USER should be aware that the same username can exist in multiple accounts.

The following table lists the context functions that are not supported by the Snowflake Native App Framework:

Reference external code files¶

There are two types of code files that you can include in an application package:

• Referenced files: include binaries, libraries and other code files. These files are specific to a version defined in an application package. These files must be located in the root directory of the stage when creating or adding a version to an application package.

  Referenced files are different from user-defined functions and stored procedures because they are not defined in the setup script of an application package. These files are referenced by import statements within the stored procedures and UDFs that are defined in the setup script.

• Resource files: include semi-structured data, structured data, and binaries, for example, a machine learning model. These files must be uploaded to a named stage that that is accessible to the application package.

A stored procedure, user-defined function, or external function that references these types of code files must be created within a versioned schema in the setup script. When creating stored procedures or functions within a versioned schema, you must reference a code file relative to the root directory of the named stage.

For example, if the root directory of the named stage is /app_files/dev, this directory would contain the following files and directories:

• A manifest.yml file.

• A directory containing the setup script, for example scripts/setup_version.sql.

• Referenced files that are imported when creating a stored procedure, UDF, or external function within the setup script, for example:

  • libraries/jars/lookup.jar

  • libraries/jars/log4j.jar

  • libraries/python/evaluate.py

In this scenario, the directory structure would be as follows:

@DEV_DB.DEV_SCHEMA.DEV_STAGE/V1:
└── app_files/
    └── dev
        ├── manifest.yml
        └── scripts/
            ├── setup_script.sql
            └── libraries/
                └── jars/
                    ├── lookup.jar
                    └── log4j.jar
            └── python
                └── evaluation.py

To access the JAR files in this directory structure, a stored procedure defined in the setup script would reference these files as shown in the following example:

CREATE PROCEDURE PROGRAMS.LOOKUP(...)
 RETURNS STRING
 LANGUAGE JAVA
 PACKAGES = ('com.snowflake:snowpark:latest')
 IMPORTS = ('/scripts/libraries/jar/lookup.jar',
            '/scripts/libraries/jar/log4j.jar')
 HANDLER = 'com.acme.programs.Lookup';

In this example, the IMPORTS statement has a path relative to the root directory used to create the version, for example, the location of the manifest.yml file.

Include Java and Scala code in an application package¶

The Snowflake Native App Framework supports using Java and Scala in stored procedures and in external code files.

CREATE OR ALTER VERSIONED SCHEMA app_code;
CREATE STAGE app_code.app_jars;

CREATE FUNCTION app_code.add(x INT, y INT)
 RETURNS INTEGER
 LANGUAGE JAVA
 HANDLER = 'TestAddFunc.add'
 TARGET_PATH = '@app_code.app_jars/TestAddFunc.jar'
 AS
 $$
   class TestAddFunc {
       public static int add(int x, int y) {
           Return x + y;
       }
   }
 $$;

@DEV_DB.DEV_SCHEMA.DEV_STAGE/V1:
└── V1/
 ├── manifest.yml
 ├── setup_script.sql
 └── JARs/
     ├── Java/
     │   └── TestAddFunc.jar
     └── Scala/
         └── TestMulFunc.jar

CREATE FUNCTION app_code.add(x INTEGER, y INTEGER)
  RETURNS INTEGER
  LANGUAGE JAVA
  HANDLER = 'TestAddFunc.add'
  IMPORTS = ('/JARs/Java/TestAddFunc.jar');

Add Python code to an application package¶

The Snowflake Native App Framework supports using Python in stored procedures and in external code files.

CREATE FUNCTION app_code.py_echo_func(str STRING)
RETURNS STRING
LANGUAGE PYTHON
HANDLER = 'echo'
AS
$$
  def echo(str):
    return "ECHO: " + str
$$;

CREATE FUNCTION PY_PROCESS_DATA_FUNC()
  RETURNS STRING
  LANGUAGE PYTHON
  HANDLER = 'TestPythonFunc.process'
  IMPORTS = ('/python_modules/TestPythonFunc.py',
    '/python_modules/data.csv')

Handle JavaScript errors¶

When using JavaScript within an application package, Snowflake recommends that you catch and handle errors. If not, the error message and stack trace that the error returns are visible to the consumer. To ensure that data content and application logic are kept private, use try/catch blocks in situations where sensitive objects or data is being accessed.

The following example shows a JavaScript stored procedure that catches an error and returns a message:

CREATE OR REPLACE PROCEDURE APP_SCHEMA.ERROR_CATCH()
  RETURNS STRING
  LANGUAGE JAVASCRIPT
  EXECUTE AS OWNER
  AS $$
     try {
      let x = y.length;
     }
     catch(err){
        return "There is an error.";
     }
     return "Done";
  $$;

This example creates a JavaScript stored procedure that contains a try/catch block. If the stored procedure encounters an error when running the statement in the try block, it returns the message “There is an error” which is visible to the consumer.

Without the try/catch block, the stored procedure would return the original error message and the full stack trace which would be visible to the consumer.