Appsheet URL has version identifier that general u...

WillVerkaik · 04-27-2023 07:38 AM

In working with my Tech Service Desk team, we have uncovered a concern and want to know if there will be a fix for this. It was discovered that some saved links were not working and the version of the application was older. Further investigation, it was seen that they were saving a URL that had version descriptions/identifiers.

By removing this, the application synched with the most recent version. Problem solved.

However, when taking this parameter of versions#, and adding it, the user was now able to move through each versions without any problems . In some cases ( and I can now understand why it is happening), a particular saved version by the user, has some fields pointing to a different column, causing bad data to be saved.

I don't know how to emphasis this any further, but the Application use can NOT be used in the state.

This is a serious compromise that my CISO will not let us continue and I am looking for help to better understand what is being done to correct this ASAP.

WillowMobileSys

I'm a little lost! I have accessed several of my apps and do not see any version details in any of the Desktop URL's being shown.

Can you provide some additional details of HOW the URL's are being saved and where in that link the version info is being included?

I am also a little curious WHY users are saving the URL's. I can't think of a reason why they need to do that.

WillVerkaik

Users can share deep links to specific content to other users and often do. As a result they may often save the link for future reference.

WillVerkaik

&version=#.###### add this to your url. This is a common security hack that was recognized as a significant problem from 2000 to 2010 and finally best practices where to test for this security issue.

In my instance, the users did not mean to save the link with the Version. It happened somehow and we had to figure out why updates where not going to them.

My comment here is this is a severe security breach as a tool unfortunately that needs to be fixed.

WillowMobileSys

Understood. At the very least you should submit your findings to AppSheet Support.

However, I am trying to inject older version numbers into an app URL and cannot seem to force the app to the specified version for myself or any virtual user I have established. The user has, so far, been directed to the proper version.

Maybe you have found a gap? But then we will need more specific details on how you and your users are accomplishing access to the old app version.

Marc_Dillon

Yah I tried as well, nothing seems to happen.

Will, are you sure the users aren't just loading from the cache?

WillVerkaik

I will go back and double check .

However, we were able to demonstrate different features showing up. Mostly it is noticeable when the column numbers change and the error will message out inconsistent columns which breaks the app entirely.

When looking at error message in the logs it shows the different version numbers. We have cleared the cache with no effect. They can increment through the versions to see different versions that I had never launch/released for them to use. ( ie taking advantage of the 'Stable' version release)

Currently I have about 10 different apps, used by about 30 different users. Some apps used daily by about 18 users. I have test 3 of these, and all produce the same results ( I being the administrator). Only 1 the most used was tested by none administrators, just general users.

I have however seen a number of log errors recorded with different users, with different version numbers. These are likely saved links with the version in the URL.

WillowMobileSys

@WillVerkaik wrote:

( ie taking advantage of the 'Stable' version release)

Obviously, this is an Enterprise level account. I'll try the version injection on similar apps I have access to. I have a vested interest for my clients.

WillVerkaik

Yes it is an Enterprise account.

WillowMobileSys

@Marc_Dillon wrote:

Will, are you sure the users aren't just loading from the cache?

This is a good point!

Something I have only recently realized is that there is a difference between a Sync and data updates. A user must manually tap Sync to get version updates.

WillVerkaik

Double checked with my team. We were able to go back to various version to a point where I implemented a security solution to avoid access to specific data. ie. Use security filtering and user record data. As a result, this user had the current version where they could not see the data, then with the older version before this, they could then open and see ALL data that they should not be able to see. I am trying to work with Appsheet Support on this.

WillVerkaik

Also, opened a completely different app by the user, that was not flagged as a problem. Added the session ID and the ability to access old functions ( or loss there of)

WillVerkaik

Some outcomes just modifying. 2 instance the app works, 1 breaks ( I will look to see how to submit to AppSheet Support)

WillowMobileSys

I spent some time testing this on an Enterprise app for which I am a co-author. I I cannot reproduce the issue. I've tried with a couple different accounts with different app access - Edit versus User. Attempts to inject an old version always bring me back to the correct expected version - whether I start with the Editor browser link or copy a deep link within the app. See images below for only some of the attempts I've made.

I believe you have found an issue. We just haven't found the root cause - an AppSheet issue or something else.

********************************************

Injection attempt on Co-Author set to Latest

Injection for App Start on User set to Stabile version

Injection into Deep Link

Injection attempt for user without access

WillVerkaik

When addition the version, use Ampersand '&' to parametrize the value pair of version/number. So use &version=#.######

SkrOYC

It''s the same.
Could you share some screenshots similar to the ones @WillowMobileSys sent? This will help the AppSheet Team troubleshoot the problem

WillVerkaik

