Welcome To TechBrothersIT: Snowflake SQL Tutorial 150 - One Quote Mark Turned A Two Row Lookup Into Three

Snowflake SQL Tutorial 150 - One Quote Mark Turned A Two Row Lookup Into Three

Snowflake SQL · Episode 150

In this post we are going to cover EXECUTE IMMEDIATE and dynamic SQL in Snowflake.

EXECUTE IMMEDIATE takes a statement you built as a string and runs it. That is useful, and it has one property worth being careful about: the string is not checked until it runs, and if you built it by joining text together then the caller helped write it.

Those two are really one fact seen twice, and we are going to see both halves with real output.

We will start with the simplest form and build it up step by step. Every query below was run on a real Snowflake account, and every result and every error you see is what it gave back.

Episode 150 of the Snowflake SQL: Zero to Hero series on TechBrothersIT.

On this page

Let's create some sample data
Let's start with the simplest EXECUTE IMMEDIATE
Can it run something that is not a query?
Now let's put it inside a block
So how do we get a single value out into a variable?
So here are the ways that do work
Now let's put a value into the string with a bind
Two things a bind will not do
What can one string hold?
Now the one that matters: nothing reads the string until it runs
And what about an empty string, or a null one?
So here is what late checking costs
And it is not only reading
One last thing: the fix is one question mark
And let's leave the schema the way we found it

Let's create some sample data

First let's see where the session is pointing.

Query

SELECT CURRENT_DATABASE()  AS THE_DATABASE,
       CURRENT_SCHEMA()    AS THE_SCHEMA,
       CURRENT_VERSION()   AS THE_VERSION;

Result1 row · 3 columns

THE_DATABASE	THE_SCHEMA	THE_VERSION
DEMO_DB	PUBLIC	10.35.101

Now a small sales table, and five rows in it.

Sample data

CREATE OR REPLACE TABLE SALES_T150 (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);

Result1 row · 1 column

status
Table SALES_T150 successfully created.

Sample data

INSERT INTO SALES_T150 (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 100.00), (2, 'south', 250.50),
       (3, 'east',   75.25), (4, 'west',  310.00),
       (5, 'north',  45.00);

Result1 row · 1 column

number of rows inserted
5

Let's see what is in there, because every string below is reading these rows.

Query

SELECT COUNT(*) AS HOW_MANY, SUM(AMOUNT) AS TOTAL_AMOUNT FROM SALES_T150;

Result1 row · 2 columns

HOW_MANY	TOTAL_AMOUNT
5	780.75

Five rows and a total of 780.75. That is the table the whole first half of this post works on.

Let's start with the simplest EXECUTE IMMEDIATE

The simplest thing there is. A query, written as a string, handed to EXECUTE IMMEDIATE.

Query

EXECUTE IMMEDIATE 'SELECT COUNT(*) AS HOW_MANY FROM SALES_T150';

Result1 row · 1 column

HOW_MANY
5

Five. It ran, and it came back exactly as if we had typed the query out ourselves. Have a look at the column heading too. The alias from inside the string is the heading we got.

And because it is a query like any other, RESULT_SCAN can read its rows back afterwards.

Query

SELECT HOW_MANY FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

HOW_MANY
5

Five again. So the result of a dynamic statement is not special in any way.

Can it run something that is not a query?

Let's hand it a CREATE TABLE instead.

Query

EXECUTE IMMEDIATE 'CREATE OR REPLACE TABLE MADE_BY_STRING_T150 (NOTE VARCHAR(40))';

Result1 row · 1 column

status
Table MADE_BY_STRING_T150 successfully created.

That is the ordinary status line you get from a CREATE TABLE. So it takes anything, not just SELECTs.

And the table really is there.

Query

SELECT COUNT(*) AS IT_EXISTS
FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND TABLE_NAME = 'MADE_BY_STRING_T150';

Result1 row · 1 column

IT_EXISTS
1

Now let's put it inside a block

Inside a block we can assign what the string produced to a RESULTSET and hand the rows back.

Create the procedure

CREATE OR REPLACE PROCEDURE EIRS_T150()
RETURNS TABLE (SALE_ID INT, THE_REGION VARCHAR)
LANGUAGE SQL
AS
$$
DECLARE
    RS RESULTSET;
