how to prevent sql injection in snowflake filter function

Question

Im using input from the user to query snowflake within DRF view, how to prevent in the below code sql injection possibility?

entity_id = kwargs['pk']
table = session.table("my_table").filter(col(ID_COL)==entity_id )

The id is a string, not an integer. @Tim Biegeleisen, I tried to find out something explicit in the documetnation but didnt find. — LiorA
– LiorA, Commented May 26, 2024 at 12:52
@LiorA Most likely, your builder code is internally being backed by a prepared statement, so injection is not a real concern. — Tim Biegeleisen
– Tim Biegeleisen, Commented May 26, 2024 at 13:04
The builder in the snowpark API you meant?. but nothing explicit is mentioned, so we one cant be sure its protecting against injection.. maybe I missing something? — LiorA
– LiorA, Commented May 28, 2024 at 10:13

Felipe Hoffa · Accepted Answer · 2024-05-29 03:25:21Z

1

The good news is that the user input should be already filtered if you are using filter().

Testing with Snowpark:

import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import col

def main(session: snowpark.Session): 
    # Your code goes here, inside the "main" handler.
    tableName = 'information_schema.packages'
    dataframe = session.table(tableName).filter(col("language") == "';drop table bobby_tables")

    # Print a sample of the dataframe to standard output.
    dataframe.show()

    # Return value will appear in the Results tab.
    return dataframe

If you go check the Snowflake logs, you'll find that Snowflake ran the following query, with the quote escaped:

SELECT  *  
FROM information_schema.packages 
WHERE ("LANGUAGE" = ''';drop table bobby_tables')

answered May 29, 2024 at 3:25

Felipe Hoffa

59.8k23 gold badges185 silver badges365 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

LiorA Over a year ago

Thanks, really think they should write something about it in the snowflake documentation.

Collectives™ on Stack Overflow

how to prevent sql injection in snowflake filter function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related