I think 2 of the 3 screenshots @WillowMobileSys did not use the Ampersand. The images I shared above have the URL. If you wish for more screen captures I can provide. I have sat with 3 staff members in office to go over their computer and was able reproduce the problem. It might be related to our environment, but it is multiple different Apps different data sources.

WillVerkaik

This is a standard user (MAC in this instance). First capture is the current version with a single data element related to the user. The second screenshot is moving back the pre-dates the release of adding security feature to allow the user to only see their department.

SkrOYC

Can you test this under an incognito/private browser window?

WillowMobileSys

So 2 things:

1) You are using the Desktop Preview. Be aware it is still in Preview/Beta and while in a very good state right now, it is riddled with issues. You really don't want to use it for LIVE apps - especially if you have security concerns.

I will re-test with Desktop Preview ON to see if that changes anything for me.

2) I am curious, if you were to open the About view, what version does it show? I have only been inspecting the version number and assuming the logic matches the version shown. But there could be a mis-match in the version reflected and the physical code loaded.

WillVerkaik

To be honest, without the Desktop version, the Appsheets solution is useless to our business. Most work is NOT using mobile devices from users. I have a budget for 2,000 users to either continue with Google or move to MS of which provides PowerApps. The technology is quite good for its simple purpose and to release companywide for interfacing with Sheets. ( and Bigquery). Without the desktop however, that is a showstopper.

WillVerkaik

The version in the URL matches the version in the About.

SkrOYC

Adding @devingu just in case as this seems a security risk

WillowMobileSys

@WillVerkaik wrote:

So use &version=#.######

It depends on location of the parameter. The first parameter is preceded by "#" which is why you see it that way in some of my tests.

SkrOYC

You sent us just a screenshot showing a common issue when adding more columns to the datasource. Could you do the same comparison as John did with the URL's version and App's versions shown on the same screenshot?
Just in case, the app version that is currently running can be found inside the "about" section inside the app's menu

This is an example using the AppSheet Gallery:

SkrOYC

OK.

I was finally able to repro this issue.
Please report to support.
Mention this post (adding the URL) when asking help from support.

Current version:

Forcing 1.000288:

Forcing 1.000280:

Forcing 1.000275:

Going older than that triggers an error, similar to the first screenshot you shared.