BEGIN
    RS := (EXECUTE IMMEDIATE 'SELECT SALE_ID, REGION FROM SALES_T150
                              WHERE SALE_ID <= 2 ORDER BY SALE_ID');
    RETURN TABLE(RS);
END;
$$;

Result1 row · 1 column

status
Function EIRS_T150 successfully created.

Query

CALL EIRS_T150();

Result2 rows · 2 columns

SALE_ID	THE_REGION
1	north
2	south

Two rows. So a RESULTSET holds the output of a string just as happily as it holds the output of a query we typed.

And we do not have to look at the result at all. Let's run one and throw it away.

Create the procedure

CREATE OR REPLACE PROCEDURE EIIGNORE_T150()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM SALES_T150';
    RETURN 'it ran and I did not look';
END;
$$;

Result1 row · 1 column

status
Function EIIGNORE_T150 successfully created.

Query

CALL EIIGNORE_T150();

Result1 row · 1 column

EIIGNORE_T150
it ran and I did not look

No complaint. Running a statement and never reading what it returned is not an error.

So how do we get a single value out into a variable?

That is the reasonable next question, and here is the spelling most of us reach for first. The string, then INTO, then our variable.

Create the procedure

CREATE OR REPLACE PROCEDURE EIINTO_T150()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
    HOW_MANY NUMBER;
BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM SALES_T150' INTO :HOW_MANY;
    RETURN :HOW_MANY;
END;
$$;

Query failed — Snowflake returned an error

001003 (42000): SQL compilation error:
syntax error line 5 at position 56 unexpected 'INTO'.
syntax error line 6 at position 4 unexpected 'RETURN'.

001003, unexpected 'INTO'. There is no INTO clause on EXECUTE IMMEDIATE at all. That is the Oracle and PL/pgSQL spelling.

Now look at what that one word cost us, because this is the part worth keeping. Let's call the procedure.

Query

CALL EIINTO_T150();

Query failed — Snowflake returned an error

002139 (02000): SQL compilation error:
Unknown function EIINTO_T150.

Unknown function. The CREATE failed, so the procedure does not exist, so the call cannot find it.

What a failed CREATE costs

Which means every other thing that procedure was going to tell us is gone with it. One wrong word in a block does not cost you a line. It costs you the whole block, and the second error you see is only the first one arriving again in a different shape.

So here are the ways that do work

This part of the demo was captured a second time, on its own copy of the sales table called SALES_T150B, with the same five rows in it.

Sample data

CREATE OR REPLACE TABLE SALES_T150B (
    SALE_ID INT,
    REGION  VARCHAR(10),
    AMOUNT  NUMBER(10,2)
);

Result1 row · 1 column

status
Table SALES_T150B successfully created.

Sample data

INSERT INTO SALES_T150B (SALE_ID, REGION, AMOUNT)
VALUES (1, 'north', 100.00), (2, 'south', 250.50),
       (3, 'east',   75.25), (4, 'west',  310.00),
       (5, 'north',  45.00);

Result1 row · 1 column

number of rows inserted
5

The first way that works is the one to learn. Assign the statement to a RESULTSET and walk it with a FOR loop.

Create the procedure

CREATE OR REPLACE PROCEDURE SCALARWALK_T150B()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
    RS       RESULTSET;
    HOW_MANY NUMBER DEFAULT -1;
    GUARD    NUMBER DEFAULT 0;
BEGIN
    RS := (EXECUTE IMMEDIATE 'SELECT COUNT(*) AS N FROM SALES_T150B');
    FOR R IN RS DO
        HOW_MANY := R.N;
        GUARD    := :GUARD + 1;
        IF (:GUARD >= 10) THEN BREAK; END IF;
    END FOR;
    RETURN :HOW_MANY;
END;
$$;

Result1 row · 1 column

status
Function SCALARWALK_T150B successfully created.

Query

CALL SCALARWALK_T150B();

Result1 row · 1 column

SCALARWALK_T150B
5

Five, out as a number. That is the one I would use.

The second way is to run it bare and then read the last query back. Note the INTO in there, on a SELECT, where it is perfectly welcome.

Create the procedure

CREATE OR REPLACE PROCEDURE SCALARSCAN_T150B()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
    HOW_MANY NUMBER DEFAULT -1;
BEGIN
    EXECUTE IMMEDIATE 'SELECT COUNT(*) AS N FROM SALES_T150B';
    SELECT N INTO :HOW_MANY FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));
    RETURN :HOW_MANY;
