Editing Database Files Directly - Validation

Posted by ujj1 on 04-Apr-2019 17:30

One of our developers asked what happens if someone uses notepad or notepad++ or some other text editing tool to edit data in the database extents directly. 

We tried it on a throw away database and as expected we saw errors like wrong dbkey and other system errors that would shut down the db server.  Are there cases were editing the DB files using a text editor or hex editor would not result in block corruption?  I'm talking about simple edits where that a novice user would attempt and not a tech savvy user/progress database engineer. 

Posted by George Potemkin on 04-Apr-2019 20:47

Blocks in type 2 storage areas are protected by CRCs: changing just a bit inside a database block will be detected .

Posted by Rob Fitzpatrick on 04-Apr-2019 17:58

> One of our developers asked what happens if someone uses notepad or notepad++ or some other text editing tool to edit data in the database extents directly.

What happens, in all likelihood, is that they corrupt the database beyond repair, in ways that you may or may not notice until it's too late.  I'd be wondering about the motivations of the developer, or whoever else might be thinking along these lines.  

> I'm talking about simple edits where that a novice user would attempt and not a tech savvy user/progress database engineer.

If you want to do simple edits to data, write a simple ABL procedure and run it.  "Novice users" who think otherwise should be re-educated or have their system access removed.

All Replies

Posted by bronco on 04-Apr-2019 17:57

I would say: don’t even think about it!

Posted by Rob Fitzpatrick on 04-Apr-2019 17:58

> One of our developers asked what happens if someone uses notepad or notepad++ or some other text editing tool to edit data in the database extents directly.

What happens, in all likelihood, is that they corrupt the database beyond repair, in ways that you may or may not notice until it's too late.  I'd be wondering about the motivations of the developer, or whoever else might be thinking along these lines.  

> I'm talking about simple edits where that a novice user would attempt and not a tech savvy user/progress database engineer.

If you want to do simple edits to data, write a simple ABL procedure and run it.  "Novice users" who think otherwise should be re-educated or have their system access removed.

Posted by gus bjorklund on 04-Apr-2019 18:36

> On Apr 4, 2019, at 1:32 PM, ujj1 wrote:

>

> One of our developers asked what happens if someone uses notepad or notepad++ or some other text editing tool to edit data in the database extents directly.

>

>

what happens is that you /will/ have to restore your most recent backup. there are /no/ scenarios in which this is not true.

your users should not have read/write privs on database files except in the case where they created their own, private databases. if that is the case, you should not care about the results — they can smash them as much as they want.

Posted by jmls on 04-Apr-2019 18:51

back in the day .. the days of OS/2 .. there was a scenario where a multi-extent database became corrupted if the variable extent exceeded 2MB (I think) . Of course, backup strategy was not tested and it didn't work. So I was tasked to go to the parent company that had the problem and "fix it". Turns out that the some blocks were off by 1 or something like that. Fired up the old hex editor, changed them back to what they should be and it fired up.

I wouldn't do that these days! Mind you, I was soldering wires to a z80 cpu (putting a soft reset switch onto my cpc6128)  just a couple of years earlier :)

Was _very_ annoyed several months later when the same thing happened ...

Posted by gus bjorklund on 04-Apr-2019 19:06

since you brought it up, not too long ago, i had a customer with a version 8 database on windows NT 4 on ancient hardware. the database ran out of space and among other things, they tried to fix it by copying in various old versions of several files. it was a big mess. i worked on it for 33 hours and managed to do a bunch of binary edits to the files, finally getting it back in order. there were no backups because the dba had meaning to figure out why the nightly backup scheduled task failed every day for the past year.

such repairs are not cheap.

Posted by jmls on 04-Apr-2019 19:10

well, crap. I was "loaned" at my normal day rate. aka salary. :)

Posted by ujj1 on 04-Apr-2019 20:31

ok, I know no one should be doing this.  What I'm trying to get at is whether the DB Engine will always pretty much get corrupted.  The example in point here was say we have some audit data recorded in a record.  A user somehow gets access to the file system and happens to get to one of the database extents.  Could a simple edit go undetected and not cause corruption?  For example if they changed a record that had their name or user identifier to some other user.  When we tried it in house, it always caused some corruption. I didn't think progress had record level integrity checks or some sort of crc at the data level so I was curious as to what got corrupted.  I'm thinking when we edited something that looked like data we actually touched other bits of data because we didn't use a hex editor. 

Posted by kirchner on 04-Apr-2019 20:37

I've done similar edits successfully in the past.

As long as you change only the correct bytes and do not stamp over anything else and do not change the file size (implied, but important) and know what you are doing it should work. Needless to say it's totally not supported nor recommended.

It's important you do the edit in a proper way, say an hex editor which won't scramble everything. Opening a DB file in a text editor and saving the file will pretty much blow it up.

Posted by George Potemkin on 04-Apr-2019 20:47

Blocks in type 2 storage areas are protected by CRCs: changing just a bit inside a database block will be detected .

Posted by gus bjorklund on 05-Apr-2019 15:06

> On Apr 4, 2019, at 4:32 PM, ujj1 wrote:

>

> Could a simple edit go undetected and not cause corruption

yes, if performed by a sufficiently knowledgeable person. most likely a simple edit WILL cause corruption but it may go undetected for a very long time, such as until year-end closing or longer.

Posted by kirchner on 05-Apr-2019 15:11

Back then it was type 1 storage area.

I did not knew (or maybe did not remember) about this block CRC so I setup a small test. I changed the block with an hex editor and could still access the database and read and change data.

Then I tried dbtool on the changed database and it came back "clean".

Only when I run dbrpr with option "16. Scan/Fix block checksum (Type II Area)" the mismatched checksum was flagged.

Is this expected or should I have seen errors elsewhere? I tried on OE 11.7.4.

Posted by George Potemkin on 05-Apr-2019 15:32
BROKER  0: (14410) Checksum validation of dbkey 170957088 block type 3 in area 7 does not match data. Expected: -9050 received 28434.

The message will be written to a db log only - %L:

%LChecksum validation of dbkey %j block type %i in area %i does not match data. Expected: %i received %i. (14410)
Posted by Richard Banville on 05-Apr-2019 16:25

One could figure out how to update the CRC easily enough.  I mean if folks are in there binary editing blocks of the database then why not?

Couldn't help myself from responding;)

Posted by George Potemkin on 05-Apr-2019 16:38

Adding a sewing kit to a knife and is still not enough to become a surgeon. ;-)

Posted by James Palmer on 05-Apr-2019 16:38

Reading between the lines of the OP's question, they are seemingly trying to ascertain how easy it would be for someone to hack audit data on the database to make it seem like they are not culpable for something. This sounds like a perfect use case for TDE.

knowledgebase.progress.com/.../000045217

Posted by Richard Banville on 05-Apr-2019 16:49

The audit data does include a hash to ensure it has not been tampered with.

The addition of TDE would be an additional layer to the security onion since it will prevent reading the data from disk as well.

Just FYI, the block CRCs are not there for security - they are there to help identify data corruption.

Posted by gus bjorklund on 07-Apr-2019 15:04

> On Apr 5, 2019, at 12:40 PM, George Potemkin wrote:

>

> Adding a sewing kit to a knife and is still not enough to become a surgeon. ;-)

>

>

>

true. but i am a rocket surgeon. :)

This thread is closed