Prevent timestamp manipulation?

This was an insane read.

Good to know for any future apps I make that need accurate time stamps.

2 Likes

Hi guys! Coming into this late (have been focused on client acquisition) but I thought I’d give you my knowledge on how this is handled in the industry. For those who don’t know me, I have over 25 years exp in corporate software dev.

How this is handled in the Industry

This problem is ever present in any software system. It’s just the way software works. When an app asks for date and time, it has no choice but to get it from the operating system of the device it is making the request of.

When IMPORTANT timestamps are needed, the way this is handled in the industry is exactly the way @Steve suggested, by retrieving the Date/Time from the server. It (or they) is the one constant in the system and ensures everyone is getting that info in a consistent manner. If its needed on the client WITHOUT a save, then a client to server call is made to retrieve it. As a side note: there is always potential the Date/Time could be changed on the server - either by mistake or by some unscrupulous activity. Unlikely, but possible.

Another thing corps do nowadays is lock down the desktop/laptop machines so that users cannot change the date time and it is usually synced with a server. In some circumstances, that is not feasible because QA testers MUST be able to change Date/Time to test software functions that rely on it. Obviously, this is not feasible for our purposes as AppSheet developers.

Related Questions

I do have a related question, based on this comment from @Steve:

Under “normal” circumstances, our edits are synced to servers right away, within seconds, of a save. As @MultiTech_Visions, points out this is perfectly acceptable for most regulating organizations. (If its not acceptable then you may be doing work that is HIGHLY scrutinized and maybe AppSheet is not the right tool for the job!)

As I am sure was implied by @Steve 's comment, where problems can occur is when connection is lost. Assuming that capturing an accurate timestamp is critical, one way to prevent the “bad data” due to the delay, is to NOT allow the action if there is no connection.

So… some questions:

  1. Do we have any way within the app to detect when the app is running in “offline” mode?
  2. Is it feasible for our apps to make a client/server call to get Date/Time from a server? I.e. are there any other such calls being made in that way?
5 Likes

To my knowledge, no, not directly.

No.

What could you do with an app table backed by a spreadsheet with two columns (ID & Server Sync) and one row, {1, =NOW()}?

1 Like

That would get you Google’s server timestamp, wouldn’t it? That may be okay, if used consistently throughout the app. Also, to be general, there’s the database side of things. I think you can do something similar using Views.

In any case, the timestamp would only be as good as the last sync. I’ve read about but never tried, forcing a sync automatically. If that is possible that would help. But then I’m back to “what if connectivity is lost?”.

Another Thought

In the end, the data source (Google sheet or database) is the system of record. And you have to go through the servers to get the data there. So simply capturing the timestamp through a Workflow is probably 90% of the battle.

For those situations where the save is delayed, maybe it’s handled as a matter of business process with a request from the affected user and Admin overrides.

So following on from the above posts, the only definitive way to get around this is use a locked down company tablet or smartphone where the user doesn’t have the permissions to change the time, put it in air plane mode or similar.

We’ve done a job where we did this for a company that was loosing £50k PA. The staff were recording on their time sheets (and getting paid for) the total hours they did. Yet they where contracted for much less. We used a reception tablet and QR Codes for each employee. Now we all know how easy it would be to copy, or even photograph a QR Code, but this leads me onto my next point…

You need to take into account the human element here. So firstly, the users only have the vaguest notion of how these Apps work. So in theory changing the phone time will work. But you can catch them out by a manual process as above. But also by some simple data checks like looking for time entries recorded out of sequence e.g.

0900
0846
0903

There a dozens of other options. But admittedly, none can prevent someone stealing who knows how the App works and its limitations. Which leads me onto point number 2. Once you, as the App dev, have detected some anomalies, you need to give the management your findings. Its not your fault they are stealing for the company. Nor it is Appsheets fault for not being “…tamper-proof…” in terms of proof - no IT system is completely infallible. For me this is now a HR or management issue. You need to amass as much evidence of tampering as you can get and then they need to have a meeting with him RE the findings. If it were me, I’d give the guy 2 options. Either he works for free until the money is repaid, or he is dismissed on the spot. Ditto for all the others that you have found evidence for.