END;
$$;

Result1 row · 1 column

status
Function SCALARSCAN_T150B successfully created.

Query

CALL SCALARSCAN_T150B();

Result1 row · 1 column

SCALARSCAN_T150B
5

Five as well. Two good answers.

And one that looks right and is not

A cursor for the RESULTSET, declared before the RESULTSET is given its query.

Create the procedure

CREATE OR REPLACE PROCEDURE SCALARFETCH_T150B()
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
    RS       RESULTSET;
    C1       CURSOR FOR RS;
    HOW_MANY NUMBER DEFAULT -1;
BEGIN
    RS := (EXECUTE IMMEDIATE 'SELECT COUNT(*) AS N FROM SALES_T150B');
    OPEN C1;
    FETCH C1 INTO HOW_MANY;
    CLOSE C1;
    RETURN :HOW_MANY;
END;
$$;

Result1 row · 1 column

status
Function SCALARFETCH_T150B successfully created.

Query

CALL SCALARFETCH_T150B();

Query failed — Snowflake returned an error

092212 (P0000): Uncaught exception of type 'STATEMENT_ERROR' on line 4 at position 24 : Stored procedure execution error: uninitialized resultSet 'RS'

092212, uninitialized resultSet. CURSOR FOR binds to the RESULTSET as it stands when it is declared, and the assignment came afterwards, so it never reached the cursor.

Now let's put a value into the string with a bind

This is the part everything else in the post depends on. A question mark in the string, and a USING clause to fill it.

Create the procedure

CREATE OR REPLACE PROCEDURE BINDQ_T150B(THE_REGION VARCHAR)
RETURNS TABLE (SALE_ID INT, THE_AMOUNT NUMBER(10,2))
LANGUAGE SQL
AS
$$
DECLARE
    RS RESULTSET;
BEGIN
    RS := (EXECUTE IMMEDIATE
           'SELECT SALE_ID, AMOUNT FROM SALES_T150B WHERE REGION = ? ORDER BY SALE_ID'
           USING (THE_REGION));
    RETURN TABLE(RS);
END;
$$;

Result1 row · 1 column

status
Function BINDQ_T150B successfully created.

Query

CALL BINDQ_T150B('north');

Result2 rows · 2 columns

SALE_ID	THE_AMOUNT
1	100.00
5	45.00

Two rows, and they are the two north ones. So binds work.

There is another spelling for the bind marker, :1 instead of ?. Let's try it.

Create the procedure

CREATE OR REPLACE PROCEDURE BINDN_T150B(THE_REGION VARCHAR)
RETURNS TABLE (SALE_ID INT, THE_AMOUNT NUMBER(10,2))
LANGUAGE SQL
AS
$$
DECLARE
    RS RESULTSET;
BEGIN
    RS := (EXECUTE IMMEDIATE
           'SELECT SALE_ID, AMOUNT FROM SALES_T150B WHERE REGION = :1 ORDER BY SALE_ID'
           USING (THE_REGION));
    RETURN TABLE(RS);
END;
$$;

Result1 row · 1 column

status
Function BINDN_T150B successfully created.

Query

CALL BINDN_T150B('north');

Result2 rows · 2 columns

SALE_ID	THE_AMOUNT
1	100.00
5	45.00

The same two rows. Both spellings are fine.

Now two markers in one string, and two values in the USING clause.

Create the procedure

CREATE OR REPLACE PROCEDURE BIND2_T150B(THE_REGION VARCHAR, CUTOFF NUMBER)
RETURNS TABLE (SALE_ID INT, THE_AMOUNT NUMBER(10,2))
LANGUAGE SQL
AS
$$
DECLARE
    RS RESULTSET;
BEGIN
    RS := (EXECUTE IMMEDIATE
           'SELECT SALE_ID, AMOUNT FROM SALES_T150B WHERE REGION = ? AND AMOUNT > ? ORDER BY SALE_ID'
           USING (THE_REGION, CUTOFF));
    RETURN TABLE(RS);
END;
$$;

Result1 row · 1 column

status
Function BIND2_T150B successfully created.

Query

CALL BIND2_T150B('north', 50);

Result1 row · 2 columns

SALE_ID	THE_AMOUNT
1	100.00

One row. Both values arrived, and they arrived in the order we wrote them, the region first and the cutoff second.