I cannot provide too much details right now as how this could affect our security, but for sure you can use an older version.
It's also unclear when this works and when it doesn't (which apps are affected vs which ones aren't)

@devingu @Arthur_Rallu

WillVerkaik

I reported to support yesterday, and they said they escalated and I will be emailed. Thank you @SkrOYC for taking the time to try and reproduce the error. From my perspective, the app cannot be used for anything that has confidentiality or PII (which is most).

SkrOYC

@WillowMobileSys wrote:

I will re-test with Desktop Preview ON to see if that changes anything for me.

I tried with Desktop On and Off and it was the same.

Also, even after clicking the Sync button (from the older version typed into the URL), AppSheet didn't deliver the latest version, it was stuck in the older one. I expected that a simple sync would take you to the most up to date.
This could even be related to some people reporting issues where the most "stable" version was delayed when making a stable vs development one

Marc_Dillon

I was also able to repro it now. It seems the version must be part of the url parameters (the part after the '?'), not part of the fragment (part after '#').

A non-desktop-preview app seems to not come with any '?' url parameters by default, just a fragment. But a desktop-preview app does come with a url parameter, specifically the '?platform=desktop'. But I still don't see any version anywhere, not sure how that first came about, did someone just take a random guess at adding a 'version' parameter in?

WillVerkaik

When you go back and look at older version, the URL is populated with it. I may have inadvertently sent this to someone and it then spun off. However, the fact that someone can just add a version is a problem regardless.

WillowMobileSys

@WillVerkaik wrote:

However, the fact that someone can just add a version is a problem regardless.

I agree that this is problematic. But it isn't a major security concern. It can be a data headache for sure!! Let me explain.

The same "old version" problem can happen organically. If a user uses the app daily but never Sync's to get the updated app version's, they can continue to operate on that version indefinitely OR until the app breaks from something like a mis-match in columns.

I recently experienced the pleasure of this issue!! Not fun when a user has 50 edits they have submitted and you are trying to recover them.

In either of these scenarios, if the user is de-listed or loses authorization, they will not be able to get into the app - old or new.

Anyway, for now it is what it is!

WillVerkaik

Really? it is what it is? I sure hope the team at Appsheet / Google do not feel this way and deal with it at the level of security compromise that it is. Exposing application history to any user engaging in a solution is an untenable argument. At the very least, versioning should have the ability to be shut to exposure.

WillowMobileSys

@WillVerkaik wrote:

Really? it is what it is?

Ah c'mon! Don't get so uptight. We are all trying to resolve these things together. I simply meant it is the way it is...for now. BUT, it's not the security problem you think it is. As I described it can happen naturally through user "normal" use of the app. If a user becomes a problem, you have the ability to remove their access.

@WillVerkaik wrote:

At the very least, versioning should have the ability to be shut to exposure.

This part I, and I'm sure ALL of us, agree with 1000%. In fact there is a Feature Idea yet to be implemented that will solve this problem. Please review it (link below) and upvote it. I would recommend adding into the comments what you have informed us of in this thread to give more clout to the urgency of the requested feature.

https://www.googlecloudcommunity.com/gc/Feature-Ideas/We-need-a-way-to-force-new-versions-of-an-app-...

WillVerkaik

Interesting: The link to this 'feature' request indicates that it remains 4 years yet to be acknowledged and IMO should be a core infrastructure delivery function. Somewhat disappointing if the technology is expecting to be used as an enterprise solution. My additional upvote to move from 22 to 23 would be inconsequential to any decision on the ROI of this 'feature' when hitting the product roadmap table. I was unfortunately under the impression the size of the tech community was larger being a Google solution. My evaluation seems to be leading down a dirt road.

WillowMobileSys

@Marc_Dillon wrote:

I was also able to repro it now. It seems the version must be part of the url parameters (the part after the '?'), not part of the fragment (part after '#').

Ok, after this, I was able to research a little and now I too can reproduce it.

I'd forgotten how some of this is used and didn't know what the "#" was for at all. For everyone's understanding, here is what each of the "parameter separators" do:

? - separates the URL from the Query parameters. It signals to the server that parameters are to follow.
& - delimiter between Query parameter Key/Value pairs
# - client side anchor - the portion after the # is typically processed on the client side to specify WHERE in the client side code (returned after parsing the Query parameters) the web page should start at. These parameters are usually ignored by the server.

So...when I tried copying the Browser link and insert "&version=x.xxxxxx" it didn't work - no app presented. Following examples, I next tried "#version-x.xxxxxx" and the app launched but I always got the proper latest version. That's because the # stuff was ignored and essentially treating the URL link the normal Browser Link.

With the above understanding, I can now add "?version=x.xxxxxx" and the server returns the specified version, the app loads or results in an error if there is a column mismatch.

To put all this together and understand how all of these work together, just look at the Editor URL it uses all three of these. See image below

SkrOYC

@Marc_Dillon wrote:

It seems the version must be part of the url parameters (the part after the '?'), not part of the fragment (part after '#').

Exactly

SkrOYC

If you go to an older version, you are presented with this (Example using desktop):

Clearly sending links with a version parameter in it is absolutely not recommended, but AppSheet should have ways to prevent this kind of behaviour

WillowMobileSys

@WillVerkaik wrote:

Interesting: The link to this 'feature' request indicates that it remains 4 years yet to be acknowledged and IMO should be a core infrastructure delivery function.

I know and I agree. Its way too long for a feature that is sorely needed. The low votes, I think, are just an indication that most apps simply don't have this problem or organizations handle it in a way that it hasn't caused them issues - yet!

I have been trying to come up with potential significant problems to "sell" the need for forcing users to newer versions. There is of course the error situations we all want to avoid.

But the worst case scenario I can think of is if an app is changed to "lock" users out of certain data areas. You no longer want anyone to have the capability to access an old version which would allow them access to data they are no longer to see. But by then any potential "damage" is likely already done. This is an app design issue not a platform security breach. Still, it is something that requires the need to force users to latest versions.

Any other serious situations you are aware of?

SkrOYC

My extra 2 cents on this:

We have always been told that the ONLY way to secure our apps is by having a curated list of users and using Security Filters.

Don't expect that using Show_if for views or columns will do anything in the sense of security.
If security is your concern, test if your Security Filters are being respected in a version older than the one where a certain new Security Filter is implemented. If it's not, I'd report this as a major bug and security risk. If it is, this is not the big problem you think it is, apart from maybe not understanding some of the ways this platform deals with security and users accessing it's data

WillVerkaik

The topic of security is long in the tooth for all technologie long before my time and likely not worth the debate here. In the end, if the use of adding version as an open paramater is deemed a feature, then the expectation is clear and the technology is relegated to the confidence of leaving content potentialy exposed.

Development processes are about continual 'experimentation'. Most of your work is deemed unusable until you find your 'gold nuget' and want to commit. If your business/clients is comfortable when aware of this with their content, then not a risk.

The alternative is to follow the best practice of separate SDLC environments for DevOPS = Dev->Integration->Test/Staging->PreDeploy->Production. Of course with a cleaned Production.

That however defeats the gains of Citizen Development just to minimize this risk.

I like the technology. It makes sense. It can do wonders on productivty of large teams on spreadsheet hell. However now as a Google Product, it should be expected to have minimum standards for mass adoption.

Appsheet URL has version identifier that general users can manipulate

OK.