SQL Injection in Power BI Service
While this case might be obvious for the most of the experienced data engineers and other specialists, it really surprised me. Our Principal Architect in Clouds on Mars – Paweł Potasiński pointed me this possibility. Basically, in a controlled environment, it will be possible to inject SQL code through Power BI parameters and do (almost) whatever one pleases. You can read more about this type of attack here or watch this video. If you want to read more about some M tricks first, try here or here.
The task was to enable the change of how many rows from the table are used as a data source. With parameters manipulation being available in Power BI, this was rather an easy task. To set up the parameter I did:
|
1 |
"1" meta [IsParameterQuery=true, Type="Number", IsParameterQueryRequired=true] |
Set this as a decimal number. You must give your parameter a type – it cannot be “Any”. This is crucial, if you want to change the parameter in Power BI Service.
Later in M I have included my SQL code:
|
1 2 3 4 5 |
let Source = Sql.Database("", "", [Query="SELECT TOP " & Text.From(Number_of_rows)&" * FROM [dbo].[tblAuthors]"]) in Source |
It all worked well. Then together with Paweł we changed the type to text and tried to input the parameter value as
|
1 |
100 * FROM Sys.Tables-- |
Because the credentials in that environment were mine it worked without an issue:
I decided to experiment more with it, just to see what I get. After I had published the report to Power BI Service, I changed the parameter in a same fashion as in desktop:
And got an error:
It seems, that Power BI Service expects certain columns when refreshing the data. I decided to try something like this, to cheat it. However, a simple UNION would be also enough.
|
1 |
100 name as Author_name, object_id as id, schema_id as country FROM Sys.tables--" |
And it worked perfectly:
However, the parameter will not always be set before FROM statement. Sometimes it will be in a stored procedure or in WHERE statement. What happens if I try to just input a second SQL statement after a semicolon? Two SELECT statements in a query will get you only the first as an output. If you put anything else, it is going to execute. I decided to try the worst first:
And it worked:
It executes without any errors, which is worrying. According to the documentation “You must be connected to the master database to drop a database” and “The DROP DATABASE statement must be the only statement in a SQL batch and you can drop only one database at a time”, which is not the case. I am still to analyze logs of how Power BI batches the queries sent. Nonetheless, we can treat it as a curiosity, because you cannot change the parameters unless you have access to the database anyway. I will be playing with it more in spare time, but if you manage to cheat Power BI in a similar way, let me know!
Thanks
