SQL Injection has the potential to ruin your database. If you have heard of the webcomic xkcd, then you have most likely heard about Little Bobby Tables, or by his full name Robert'); DROP TABLE Students;-- . Let’s see how Snowflake handles the input of this student.

Little Bobby Tables

The webcomic about Little Bobby Tables is a cautionary tale for SQL injection. This means that the user input or files loaded may consist of SQL code that is recognized by your language. In this example, a mischievous hacker mom has given her son a name that is part of a SQL command. For a full explanation of the comic, look here.

Based on this comic, there is another website bobby-tables.com that is a guide to prevent injection in various programming languages. There is a section that gives us an example how to avoid injection for SQL. Since Snowflake technically uses Snowflake Scripting (based on SQL, but with some differences) I am curious what will happen if we try to input Little Bobby Tables into a Snowflake table.

Try it in Snowflake

We first create a students table and fill it with some previous data. Let’s say that the table has a column for name, and at least one other column such as age.

CREATE OR REPLACE TABLE students (name VARCHAR, age INT);

INSERT INTO students (name, age) 
VALUES ('Katlin', 4)
       , ('Marcelo', 4)
       , ('Ginger', 5)
       , ('Burl', 4)
       , ('Barbee', 5); 

If we run the command SELECT * FROM students; we will get the following output.

Now, if we want to add Little Bobby Tables to this table (with full name), the code will look like this.

INSERT INTO students (name, age) 
VALUES ('Robert'); DROP TABLE Students;-- , 4);

Luckily for us, this does not run and therefore does not drop our students table. It throws the following error.

There is a SQL compilation error because we specify to expect two columns by adding (name, age) after the name of the table. However, the SQL injection ends the command and the age value is never read. This is one of the reasons why specifying which columns to expect is best practice. We got lucky though. If the SQL injection was the last column that we are trying to insert, this would not have saved us. The code below will drop the students_reversed table.

CREATE OR REPLACE TABLE students_reversed (age INT, name VARCHAR);

INSERT INTO students_reversed (age, name) 
VALUES (4, 'Katlin')
       , (4, 'Marcelo')
       , (5, 'Ginger')
       , (4, 'Burl')
       , (5, 'Barbee'); 

SELECT * FROM students_reversed;

INSERT INTO students_reversed (age, name) 
VALUES (4, 'Robert'); DROP TABLE Students;--');

What now?

As mentioned in this blog about Time Travel, there are unique Snowflake functions that make dropping a table way less dramatic then it would be in SQL. Even though, in my next blog I will talk about data validation in Snowflake and if we can prevent this mischievous hacker mom from injecting SQL in our program.