And it does not need a `RESULTSET` at all

Here is a bare EXECUTE IMMEDIATE with USING, on an UPDATE.

Create the procedure

CREATE OR REPLACE PROCEDURE BINDBARE_T150B(THE_ID NUMBER)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    EXECUTE IMMEDIATE
        'UPDATE SALES_T150B SET REGION = ''bound'' WHERE SALE_ID = ?'
        USING (THE_ID);
    RETURN 'the bound update ran';
END;
$$;

Result1 row · 1 column

status
Function BINDBARE_T150B successfully created.

Query

CALL BINDBARE_T150B(5);

Result1 row · 1 column

BINDBARE_T150B
the bound update ran

And the row really changed.

Query

SELECT SALE_ID, REGION FROM SALES_T150B WHERE SALE_ID = 5;

Result1 row · 2 columns

SALE_ID	REGION
5	bound

Sale five is called bound now. So USING is not a RESULTSET feature. It belongs to EXECUTE IMMEDIATE.

Two things a bind will not do

The first is a :COLON variable written inside the string, with no USING at all. That looks like it should pick up the block's own variable.

Create the procedure

CREATE OR REPLACE PROCEDURE COLONSTR_T150B()
RETURNS TABLE (SALE_ID INT)
LANGUAGE SQL
AS
$$
DECLARE
    THE_REGION VARCHAR DEFAULT 'north';
    RS         RESULTSET;
BEGIN
    RS := (EXECUTE IMMEDIATE
           'SELECT SALE_ID FROM SALES_T150B WHERE REGION = :THE_REGION ORDER BY SALE_ID');
    RETURN TABLE(RS);
END;
$$;

Result1 row · 1 column

status
Function COLONSTR_T150B successfully created.

Query

CALL COLONSTR_T150B();

Query failed — Snowflake returned an error

002049 (42601): Uncaught exception of type 'STATEMENT_ERROR' on line 6 at position 11 : SQL compilation error: error line 1 at position 47
Bind variable :THE_REGION not set.

002049, bind variable not set. The string cannot see your variables by name. It is a string, and whatever is inside it is text until USING hands it a value.

The second is a table name. Let's ask a bind to supply one.

Create the procedure

CREATE OR REPLACE PROCEDURE BINDTABLE_T150B(THE_TABLE VARCHAR)
RETURNS TABLE (SALE_ID INT)
LANGUAGE SQL
AS
$$
DECLARE
    RS RESULTSET;
BEGIN
    RS := (EXECUTE IMMEDIATE 'SELECT SALE_ID FROM ? ORDER BY SALE_ID'
           USING (THE_TABLE));
    RETURN TABLE(RS);
END;
$$;

Result1 row · 1 column

status
Function BINDTABLE_T150B successfully created.

Query

CALL BINDTABLE_T150B('SALES_T150B');

Query failed — Snowflake returned an error

001003 (42000): Uncaught exception of type 'STATEMENT_ERROR' on line 5 at position 11 : SQL compilation error:
syntax error line 1 at position 20 unexpected '?'.

001003, unexpected '?'. A bind is a value and only ever a value. The parser needs to know what table it is reading before it can plan anything, and a bind arrives too late for that.

For a name, the answer is `IDENTIFIER()`

Create the procedure

CREATE OR REPLACE PROCEDURE EIIDENT_T150(THE_TABLE VARCHAR)
RETURNS NUMBER
LANGUAGE SQL
AS
$$
DECLARE
    HOW_MANY NUMBER;
BEGIN
    SELECT COUNT(*) INTO :HOW_MANY FROM IDENTIFIER(:THE_TABLE);
    RETURN :HOW_MANY;
END;
$$;

Result1 row · 1 column

status
Function EIIDENT_T150 successfully created.

Query

CALL EIIDENT_T150('SALES_T150');

Result1 row · 1 column

EIIDENT_T150
5

Five, off the first table. And notice there is no dynamic string in that procedure at all. IDENTIFIER() took the name and the query stayed an ordinary query.

Values and names

So build your statements with binds, and your names with IDENTIFIER(). Keep that pair in your head, because the reason a bind cannot be a table name turns out to be the same reason it cannot be an attack. We get to that at the end.

What can one string hold?

Let's write a block whose string creates a table, and then a later statement in the same block that uses it.

Create the procedure

