Prevent timestamp manipulation?

Dialect_Junk · 02-11-2020 05:23 AM

Is there a way to ensure that the timestamp is the actual time and not the device’s time?

We just discovered someone manipulating their timecard by manually setting the device time and then punching in to show they were on time. Sneaky…

I can go into the audit logs which timestamps the actual time of the operation to verify but this would get pretty cumbersome when trying to manage 35+ timecards.

The device’s belong to the employees and we install the app on their phone.

Thank you.

MultiTech

Sneaky sneaky. Hmmm I would suggest checking out UTCNOW() - though I think it’s not actually polling the time from some external source, instead only adding or subtracting based on your timezone.

@Phil might have a good idea what to do.

Dialect_Junk

Just tried this and you’re correct. Changed my column to UTCNOW() and I was still able to manipulate the timestamp via phone settings; does not poll an external source.

Steve

UTCNOW() is the device time without the timezone adjustment. It’s just as subject to tampering as NOW() is.

Steve

There is no way to poll an external source for time.

If you use a workflow to add a timestamp to a row, the timetamp will reflect the server’s time rather than that of the user’s device. (Workflows run from AppSheet servers, not on the user’s device.) The downside with workflows is that they run only when the server receives the data from the device when a sync occurs, so the timestamp added by a workflow really reflects when the data was synced, not when it was modified on the device. But this might still help you discover tampering. Or you could even say the clocking-in and -out must be done by syncing.

Dialect_Junk

This worked.

I created a new column [Actual Sync Time] and created the workflow to set this column with a timestamp. The [Date/Timestamp] column captures device time, [Actual Sync Time] shows when workflow was run.

I’m curious how others have handled this. Particularly with apps that may be subject to compliance or regulatory review where accuracy and validity of something like when a record was created would be critical - apps in the medical field, osha inspections, fleet inspections, etc.

Thanks, again, Steve.

Bahbus

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.

WillowMobileSys

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:

Do we have any way within the app to detect when the app is running in “offline” mode?
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?

Steve

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()}?

WillowMobileSys

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.

MultiTech

Yeah @Dialect_Junk you just exposed a major design flaw, one that I’m afraid I never really considered.

Even the solution of using a workflow to set a timestamp is kind of a Band-Aid.

What if I’m on the job and create all of my records, but I shut the app before they have a chance to sync… then the next morning when I open the app, it syncs all of those records - now my timestamps are all off.

No, we need a real solution to this; something that will appease any kind of regulatory investigators.

A solution I can think of is to have an internal clock inside the app, one that is separate from the local devices system clock and only get its updates from AppSheet’s servers. Then have some kind of function that we can use so we can call that time. SYSTEMTIME() or something.

Or, just completely switch over all of the time mechanisms to an internal clock that’s locked into the actual time of the world. This way any current formulas using NOW() or something like that will automatically start keeping track based on the real time.

@praveen

Steve

This only works for an always-online app.

Wouldn’t this require hardware support in the device?

MultiTech

I’m curious how you caught this?

Dialect_Junk

We noticed an employee’s timecard timestamp didn’t match the time he actually arrived to work. We knew he arrived at 8:30AM, yet the timestamp showed 7:45AM. We thought he must’ve clocked in on the way to work, but we checked the gps capture and it showed he clocked in at the job-site.

I checked the audit log and the log captured the actual UTC time that the sync was performed, not the timestamp. This verified his timestamp was wrong and that something was amiss.

We did some poking around, asked the right questions, and found out that last year several employees were changing their device’s time, and then clocking in. Turns out, it was happening very frequently. We thought we’d devised a fool-proof system with timestamps and gps capture, but, obviously, we were mistaken.

As you can imagine, it’s caused quite a stir around here. The amount of loss via theft of time is incalculable and nightmarish. Quite honestly, it has our leadership group asking whether, absent a tamper-proof solution, this is the right platform for us. I’m an advocate for the platform so I’ve fallen on the sword and accepted responsibility for not being aware of this. I’m keeping my fingers crossed that Steve’s “band-aid” is sufficient but I think it’s something I’ll have to monitor daily.

