Patch() Error for Primary Key of BIN

[Tgunderson]

Hello, I think I found a specific scenario that is causing a bug when using the Patch() function.

My App/Database setup is as follows:

• I have a SQL Server database running in an Azure microservice environment.
• I have a table in the database with a Primary Key defined as nvarchar(3).
• The data values in the PK are all 3 alphabetical characters (e.g. ABC, DDP, MOO, etc.)
• I have a PowerApps screen that creates a collection of values (1 or more rows) as user edits rows within a gallery.
• When the user clicks a <Save> button, the Patch() function is called to update the rows in the database from the collection of ‘edits’

This works and has worked flawlessly for ~1 year+, EXCEPT for one scenario that just occurred.  If the data value of the primary key for a row is “BIN”, the App will present the following error message and NOT perform the update:

The specified record was not found. Server Response: tblBatchManagement failed: The response is not in a JSON format.

To troubleshoot the issue, I did the following:

• Manually update the row with PK of BIN using Edit in SSMS.  No problem.
• Run an SQL statement in SSMS to SET values on the row with PK of BIN.  No problem.
• Created a brand new test database and test App.  Same issue if the PK is BIN.  

After pondering and being thoroughly confused how a data value for a column could be causing an error, I sought assistance within my company and got some great help and ideas to pursue.  After a co-worker created his own test as well, with the same results, it appears that the issue truly is that the data value of the Primary Key is BIN.

I have pasted example of successful and failed updates below.

1. Can anyone confirm this is a known issue?
2. Is it a security risk?
3. Are there workarounds?  (Besides using an Identity column as a PK and avoid the issue that way)
4. If this is an unknown issue thus far, how does one get word to MS?

Any thoughts GREATLY appreciated.  Thanks!

Tom

Monitor Information

Using the Monitor feature of the PowerApps App, here is what we see:

Successful Update

The green rows above represent the collection of one row with a PK of BHD, and the subsequent successful update.  Details in the monitor of the patchRow operation for this are below.

• The URL includes the server name, database, table, and the primary key for the row I want to update.  I have grayed that out for here.
• The last part of the URL is highlighted as that displays the PK of the row.
• The method PATCH is being used
• The data indicates shared_sql.PatchRow is being called.
• Content-Type is “application/json”

And the response section shows a LOT more detail, but pertinent part is here:

FAILED Update

The yellow rows above represent the collection of one row with a PK of BIN, and the subsequent FAILED update.  

The request details in the monitor of the patchRow operation for this are below, and the same as the successful update above.

And the response section for this one shows the error information:

[Tgunderson]

Someone HAS to be curious about this?  MS person monitoring the forum?  I assume a quick resolution is not possible, but some further confirmation this is a bug would be good!  Thanks!

[Tgunderson · Accepted solution]

The issue was submitted to MS Support, which provided the following reply:

After conducting a thorough investigation, our Product Group team has determined that the issue is not related to the SQL Server Database or SQL Server connector. Rather, it is caused by the default configuration of the Web IIS. Specifically, the <hiddenSegments> element contains a collection of <add> elements that restrict certain URLs from being accessible to clients.

For example, on Web servers that host ASP.NET content, IIS 7 blocks several of the ASP.NET-related paths for security reasons. This includes paths such as Web.config, bin, App_Code, etc. Blocking these URL segments reduces the risk of an attacker exploiting these URLs for information. This behavior is further detailed in the documentation found here: Hidden Segments <hiddenSegments> | Microsoft Learn

As the issue is related to security concerns, the Product Group team will not be making changes to this configuration in the near future. However, as a workaround, we suggest adding an additional character to the code or explicitly avoiding the word "BIN".