CREATE OR REPLACE PROCEDURE EIDDL_T150()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    HOW_MANY NUMBER;
BEGIN
    EXECUTE IMMEDIATE
        'CREATE OR REPLACE TABLE INBLOCK_T150 (NOTE VARCHAR(40))';
    EXECUTE IMMEDIATE
        'INSERT INTO INBLOCK_T150 (NOTE) VALUES (''made inside the block'')';
    SELECT COUNT(*) INTO :HOW_MANY FROM INBLOCK_T150;
    RETURN 'the table has ' || :HOW_MANY || ' row';
END;
$$;

Result1 row · 1 column

status
Function EIDDL_T150 successfully created.

Query

CALL EIDDL_T150();

Result1 row · 1 column

EIDDL_T150
the table has 1 row

So the rest of the block can see what the string made. And it outlived the call too.

Query

SELECT NOTE FROM INBLOCK_T150;

Result1 row · 1 column

NOTE
made inside the block

Now two statements in one string, with a semicolon between them.

Create the procedure

CREATE OR REPLACE PROCEDURE EITWO_T150()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    EXECUTE IMMEDIATE
        'INSERT INTO INBLOCK_T150 (NOTE) VALUES (''one''); INSERT INTO INBLOCK_T150 (NOTE) VALUES (''two'')';
    RETURN 'both ran';
END;
$$;

Result1 row · 1 column

status
Function EITWO_T150 successfully created.

Query

CALL EITWO_T150();

Query failed — Snowflake returned an error

000008 (0A000): Uncaught exception of type 'STATEMENT_ERROR' on line 3 at position 4 : Actual statement count 2 did not match the desired statement count 1.

000008, and that message is unusually clear about what it wanted. One string, one statement.

And neither of the two got in.

Query

SELECT COUNT(*) AS ROWS_IN_IT FROM INBLOCK_T150;

Result1 row · 1 column

ROWS_IN_IT
1

One row, which is the one the earlier block put there. Neither INSERT from the two-statement string ran.

Now the one that matters: nothing reads the string until it runs

Let's write a procedure whose string is not SQL at all. Have a look at the spelling of SELECT in there.

Create the procedure

CREATE OR REPLACE PROCEDURE EIBROKEN_T150()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    EXECUTE IMMEDIATE 'SELEKT COUNT(*) FROM SALES_T150';
    RETURN 'never reached';
END;
$$;

Result1 row · 1 column

status
Function EIBROKEN_T150 successfully created.

Created successfully. Nothing looked at that string.

Query

SELECT 'if the create above succeeded, the string was never parsed' AS WHAT_TO_WATCH;

Result1 row · 1 column

WHAT_TO_WATCH
if the create above succeeded, the string was never parsed

So it fails when we call it, and not before.

Query

CALL EIBROKEN_T150();

Query failed — Snowflake returned an error

001003 (42000): Uncaught exception of type 'STATEMENT_ERROR' on line 3 at position 4 : SQL compilation error:
syntax error line 1 at position 0 unexpected 'SELEKT'.

Unexpected 'SELEKT'. That is the whole point of this episode in one pair of results. A CREATE PROCEDURE that succeeds tells you nothing at all about the strings inside it.

A handler catches it like any other error, and SQLERRM carries the whole complaint, misspelling and all.

Create the procedure

CREATE OR REPLACE PROCEDURE EICAUGHT_T150()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    EXECUTE IMMEDIATE 'SELEKT COUNT(*) FROM SALES_T150';
    RETURN 'never reached';
EXCEPTION
    WHEN OTHER THEN
        RETURN 'caught it: ' || SQLERRM;
END;
$$;

Result1 row · 1 column

status
Function EICAUGHT_T150 successfully created.

Query

CALL EICAUGHT_T150();

Result1 row · 1 column

EICAUGHT_T150
caught it: SQL compilation error: syntax error line 1 at position 0 unexpected 'SELEKT'.

And what about an empty string, or a null one?

Let's try both, each with a handler so we can read the message.

Create the procedure

CREATE OR REPLACE PROCEDURE EIEMPTY_T150()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
BEGIN
    EXECUTE IMMEDIATE '';
    RETURN 'the empty string ran';
EXCEPTION
    WHEN OTHER THEN
        RETURN 'the empty string failed: ' || SQLERRM;
END;
$$;

Result1 row · 1 column

