Trying to pass parameter as binding variable in snowflake statement

Question

Below is my stored procedure, I'm not sure as to why it keeps throwing an error. The error I get is

SQL compilation error: syntax error line XX at position XX unexpected '?'.

I have followed the documentation here but it does not seem to work for me.

This is what I have:

CREATE OR REPLACE PROCEDURE spExample(INPUT_TABLE VARCHAR)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS
    $$
    result = "";
    try {
        var sql_cmd = "SELECT * FROM ?;";
        var sql_stmt = snowflake.createStatement({sqlText: sql_cmd, binds:[INPUT_TABLE]});
        sql_stmt.execute();
    } catch(err) {
        result += "Message: " + err.message;
    }
    return result;
    $$;

Have I made a mistake somewhere?

score 3 · Answer 1 · answered Mar 08 '21 at 21:43

3

Above answers subject your code to sql injection attack. And of course you can bind a table name to a variable in snowflake.

Do
var sql_cmd = "SELECT * FROM IDENTIFIER('?');";

answered Mar 08 '21 at 21:43

user2555515

779
6
20

score 1 · Answer 2 · edited Mar 15 '21 at 12:00

1

Here is what I tried and it executed perfectly:

CREATE OR REPLACE PROCEDURE spExample(INPUT_TABLE VARCHAR)
    RETURNS VARCHAR
    LANGUAGE JAVASCRIPT
    AS
    $$
    result = "";
    try {
        var sql_cmd = "SELECT * FROM IDENTIFIER(?);";
        var sql_stmt = snowflake.createStatement({sqlText: sql_cmd, binds:[INPUT_TABLE]});
        sql_stmt.execute();
    } catch(err) {
               result += "Message: " + err.message;
    }
    return result;
    $$;
Call spExample('PAIDBILLS');

edited Mar 15 '21 at 12:00

Adrian Mole

49,934
160
51
83

answered Mar 09 '21 at 11:25

sosf

11
1

1

This works for me too, although not with MERGE statement , it seems. I do the same "MERGE INTO IDENTIFIER(?) tgt USING () as src ON src.ID = tgt.ID ..." – neverMind Jan 05 '22 at 15:32

score 0 · Answer 3 · answered Feb 09 '23 at 06:53

For me, this worked, first Quotes then brackets. I think its different for different type of query , other answers worked for me for select but not for SHOW

var sql_cmd = "SHOW WAREHOUSES like '(?)';"

 CREATE OR REPLACE PROCEDURE spExample(INPUT_TABLE VARCHAR)
RETURNS varchar
LANGUAGE JAVASCRIPT
execute as owner
AS
$$
result = "";
try {
    var sql_cmd = "SHOW WAREHOUSES like '(?)';";
    var sql_stmt = snowflake.execute({sqlText: sql_cmd, binds:[INPUT_TABLE]});
} catch(err) {
           result += "Message: " + err.message;
}
return result;
$$;

Call spExample('WREHOUSE NAME');

score -1 · Answer 4 · answered Jul 22 '20 at 07:40

-1

actually yes.

Bind variable is just that, a variable. So you can do a

SELECT * FRMO MY_TABLE WHERE MY_COLUMN=?

but you can't use bind to substitute for commands or column or table names. You can however use simple JS concatenation, like

var sql_cmd = "SELECT * FROM "+INPUT_TABLE;

answered Jul 22 '20 at 07:40

MMV

920
4
9

You're right. I can simply use concatenation but that's the alternative for me. If you scroll down to "right-bind" in the [documentation](https://docs.snowflake.com/en/sql-reference/stored-procedures-usage.html#binding-variables) you can see they are also passing a parameter as a bind variable. I have this working for my another stored procedure but it doesn't work here. I find that very odd. – Addi Jul 22 '20 at 15:41
err, sorry, can you please copypaste the example? because I found the right-bind, and what they are passing is timestamp value that needs conversion..... – MMV Jul 22 '20 at 15:55
Okay this makes sense. I went back and looked at the example that works and it's not a column name or a table name I'm passing. You're right it works for everything else expect for column or table names. Thank you! – Addi Jul 22 '20 at 16:47

score -1 · Answer 5 · edited Feb 09 '21 at 03:39

-1

It seems I had the exact same misunderstanding as the OP. It was good to find this answer.

In any case, it's a lot more flexible & more readable to use JavaScript template literals using backticks (instead of using single quotes or double quotes). They allow you to use expression interpolation in the format of

`Some text here. ${expression} Some more text here.`

Just fill in with your variable or variables (or expression).

edited Feb 09 '21 at 03:39

bguiz

27,371
47
154
243

answered Feb 09 '21 at 02:32

cosmicaug

33
5

2

This is a very bad practice, You should never create an SQL strings dynamically. Use parameters instead as OP asked. – user2555515 Feb 27 '22 at 14:39
1

THIS IS WRONG. NEVER EVER DO THIS UNDER ANY CIRCUMSTANCES! In case you've never seen it before please reference Bobby Tables: https://xkcd.com/327 – Noah Goodrich Feb 03 '23 at 19:39

Trying to pass parameter as binding variable in snowflake statement

5 Answers5