{"id":289,"date":"2018-06-06T08:00:37","date_gmt":"2018-06-06T06:00:37","guid":{"rendered":"http:\/\/cwiok.pl\/?p=289"},"modified":"2018-06-06T14:14:27","modified_gmt":"2018-06-06T12:14:27","slug":"power-bi-sql-injection-attack-f","status":"publish","type":"post","link":"https:\/\/cwiok.pl\/index.php\/en\/2018\/06\/06\/power-bi-sql-injection-attack-f\/","title":{"rendered":"SQL Injection in Power BI Service"},"content":{"rendered":"

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\u0142 Potasi\u0144ski 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<\/a> or watch this video<\/a>. If you want to read more about some M tricks first, try here<\/a> or here<\/a>.<\/p>\n

\"\"<\/p>\n

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:<\/p>\n

\"1\" meta [IsParameterQuery=true, Type=\"Number\", IsParameterQueryRequired=true]<\/pre>\n
 <\/p>\n
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.<\/p>\n
Later in M I have included my SQL code:<\/p>\n
let\r\n\r\nSource = Sql.Database(\"\", \"\", [Query=\"SELECT TOP \" & Text.From(Number_of_rows)&\" * FROM [dbo].[tblAuthors]\"])\r\nin\r\nSource<\/pre>\n
 <\/p>\n
It all worked well. Then together with Pawe\u0142 we changed the type to text and tried to input the parameter value as<\/p>\n
100 * FROM Sys.Tables-- \r\n<\/pre>\n
Because the credentials in that environment were mine it worked without an issue:<\/p>\n
\"\"<\/a><\/p>\n
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:<\/p>\n
\"\"<\/a><\/p>\n
And got an error:<\/p>\n
\"\"<\/a><\/p>\n
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.<\/p>\n
100 name as Author_name, object_id as id, schema_id as country FROM Sys.tables--\"\r\n<\/pre>\n
And it worked perfectly:
\n\"\"<\/a><\/p>\n
However,\u00a0 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:<\/p>\n
\"\"<\/a><\/p>\n
And it worked:
\n\"\"<\/a>
\nIt executes without any errors, which is worrying. According to the documentation<\/a> “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!<\/p>\n
Thanks<\/p>\n","protected":false},"excerpt":{"rendered":"
I know that this case might be obvious for the most of the experienced data engineers and other specialists, but it really surprised me. Our Principal Architect in Clouds on Mars – Pawe\u0142 Potasi\u0144ski pointed me this possibility.
\n 
\n\"\"<\/p>\n
Read More<\/a><\/div>","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[26],"tags":[],"class_list":["post-289","post","type-post","status-publish","format-standard","hentry","category-powerbi"],"_links":{"self":[{"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/posts\/289","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/comments?post=289"}],"version-history":[{"count":0,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/posts\/289\/revisions"}],"wp:attachment":[{"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/media?parent=289"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/categories?post=289"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/cwiok.pl\/index.php\/wp-json\/wp\/v2\/tags?post=289"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}