People will try to steal from a company if both these two things are true:
1 = They think they can get away with it, i.e. it won’t be detected
2 = Management won’t do anything about it

So not only does this guy need hauling in and ideally dismissing on the spot. The management needs to tell the WHOLE COMPANY what happened and the reasons he’s gone. Trust me, you do that, it won’t happen again.

Wanna know what the company loosing £50k did with the time sheet system I created for them - NOTHING!. The manager was so concerned she might upset some of the employees by only paying them for the shift they we asked to work that she found various reasons to not use the App. She even went as far as claiming that she’d entered in all the shift patterns and it wasn’t letting people clock in. In truth I could see from the App she’d not even created a single row of data since I did a few test items after installing it on her phone. Finally the business owner didn’t want to upset the manager by forcing her to do her job!

As a final example we did a big time sheet job for a construction company who had about 50 employees all working on difference sites all over the country. So the guys could choose their start and end times. But we secretly did a Change_Timestamp and Change_Location on both the start time and end time. Then we used a workflow to convert the job address via Google Maps API to a Lat Long so we could measure the distance between them and the building site. Before we even started I checked with a number of the employees who said that fiddling time sheets was “…industry practice…” and they all said that everyone was adding at least 30min each day to their time sheet. So in the first App version we found that they’d just do all their time entries at the end of the week. So we added a rule that said you have to create today’s time entry today. So now their doing their time entry when they get home or in McDonalds. So we add another rule that says you have to be within 200m of the building site to clock in or out. The complaints started. “This app is crap and doesn’t work”. “I can’t clock in…” etc etc. Turns out they have turned the GPS off to try and bypass the location rule. So I added a warning if the Change_Location column is either blank or 0,0 - “GPS not detect, go outside and try again”. Now to break the App they are trying new stuff like clocking in but not out, or vice versa. Or having the end time before the start time. I add more rules to highlight these issues. This app also calculated the wages, which was important because some people are paid a day rate, others hourly and some on ‘price’. Which meant once a particular bit of the job was marked as complete they got paid a set price for that bit. Whilst all the above is going on, some people are still submitting their time sheets on paper to the wages woman. Its taking her nearly 2 days per week to collate all the data, call people regarding missing info and chasing up late time sheets. So I convince the boss to force people to use the App by saying that in 2 weeks they will only accept time sheets via the App. He arranges a meeting with all the guys and I spend about 2 hours confirming its working on everyone’s and getting them all to submit one test time entry. I create a whatapps group with me and all the 50 guys on in case of any issues. They are told to contact me driectly with any issues. 1 week later he calls me in a panic. He’s getting dozens of calls per day from guys who can’t use the App. Some are threatening to quit. New guys are being told to go back to their old firms as “…this app means you won’t get paid”. I call most of these guys and even visit a few on nearby sites. There is nothing wrong with the App. A couple more weeks pass. I can see that each day more and more people are using the App. The boss calls again, same issue. People claiming they can’t use the App. I tell him to hang on as 60% are now using this App daily and I predict that in another week it will be 80-90%. But he chickens out. Drops the App completely even though he knew the App was working fine. It was all purely the staff putting pressure on him to drop the App so they could continue to pick his pockets.

So whilst you can do all you can in terms of App development. The human issue is more powerful and can only truly be handled as HR issue.

Happy to chat on a person level with anyone if this helps

Simon@1minManager.com

6 Likes

Your experience is hilariously similar to my own.

We rolled out our timecard app last April in a meeting. I tested the app on everyone’s device and confirmed it was working. I explained and underscored, "IF YOU HAVE ANY ISSUES WITH THE APP - ANY ISSUES AT ALL - YOU MUST CALL ME RIGHT AWAY! Friday rolls around, and I get a frantic call from payroll department saying half of the timecards are missing, and the other half are incomplete. I start the call-around to find out what’s happening.

“I couldn’t clock in,” “I tried to sync but it didn’t let me,” “The app didn’t start,” This app s#cks…"

