title: Snowflake dynamic SQL: quoting parameters
body: <p>Based on Snowflake docs:</p>
  <blockquote>
  <p><a href="https://docs.snowflake.net/manuals/sql-reference/stored-procedures-usage.html#label-example-of-dynamic-sql-in-stored-procedure" rel="nofollow noreferrer">Dynamically Creating a SQL Statement</a></p>
  <p><strong>As stated in SQL Injection (in this topic), be careful to guard against attacks when using dynamic SQL.</strong></p>
  <p>Create the stored procedure. This procedure allows you to pass the name of a table and get the number of rows in that table (equivalent to select count(*) from table_name):</p>
  <pre><code>create or replace procedure get_row_count(table_name VARCHAR)
      returns float 
      not null
      language javascript
      as
      $$
      var row_count = 0;
      // Dynamically compose the SQL statement to execute.
      // Note that we uppercased the input parameter name.
      var sql_command = &quot;select count(*) from &quot; + TABLE_NAME;
      // Run the statement.
      var stmt = snowflake.createStatement(
             {
             sqlText: sql_command
             }
          );
      var res = stmt.execute();
      // Get back the row count. Specifically, ...
      // ... first, get the first (and in this case only) row from the
      //  result set ...
      res.next();
      // ... then extract the returned value (which in this case is the
      // number of rows in the table).
      row_count = res.getColumnValue(1);
      return row_count;
      $$
      ;
  </code></pre>
  </blockquote>
  <p>The provided example does not look safe: <code>&quot;select count(*) from &quot; + TABLE_NAME;</code> - simple string concatenation</p>
  <p>So it is possible to run code like:</p>
  <pre><code>CALL get_row_count('TAB');    -- intended use
  CALL get_row_count('(SELECT * FROM TAB UNION ALL SELECT * FROM TAB) s'); -- subquery
  </code></pre>
  <hr />
  <p>First idea was to run code by using <code>TABLE/IDENTIFIER</code> function(in worksheet it would look like):</p>
  <pre><code>SET TABLE_NAME = 'TAB';  -- works
  SET TABLE_NAME = '(SELECT * FROM TAB UNION SELECT * FROM TAB) s'; 
  -- error: error line 1 at position 27 invalid identifier '$TABLE_NAME'
  SELECT COUNT(*) FROM TABLE($TABLE_NAME);
  </code></pre>
  <p>And as part of procedure:</p>
  <pre><code>var sql_command = &quot;SET TABLE_NAME = &quot; + TABLE_NAME + &quot;; SELECT COUNT(*) FROM TABLE($TABLE_NAME);&quot;;
  </code></pre>
  <p>But it won't work since:</p>
  <blockquote>
  <p><em>Execution error in stored procedure GET_ROW_COUNT: <strong>Multiple SQL statements in a single API call are not supported; use one API call per statement instead</strong>.</em></p>
  </blockquote>
  <hr />
  <p>Using <code>TABLE/IDENTIFIER</code> and binding variable:</p>
  <pre><code>create or replace procedure get_row_count(table_name VARCHAR)
  returns float 
  not null
  language javascript
  as
  $$
  var row_count = 0;
  // Dynamically compose the SQL statement to execute.
  // Note that we uppercased the input parameter name.
  var sql_command = &quot;select count(*) from TABLE(:1)&quot;;
  // Run the statement.
  var stmt = snowflake.createStatement(
         {
         sqlText: sql_command,
         binds: [TABLE_NAME]
         }
      );
  var res = stmt.execute();
  res.next();
  row_count = res.getColumnValue(1);
  return row_count;
  $$
  ;
  </code></pre>
  <p>Call:</p>
  <pre><code>CALL get_row_count('TAB'); --works
  CALL get_row_count('(SELECT * FROM TAB UNION ALL SELECT * FROM TAB) s');
  -- SQL compilation error: syntax error line 1 at position 0 unexpected '('.
  </code></pre>
  <p>Questions:</p>
  <ol>
  <li><p>Is it possible to somehow run multiple statements as a single batch(<code>snowflake.createStatement</code>)?</p>
  </li>
  <li><p>Is there is an option to <strong>safely quotename</strong> within JS, sort of:</p>
  <p><code>var sql_command = &quot;select count(*) from &quot; + FUNC(TABLE_NAME);</code></p>
  </li>
  <li><p>Are there any potential flaws with <code>TABLE</code> and binding values approach?</p>
  </li>
  </ol>