SQL Injection

(0) Share

Report

Posted on by EpicTriffid

345

Good morning all,

Due to circumstances with my institution, I ended up being the main database architect for our PowerApp. The creation itself was smooth, and incredibly fun to learn as I go (Yes, I was that new). I have one DB, with 4 tables, on Azure, which is hosted by my institutions IT department. I run Stored Procedures via Flow to update certain tables with values from other tables, but not of these take inputs from the front-end, they merely update values from one table to another.

However, a friend of mine, who is much more knowledgeable in SQL matters brought to my attention the possibility of SQL injection. Obviously, that got me rather worried. I can't seem to find concrete evidence whether PowerApps will allow SQL injection. I assume it wouldn't, but proof would be nice. I have actually managed to save a query into one of the fields in my DB when submitting it through my app via Patch. It was a harmless query, but it did appear in it's entirety in the DB field. However, it didn't run. Is this something that could be ran after it was sent to the DB?

Basically, I just need my fears assuaging over whether Azure SQL or PowerApps will allow SQL injection or not!

Categories:

All responses (3)

Answers (1)

PaulD1 2,914 on at

Like (0)

Report

Re: SQL Injection

To make additional environments I believe you need at least one 'premium' license (it used to be that you could make one new environment per PowerApps Plan 2 license so I guess it is now per 'PowerApps Per User' license).

I don't have a reference for the conversion of PowerApps filters to Select statements, I just ran some traces in SQL so I could see what SQL statements were being generated when filters were run.
EpicTriffid 345 on at

Like (0)

Report

Re: SQL Injection

@PaulD1

Thank you for the response! I've been stressing over this for quite a while! Do you happen to have a resource where you learnt how PowerApps runs Select statements and the like? You're answer is fantastic, I'm just always eager to learn more!

As for the permissions based on the environment, I'm not sure I have that capability as we just have one license and as far as I'm aware of, one environment. Unless I am not understanding you correctly?
Verified answer

PaulD1 2,914 on at

Like (0)

Report

Re: SQL Injection

Never like to say definitively (not a security expert and who knows if there is some obscure bug that allows it), but as PowerApps does not let you directly specify SQL statements to run I don't see how a SQL Injection attack (embedding a SQL statement like ';Drop Database...' or ';Truncate Table...' as the text parameter within a SQL statement built in the client and sent to the SQL DB) would be possible.

PowerApps connects to SQL tables and views and will generate Select statements (translated from the Filter/Search formulas you specify) and Update/Insert/Delete statements (for tables only, views still not supported) translated from the Patch, Update, UpdateIf, Remove and RemoveIf formulas. Any special characters in text fields should be properly escaped or treated as parameters and you have no ability to override that.

Sure, you can save a SQL statement as a string to a 'text' field because it is just a string like any other. Unless your DB has a proc that will run dynamic SQL based on the text in a field in a table you should be fine.

More of a concern is if your App (and its SQL connection) exists in the Default environment.

IIRC All users have 'maker' rights in the default environment. If an App is shared with them, then so is the connector. As they have maker rights then can build a new App or Flow that uses the connector. If the connector uses SQL Authentication (rather than Azure Authentication) a user could create a new app, select the connector and start making whatever data changes the connector allows - so while the could not drop a table, they could delete/change the content of a table if the SQL Account used by the connector allows that.

The recommended approach is to have a separate environment for Dev/Test/Production and limit the number of people with Maker rights in those environments.