We quickly realized we were fighting human nature; by implementing the app, we were, in their eyes, “taking money” out of their pockets. We knew if we didn’t do something, we’d have a small scale mutiny on our hands.

Fortunately, the company was dedicated to making this work. We decided to get creative and made the use and accuracy of the timecard app one of the criteria in the employee bonus program. And, we also implemented a policy stating that we would only pay for the hours documented on the timecard app and that we would not chase down individual timecards anymore; if they were short a couple of hours, they’d have to wait until the following week to get paid. These changes quickly got us to 100% use rate.

We were pretty pleased with ourselves. High fives, pats on the back, sipping champagne. That is, until last week when we found out that timestamp manipulation was fairly rampant. Turns out, they had the last laugh, and we’ll never know how much it cost us.

My takeaway from this experience: fully understand and educate yourself on the limitations of the apps you’re building and compare that to what you absolutely need. While we can still use the timecard app (with some oversight/mgmt.), we’re not so sure about some of the other “compliance” apps we’ve developed or are in the pipeline (DOT pre-trip inspections, OSHA job site safety audits, accident reports, near-miss reports, etc.) At least, not without some additional checks and balances. They’re still a work in progress…

3 Likes

This would have been my way forward. Make sure the server time is with like 10 minutes of the user time. If so, the user time is good is valid.

1 Like

We’re going to do something about this. Try to harden the UTCNOW() function to be tamper-proof. Please stay tuned … @Thierry @tony FYI

12 Likes

A few thoughts on this… For what they’re worth…
My first point of attack is to stick with your original method with supervisors clocking people in… That’s what we do. They can just do it slick and easy now, especially when each employee has a badge…
Second, roll with a kiosk mode tablet at a gate…
If those options aren’t possible then you already wouldn’t have any better controls over paper… You’re paper would be just as potentially flubbed.

Another point to keep in mind… DOT uses electronic driver logs, and the software must be registered with the DOT, self certified, in order to be utilized… So you might want to verify that the things you need for DOT don’t fall under that. They have an entire specification about the software itself. https://www.fmcsa.dot.gov/hours-service/elds/electronic-logging-devices

3 Likes

Thanks for input, Grant. We’ve considered both options 1 and 2 but we have too many variables to reliably implement either. And, you’re right - paper option was just as flubbed. Worse, actually.

About e-logs: fortunately for us, we fall outside of e-log requirements. We have a compliance group that ensures we’re good here. We’re primarily concerned with pre-trip inspections and OSHA.

Thanks, again.

1 Like

I was just going to chime in on this.

Before I started being an AppSheet consultant, I worked for a construction company as their inventory manager - and eventually all the DOT stuff was dropped in my lap. Worse, we had a DOT audit happening in 2 months time. Yay! :grimacing:

At the time of the audit I was a couple of months into learning AppSheet, and I immediately saw the potential to build a trucking DOT inspection app - so I asked the auditor when she was there about it.

Exactly what Grant was saying, the only trucking logs that will be accepted must be from an approved software source - and one of the requirements (here in Iowa at least) is that the software talk to the engine’s computer (to get information directly from it). Something that is NOT possible with an AppSheet app.


I’ve seen people building trucking inspection apps and have voiced this concern there, as I do now.

3 Likes

@Praveen sorry to add even more to your workload. But if you are looking at the timestamp to make it more secure, can you do the same with the GPS location? One issue we noticed about 12months ago was that if you are on wireless, sometimes Appsheet uses the internet connection location, presumably via the DNS record. Is there any change you can force Appsheet to only use the GPS receiver?

How we worked this out was with the above company sometimes they couldn’t clock in. I went to site and ran some tests. Google Maps was reporting my location correctly but Appsheet was getting a location about a mile away. When I turned off wireless on my phone and Appsheet then got the correct location.

Simon@1minManager.com

1 Like

@1minManager you’ve seen this right?

1 Like

Following this closely!

@MultiTech_Visions yes. But its not clear how it works. We can have 40-50 people doing multiple time entries per day, and each one has a Change_Location on both the start time and end time. So the limit of 1000 is potentially not enough. Also its not clear why this limitation is in place if its just pulling a Lat Long from the GPS receiver.

1 Like