Editing Database Files Directly - Validation - Forum - OpenEdge RDBMS - Progress Community

Editing Database Files Directly - Validation

 Forum

Editing Database Files Directly - Validation

This question is answered

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. 

Verified Answer
  • > 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.

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

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

  • > 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.

  • > 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.

  • 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 ...

  • 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.

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

  • 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. 

  • 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.

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

  • > 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.

  • 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.

  • 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)
  • 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;)

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

  • 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