0

I have a table with one single text column(there are some other columns. But they are irrelevant here). The values in this column can contain duplicates. The table doesn't have any primary key or other unique data to individually identify each row.

The text was previously encrypted using Blowfish algorithm. But we are now planning to encrypt the text using AES.

So we take a batch of rows from the table and then run it through our java code, which does the conversion from Blowfish to AES. But, since we don't have a unique key to identify the row, how do I make sure that we are updating the correct column.

I can think of two ways to do this. 1) Add a auto-incrementing primary key/unique key to the DB. Which although is ideal, will result in lots of code changes in our application. 2) Do the update using rownum, in some weird way. I am concerned about the data-integrity with this approach.

Is there any other way to do this? or What the best approach?

We are using an Oracle SQL DB and JDBCTemplate.

Ashish Joseph
  • 1,103
  • 3
  • 12
  • 35
  • 1
    Considered using oracle's [rowid](https://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns008.htm)? – ernest_k Apr 06 '20 at 06:14
  • Beware; rowids can change, and should not be relied upon as a primary key unless you're willing to take the risk if they do change mid operation. https://stackoverflow.com/questions/435109/what-can-cause-an-oracle-rowid-to-change – Caius Jard Apr 06 '20 at 06:56

1 Answers1

0

In the scenario you've described I'm not sure why you care; just download the row, decrypt it, delete it, reencrypt it and insert the new values. If multiple rows change as a result then so be it; you can't tell what they were before anyway and you say there is nothing else on the row that uniquely identifies it so the system must be tolerant of losing information

What I mean by this is, say you had two text values:

hello
goodbye

And you encrypted them and they became the same:

1235
1235

It's either that one of them is now lost, and decrypting 1235 will always produce "hello" or its that the decrypted knows that 1235 will either produce "hello" or "goodbye" - you can run both of these "hello"/"goodbye" through your new algorithm that does produce unique values 111 and 222, so you can safely erase all the 1235 from the table (delete from t where enctext = 1235) and insert 111 and 222..

Having duplicated 1235 in the table wasn't helpful in any way( you couldn't say "this 1235 decrypts to hello, but that 1235 decrypts to goodbye" because then some other piece of knowledge must have been added- knowledge which can also uniquely identify the row. If things are as you say you don't need to be concerned about which was what; either was both and you can either retrieve to original data for reencryption or you can't.

Caius Jard
  • 72,509
  • 5
  • 49
  • 80