Custom SQL in Tableau: Subqueries and SQL Injection

I recently answered a question on the Tableau Community forums that arose from confusion over why some (perfectly correct) SQL wasn't working as custom SQL in Tableau. The poster wanted a list of Tableau's supported syntax.

But as it turns out, that's the wrong question: Tableau doesn't have a list of all the custom SQL syntax it supports because it really is just passing along the SQL code as you've typed it.

So why would a perfectly reasonable custom query fail? And what's the link to SQL injection? Read on!

The Issue

To explore this, I'm going to use a really simple table and my favorite database system, SQL Server. Here's the schema for the table:

students(name, class)

Now, if I put a very simple query into Tableau, I get exactly what I'd expect:

But if I try something wee bit more interesting, Tableau gives me an error:

What gives? How is Tableau screwing up my query? This works fine if I run it directly against SQL Server:

As it happens, the error doesn't come from Tableau: it comes from SQL Server—because the query is wrong.

What Gives?

When you put custom SQL into Tableau, Tableau passes it along nearly unadulterated to the target system. But by "nearly unadaulterated" I mean "wrapped in a subquery".

So, for example, when I enter:

select class, count(*) 
from students 
group by class

What actually gets passed along to SQL Server is:

SELECT TOP 1 *
FROM (
select class, count(*)
from students
group by class
) [Custom SQL Query]

You can see our query in there, but it isn't issued by itself: it's wrapped in an outer SELECT and lives on as a subquery creatively named "Custom SQL Query".

I'll come back to why this outer query is there in a second. But first, let's take this whole query and try running it in SQL Server directly:

This error looks familiar: It's telling us that the count(*) statement in the select needs to have a column name if it's going to live in a subquery. If we fix this by giving the count a name...

FROM (
select class, count(*) a_name
from students
group by class
) [Custom SQL Query]

...then the query will run correctly SQL Server—and the inner bit will work in Tableau:

One lesson here is that if you're trying to debug why your query isn't working in Tableau, you can wrap it as a subquery and try debugging it in the underlying database directly. Once you have it working in that context, it will probably work as custom SQL in Tableau.

Oh, and why the SELECT TOP 1 *? The first thing Tableau wants to do when it's faced with custom SQL is to get the resulting schema, and fetching a single row is a good way to do this. If this succeeds, Tableau will use your query in other combinations, but always as a subquery.

A SQL Injection?

If all of this makes you think of SQL injection, you're not crazy. A SQL injection attack works by letting someone provide code that gets naively splatted into a SQL statement that's sent along to the database—and that's a lot like what's going on here.

Obligatory XKCD Reference

I'm sure that someone out there can do something a whole lot slicker (and more nefarious) than this simple example, but what if we put this garbage SQL into our custom SQL?

select 1 as foo) [Custom SQL Query]
create table SUPER_NEFARIOUS_TABLE (EVIL_COLUMN int)
select 1 from (select 1 as foo

This is clearly malformed SQL all by itself, but remember that it's going to be inserted into another query:

SELECT TOP 1 *
FROM (
select 1 as foo) [Custom SQL Query]
create table SUPER_NEFARIOUS_TABLE (EVIL_COLUMN int)
select 1 from (select 1 as foo
) [Custom SQL Query]

Now this looks more like SQL. In fact, it's three separate SQL commands—I'll color code them:

SELECT TOP 1 *
FROM (
select 1 as foo) [Custom SQL Query]
create table SUPER_NEFARIOUS_TABLE (EVIL_COLUMN int)
select 1 from (select 1 as foo
) [Custom SQL Query]

What happens when Tableau executes this? It gets its data from the first query, but the second and third queries run as well, and number two is a little suspicious. That red bit will actually create a table on your target database—assuming you have the proper permissions—right from custom SQL.

Of course, here I'm just creating a table, but I could drop one just as easily.

Stay Safe

Here's the good news: I'm just not that clever.

While I've injected some potentially nefarious SQL into my custom SQL, so far all I've really done is stage a SQL injection attack on myself.

Naturally, this would be harmful if I could get you to run the query. What if I sent you a present: a nice, simple, harmless, cuddly, nonthreatening .twb...

...with laser beams! Or better yet, nasty custom SQL.

But (alas!) I'm not the first one to figure out this potential attack vector, and some smart engineers at Tableau decided to warn you when custom SQL is present in your .twb. So when you get a suspicious workbook—any workbook with custom SQL—you'll be confronted with a warning like this:

If you see this warning, you should take note and make sure you understand what that custom SQL is doing before you proceed.

And to be doubly safe, don't accept any .twb from me.