MultiTech

I can hear the war drums from my clients already…

I’ve also had an app of mine go through OSHA scrutiny, and they were satisfied with everything we had put in place as well. (Creation and Edit timestamps. gps, audit trail, etc.)

Nefarious players are at work everywhere, and while it may be impossible to stop them - at least we can build systems to catch them.

Dialect_Junk

I’ll also add that last year we had a reportable OSHA incident where we had to provide records of our fleet inspections. We were able to provide a table of this information and satisfy the OSHA inspectors. But, now, I’m not so sure that if our information was more closely examined, it would stand up to additional scrutiny. Especially if we were asked to verify and prove that inspection dates are accurate.

Steve

How was fraud/tampering prevented prior to apps?

Dialect_Junk

Old paper and pencil system:

Employees checked in and checked out with Supervisors daily.
Supervisors wrote down their hours.
Supervisors would have to get paper timecards back to the office by Friday 6pm.

Lots of delays in processing payroll and invoicing if paperwork was missing or turned in late. Wildly inefficient.

Hence the move to Appsheet.

MultiTech

Exactly how I found appsheet; I was working as an inventory manager at a construction company, and this is how they did payroll as well.

But I was privy to both sides of that equation - I was friends with all the workers, but I worked in the office (with the people who processed payroll) - so I was able to see the truth of what was really going on there.

Steve

I think a reasonable solution is to use the sync time as recorded by the server. You can even force the app to sync when the clock-in and -out is submitted. Just make sure staff knows that they must be online when they check-in and -out.

Dialect_Junk

This would be 50/50 for us. Many of our jobs are in remote areas where service is spotty or nonexistent. In these instances, employees have to wait until they get back to an area with service or back to the hotel before they can sync.

Dialect_Junk

I don’t know much about much, and this could be spectacularly dumb, but, I’m wondering if GPS may hold a solution. GPS doesn’t require internet connectivity and GPS timestamps are supposed to be accurate(?)

If the app is forced to allow location services, and the app can be forced to record GPS location upon startup, can the GPS’s timestamp be extracted from the GPS metadata at the time it was captured? This way it wouldn’t be reliant on device time or sync time.

Just an idea…

Jonathon

GPS can be spoofed just as easily as device time. With dozens of third party applications directly on a phone, or even directly in a chrome browser with chrome devtools:

Jonathon

One solution may be to have a terminal set up at the job sites where users punch in and out at (instead of using their personal devices), although this seems to forego some of the benefits you are trying to gain.

I have a hard time envisioning a foolproof system. Cheaters are going to cheat. You may be able to come up with some algorithm based upon the recorded data and server sync times, and other records being uploaded, which can help detect the cheaters. Or you may have to do spot checks at job sites.

Jonathan_S

This was an insane read.

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

1minManager

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

Dialect_Junk

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…

Grant_Stead

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

Dialect_Junk

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.

MultiTech

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!

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.

pravse

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

APiCC_Conor

Following this closely!

Yogesh_laddha

Hi,
Are there any updates on this?

Christian

Following…hopefully someone knows more.

WillowMobileSys

This thread morphed from “preventing timestamp manipulation” into retrieving GPS coords for addresses. Which issue are you wanting more information on?

dbaum

@pravse wrote:
Try to harden the UTCNOW() function to be tamper-proof.

@pravse, was this ever accomplished somehow?

Steve

No change, nor would I ever expect any.

pravse

No, we discussed how we might implement this back in 2020. But since then unfortunately, it has probably been lost in the shuffle along with the many TODO features.

comgen_technolo

are you going to do it? its been years. feeling like to abandon the platform which cant secure 1st rule of data security, whether be it user or someone. @lizlynch @Steve

Steve

AppSheet has no way to prevent the user from reconfiguring their own device.

What you want is literally impossible.