status
Function EIEMPTY_T150 successfully created.

Query

CALL EIEMPTY_T150();

Result1 row · 1 column

EIEMPTY_T150
the empty string failed: Executing NULL or empty statement is not permitted.

Create the procedure

CREATE OR REPLACE PROCEDURE EINULL_T150()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    STMT VARCHAR;
BEGIN
    EXECUTE IMMEDIATE :STMT;
    RETURN 'the null string ran';
EXCEPTION
    WHEN OTHER THEN
        RETURN 'the null string failed: ' || SQLERRM;
END;
$$;

Result1 row · 1 column

status
Function EINULL_T150 successfully created.

Query

CALL EINULL_T150();

Result1 row · 1 column

EINULL_T150
the null string failed: Executing NULL or empty statement is not permitted.

The same sentence both times. Snowflake treats a null statement and an empty one as the same refusal.

That is worth knowing if you build your statement by concatenation, because a null anywhere in a concatenation makes the whole thing null. You will not get a complaint about the null. You will get this.

So here is what late checking costs

Everything so far has been about the string not being looked at until it runs. Now let's see why that matters.

A small table, with one row in it that is meant to stay hidden. This part of the demo was captured on its own copy too, called VICTIM_T150B.

Sample data

CREATE OR REPLACE TABLE VICTIM_T150B (
    ROW_ID INT,
    SECRET VARCHAR(20),
    NOTE   VARCHAR(40)
);

Result1 row · 1 column

status
Table VICTIM_T150B successfully created.

Sample data

INSERT INTO VICTIM_T150B (ROW_ID, SECRET, NOTE)
VALUES (1, 'public',  'anyone may read this'),
       (2, 'public',  'and this'),
       (3, 'private', 'this row is not for the caller');

Result1 row · 1 column

number of rows inserted
3

Query

SELECT ROW_ID, SECRET, NOTE FROM VICTIM_T150B ORDER BY ROW_ID;

Result3 rows · 3 columns

ROW_ID	SECRET	NOTE
1	public	anyone may read this
2	public	and this
3	private	this row is not for the caller

Three rows. Two marked public, one marked private.

And here is a lookup that takes a secret and joins it into the statement between a pair of quote marks. This is the shape a lot of real procedures are written in.

Create the procedure

CREATE OR REPLACE PROCEDURE UNSAFE_T150B(THE_SECRET VARCHAR)
RETURNS TABLE (ROW_ID INT, NOTE VARCHAR)
LANGUAGE SQL
AS
$$
DECLARE
    RS   RESULTSET;
    STMT VARCHAR;
