Let me give you some context regarding my problem.
I have developed a Power App that contains a gallery. This gallery has the following fields: Product Name, Product Description, and Product's Objective. The users can come and add a new row and fill in the values for these fields or can update what's existing. The data is stored in a SQL database.
Let's pick an example of updation. Let's say a user comes and wants to update the description and objective. They can type in the input fields and then click the submit button. Now, in the submit button, I am not using the Patch or UpdateIf functions but a Power Automate Flow to update the data in the SQL table. We have this 'Execute SQL Query (v2)' connector in Power Automate that I am using here (the trigger being Power Apps). My major concern here is how can I prevent SQL Injection attacks. The submit button OnClick script looks something like this:
Concurrent(
ClearCollect(
ModifiedRows,
Filter(
product_gallery.AllItems,
Value(gallery_is_modified.Text) = 1 // gallery_is_modified is a gallery element
)
),
Clear(UpdateStatements)
);
// concat all update statements
ForAll(
ModifiedRows As modified_rows,
With(
{
product_name_val: If(
modified_rows.gallery_product_name.Text in [Blank(), ""],
"NULL",
"'" & Substitute(modified_rows.gallery_product_name.Text, "'", "''") & "'"
),
product_description_val: If(
modified_rows.gallery_product_description.Text in [Blank(), ""],
"NULL",
"'" & Substitute(modified_rows.gallery_product_description.Text, "'", "''") & "'"
),
product_objective_val: If(
modified_rows.gallery_product_objective.Text in [Blank(), ""],
"NULL",
"'" & Substitute(modified_rows.gallery_product_objective.Text, "'", "''") & "'"
)
},
Collect(
UpdateStatements,
{
Text: "
UPDATE
ProductTable
SET
product_name = " & product_name_val & ",
product_description = " & product_description_val & ",
project_lead_product_objectives = " & product_objective_val & "
WHERE
id = " & modified_rows.id & ";
"
}
)
)
);
If(
CountRows(ModifiedRows) > 0,
ExecuteSQLQuery.Run(
"
BEGIN TRANSACTION
" &
Concat(
UpdateStatements,
Text & "
"
) &
"
COMMIT TRANSACTION
"
)
);
// ... rest of the script
The gist is:
- Fetch all the modified rows from the gallery (product_gallery).
- Traverse, create, and collect the required UPDATE statements.
- Run all the UPDATE statements in one go (I am using this approach because I have found that this approach executes all the queries faster than using the Patch or UpdateIf functions for each modified row).
As you can see in the code, I am already escaping the single quotes for strings. But that's not enough to prevent SQL Injections. I read that we can use parameterization to prevent SQL Injections. However, I am not able to find the correct approach to implement the same in Power Apps. Can someone nudge me in the right direction here?
