SNOW-1045932: Can I use parameters with a begin/end block with .Net Client?

[Coder3333]

I am using the .Net client to execute Snowflake queries. This works fine when I only try to execute a single sql statement with a parameter.

 SELECT TOP 1 Col1     
 FROM Table1 WHERE Col2 = :p1; 

If I remove the parameter and move the query into a begin/end block, that works, too.

 begin
  SELECT TOP 1 Col1     
 FROM Table1; 
 
 return 'value';
 end

However, if I use a begin/end block and keep the parameter, I get an error about the parameter being undefined.

begin

 SELECT TOP 1 Col1     
 FROM Table1 WHERE Col2 = :p1; 

  return 'value';
end;

Should I expect this scenario to work with a parameter in a begin/end block, or is there some reason why this should not work?

[sfc-gh-dszmolka]

hi, thank you for submitting this issue - we'll take a look

[sfc-gh-dszmolka]

As a reproduction attempt, did the following

CREATE OR REPLACE PROCEDURE test(input INT)
  RETURNS FLOAT
  LANGUAGE SQL
  AS
    DECLARE
      result INT;
    BEGIN
      SELECT ID into :result FROM net_gh_860 WHERE ID >= :input;

      return result;
    END;

then

using System;
using System.Data;
using System.Data.Common;
using Snowflake.Data.Client;
namespace SnowflakeTestProgram
{
    class Program
    {
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);
        private static DataTable dbdataTable;
        
        static void Main(string[] args)
        {
            try
            {
                using (IDbConnection conn = new SnowflakeDbConnection())
                {
                    conn.ConnectionString = "account=myaccount.eu-central-1; user=admin; password=password; DB=TEST_DB; SCHEMA=DOTNET; warehouse=COMPUTE_WH";
                    conn.Open();
                    Console.WriteLine("Connection successful!");
                    
                    using (IDbCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = "CALL TEST_DB.PUBLIC.TEST(:1);";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "1";
                        p1.Value = 2;
                        p1.DbType = DbType.Int32;
                        cmd.Parameters.Add(p1);
                        IDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                                Console.WriteLine(reader.GetString(0));

                        }
                        conn.Close();
                    }
                }
            }
            catch (DbException exc)
            {
                Console.WriteLine("Error Message: {0}", exc.Message);
            }
        }
    }
}

properly returns 2 which is the only row in the underlying test table.

Perhaps you're trying to do something entirely different here. If so, could you please provide a detailed reproduction or a runnable script which leads to the issue ? I would like to see if it's a limitation in the Snowflake .NET driver, or the backend itself.

Thank you in advance !

[Coder3333]

@sfc-gh-dszmolka , modify your test in this way.

 using (IDbCommand cmd = conn.CreateCommand())
                    {
                        cmd.CommandText = @"DECLARE
      result INT;
    BEGIN
      SELECT ID into :result FROM net_gh_860 WHERE ID >= :input;

      return result;
    END;";
                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "1";
                        p1.Value = 2;
                        p1.DbType = DbType.Int32;
                        cmd.Parameters.Add(p1);
                        IDataReader reader = cmd.ExecuteReader();
                        while (reader.Read())
                        {
                                Console.WriteLine(reader.GetString(0));

                        }
                        conn.Close();
                    }

Begin/End blocks run just fine as long as there are no parameters. How can I get this to run with a parameter? I am trying to avoid writing a full out procedure that has to be deployed.

[sfc-gh-dszmolka]

okay, got it - thank you! Got to this point:

                        cmd.CommandText = @"
    DECLARE
      INPUT INT;
      RESULT INT;
    BEGIN
      SELECT ID into :RESULT FROM net_gh_860 WHERE ID >= :INPUT;

      return :RESULT;
    END;
    ";

                        var p1 = cmd.CreateParameter();
                        p1.ParameterName = "INPUT";
                        p1.Value = 1;
                        p1.DbType = DbType.Int32;
                        cmd.Parameters.Add(p1);
                        IDataReader reader = cmd.ExecuteReader();

Which does not error out, but does not return anything either. On the Snowflake side, the query is translated into
SELECT ID FROM net_gh_860 WHERE ID >= :INPUT::NUMBER(38,0);

Parameter doesn't seem to be passed. Similarly, this does not even seem to be possible with another Snowflake driver, the Python Connector. Also returns empty result.

You'll need to either create it as a procedure after all, or if thats possible, perhaps dynamically generate the whole anonymous function (+its parameter values) during runtime.

[Coder3333]

@sfc-gh-dszmolka , right now I am just building the query dynamically as a work around, but I wish there was a way to ensure I am not susceptible to sql injection, which parameters would provide.

[sfc-gh-dszmolka]

Good to know that at least you have a workaround here ! I'm now closing this issue since it is not related to the .NET driver as it looks like now but looks like a backend limitation.