BEGIN
    STMT := 'SELECT ROW_ID, NOTE FROM VICTIM_T150B WHERE SECRET = ''' ||
            :THE_SECRET || ''' ORDER BY ROW_ID';
    RS := (EXECUTE IMMEDIATE :STMT);
    RETURN TABLE(RS);
END;
$$;

Result1 row · 1 column

status
Function UNSAFE_T150B successfully created.

Called honestly, with public, it does exactly the right thing.

Query

CALL UNSAFE_T150B('public');

Result2 rows · 2 columns

ROW_ID	NOTE
1	anyone may read this
2	and this

Query

SELECT COUNT(*) AS ROWS_RETURNED FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

ROWS_RETURNED
2

Two rows, and the private one is not among them. That is the function working.

Now the same function, with a quote mark in the argument

The argument is public, then a quote mark to close the one the procedure opened, then OR 1=1, then two dashes to comment out whatever came after.

Query

CALL UNSAFE_T150B('public'' OR 1=1 --');

Result3 rows · 2 columns

ROW_ID	NOTE
1	anyone may read this
2	and this
3	this row is not for the caller

Query

SELECT COUNT(*) AS ROWS_RETURNED FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

ROWS_RETURNED
3

Three rows, and the third one is the row marked private.

Three against two, from the same procedure, with nothing changed but the argument. And the function did not break. It did exactly what the statement said, because by the time the statement existed the argument was part of it.

And it is not only reading

Here is an UPDATE written the same way. This one was captured in the first pass of the demo, on its own copy of the table called VICTIM_T150, holding the same three rows.

Create the procedure

CREATE OR REPLACE PROCEDURE UNSAFEUPD_T150(THE_NOTE VARCHAR, THE_ID VARCHAR)
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    STMT VARCHAR;
BEGIN
    STMT := 'UPDATE VICTIM_T150 SET NOTE = ''' || :THE_NOTE ||
            ''' WHERE ROW_ID = ' || :THE_ID;
    EXECUTE IMMEDIATE :STMT;
    RETURN 'ran: ' || :STMT;
END;
$$;

Result1 row · 1 column

status
Function UNSAFEUPD_T150 successfully created.

It hands back the statement it ran, so we can read it. Called properly first.

Query

CALL UNSAFEUPD_T150('edited properly', '1');

Result1 row · 1 column

UNSAFEUPD_T150
ran: UPDATE VICTIM_T150 SET NOTE = 'edited properly' WHERE ROW_ID = 1

The statement ends at WHERE ROW_ID = 1, which is one row. Now let's call it with 1 OR 1=1 as the id, and read the end of the statement it actually ran.

Query

CALL UNSAFEUPD_T150('edited by the payload', '1 OR 1=1');

Result1 row · 1 column

UNSAFEUPD_T150
ran: UPDATE VICTIM_T150 SET NOTE = 'edited by the payload' WHERE ROW_ID = 1 OR 1=1

The WHERE clause grew. And this is what it did to the table.

Query

SELECT ROW_ID, SECRET, NOTE FROM VICTIM_T150 ORDER BY ROW_ID;

Result3 rows · 3 columns

ROW_ID	SECRET	NOTE
1	public	edited by the payload
2	public	edited by the payload
3	private	edited by the payload

All three rows rewritten, including the private one. The id was taken as a VARCHAR and pasted straight into the statement, so it could be anything at all.

One last thing: the fix is one question mark

Here is the same lookup, written with a bind instead of joined text. Nothing else about it changes.

Create the procedure

CREATE OR REPLACE PROCEDURE SAFE_T150B(THE_SECRET VARCHAR)
RETURNS TABLE (ROW_ID INT, NOTE VARCHAR)
LANGUAGE SQL
AS
$$
DECLARE
    RS RESULTSET;
BEGIN
    RS := (EXECUTE IMMEDIATE
           'SELECT ROW_ID, NOTE FROM VICTIM_T150B WHERE SECRET = ? ORDER BY ROW_ID'
           USING (THE_SECRET));
    RETURN TABLE(RS);
END;
$$;

Result1 row · 1 column

status
Function SAFE_T150B successfully created.

Called honestly it does the same job as before.

Query

CALL SAFE_T150B('public');

Result2 rows · 2 columns

ROW_ID	NOTE
1	anyone may read this
2	and this

Query

SELECT COUNT(*) AS ROWS_RETURNED FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

ROWS_RETURNED
2

Two rows. No difference at all for the honest caller.

Now the identical attack, the same argument character for character.

Query

CALL SAFE_T150B('public'' OR 1=1 --');

Result0 rows · 2 columns

ROW_ID	NOTE

Query

SELECT COUNT(*) AS ROWS_RETURNED FROM TABLE(RESULT_SCAN(LAST_QUERY_ID()));

Result1 row · 1 column

ROWS_RETURNED
0

Nothing. Zero rows, against the three the concatenating one gave.

So where did the attack go? It did not go anywhere. Let's ask the table how many rows have that whole thing as their secret.

Query

SELECT COUNT(*) AS ROWS_WITH_THAT_LITERAL_SECRET
FROM VICTIM_T150B
WHERE SECRET = 'public'' OR 1=1 --';

Result1 row · 1 column

ROWS_WITH_THAT_LITERAL_SECRET
0

None of them. The bound version went looking for the entire argument as one value, found no row whose secret is that, and handed back nothing.

Why the bind wins

There is no escaping here, no sanitising, and no list of bad words. The quote mark never got to be a quote mark, because the statement was finished before the value arrived. That is the whole difference between the two procedures, and it is one character of typing.

And let's leave the schema the way we found it

The demo script drops everything it made. Here is the check afterwards.

Query

SELECT CURRENT_DATABASE()    AS THE_DATABASE,
       CURRENT_SCHEMA()      AS THE_SCHEMA,
       CURRENT_TRANSACTION() AS ANY_OPEN_TRANSACTION;

Result1 row · 3 columns

THE_DATABASE	THE_SCHEMA	ANY_OPEN_TRANSACTION
DEMO_DB	PUBLIC	NULL

Query

SELECT COUNT(*) AS PROBE_TABLES_LEFT
FROM DEMO_DB.INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = 'PUBLIC' AND ENDSWITH(TABLE_NAME, '_T150');

Result1 row · 1 column

PROBE_TABLES_LEFT
0

Same database and schema, no transaction open, and nothing whose name ends in _T150 is left in there.

Key points

EXECUTE IMMEDIATE runs a statement you built as a string, query or not, and the result is an ordinary result that RESULT_SCAN can read back.
There is no INTO clause on EXECUTE IMMEDIATE. That spelling is 001003, and because it kills the CREATE, it takes the whole procedure with it.
To get one value out, assign the statement to a RESULTSET and walk it, or run it bare and read RESULT_SCAN(LAST_QUERY_ID()).
CURSOR FOR a RESULTSET binds when it is declared, so assigning the RESULTSET afterwards never reaches the cursor. That is 092212.
Binds work. ? or :1 with USING, and two of them arrive in the order you wrote them. USING needs no RESULTSET.
A :COLON variable inside the string does not work. The string cannot see your block's variables by name.
A bind cannot be a table name. For a name the answer is IDENTIFIER().
One string holds one statement. Two is 000008, and neither of them runs.
A string that is not SQL at all is created without a complaint and fails only when you call it. Nothing reads that text until the moment it runs.
An empty string and a null string are refused with the same sentence, which is what a concatenation with a null in it turns into.
A lookup that joins its argument into the statement gave two rows for an honest argument and three for an argument with a quote mark in it, including the row that was meant to stay hidden.
Written the same way, an UPDATE rewrote every row in the table.
The same lookup with a bind gave the honest caller the same two rows and the attack nothing at all. The quote never got to be a quote.

Next in this series

In the next post we drop the CREATE and the CALL altogether: anonymous procedures, a block you run once and that leaves nothing behind. Except, as we will see, for the row it wrote.

Every query, every result grid and every error pane on this page was captured from a live Snowflake account (version 10.35.101) running the demo script for this episode. The first pass of that script wrote its scalar and bind procedures with EXECUTE IMMEDIATE ... INTO, so every one of them failed to be created and every call to one of them is an unknown function. Those questions were asked again in a second pass on its own tables, which is why the working forms above end in B. Every loop on this page carries a guard, which is in the bodies shown. Every count in the text is read off one of the grids on this page. No account identifier, user name or query id appears anywhere on this page. The injection payloads only read rows and rewrote one NOTE column, on tables the capture created and dropped. Nothing is timed and no performance claims are made anywhere in this series. Behaviour follows the Snowflake EXECUTE IMMEDIATE documentation.

Welcome To TechBrothersIT

Label

Snowflake SQL Tutorial 150 - One Quote Mark Turned A Two Row Lookup Into Three

Let's create some sample data

Let's start with the simplest EXECUTE IMMEDIATE

Can it run something that is not a query?

Now let's put it inside a block

So how do we get a single value out into a variable?

So here are the ways that do work

And one that looks right and is not

Now let's put a value into the string with a bind

And it does not need a `RESULTSET` at all

Two things a bind will not do

For a name, the answer is `IDENTIFIER()`

What can one string hold?

Now the one that matters: nothing reads the string until it runs

And what about an empty string, or a null one?

So here is what late checking costs

Now the same function, with a quote mark in the argument

And it is not only reading

One last thing: the fix is one question mark

And let's leave the schema the way we found it

Key points

No comments:

Post a Comment

Label

Snowflake SQL Tutorial 150 - One Quote Mark Turned A Two Row Lookup Into Three

Let's create some sample data

Let's start with the simplest EXECUTE IMMEDIATE

Can it run something that is not a query?

Now let's put it inside a block

So how do we get a single value out into a variable?

So here are the ways that do work

And one that looks right and is not

Now let's put a value into the string with a bind

And it does not need a RESULTSET at all

Two things a bind will not do

For a name, the answer is IDENTIFIER()

What can one string hold?

Now the one that matters: nothing reads the string until it runs

And what about an empty string, or a null one?

So here is what late checking costs

Now the same function, with a quote mark in the argument

And it is not only reading

One last thing: the fix is one question mark

And let's leave the schema the way we found it

Key points

No comments:

Post a Comment

And it does not need a `RESULTSET` at all

For a name, the answer is `IDENTIFIER()`