How to store a Java byte array in an Oracle database?

Question

I have a password key for an AES cipher, and I need to store it in an Oracle database column. The password is generated as a byte array in Java, and so I need to figure out the best way to convert it into a data type that Oracle understands and vice versa.

Solution 1

Assuming that the byte array in Java has fewer than 4000 elements, you can store it in a RAW column in Oracle. This tells Oracle that the data is binary so it won't ever attempt to do character set conversion. And it is the least amount of overhead (both in terms of storage and in terms of the complexity of working with the data).

If the byte array can potentially have more than 4000 elements, you can store it in a BLOB column.

Solution 2

Use a BLOB column and a PreparedStatement:

CREATE TABLE pwd_table (id integer primary key, pwd blob);

Then in your Java code:

byte[] data = ... // obtain the byte array
PreparedStatement pstmt = connection.prepareStatement(
   "insert into pwd_table (id, pwd) values (?, ?)");
pstmt.setInt(1, 42);
pstmt.setBytes(2, data);
pstmt.executeUpdate();
connection.commit();

Solution 3

Define understand.

If you are storing password keys in databases you might want to rethink that. However, you have a couple of straight forward options.

1. Convert the byte array into a UU Encoded ASCII String then store that in a VARCHAR field.
2. Store the byte array into a CLOB or BLOB.

Comments

[a_horse_with_no_name]

I thought RAW was deprecated in favor of BLOB?

[Justin Cave]

@a_horse_with_no_name - LONG and LONG RAW are depricated in favor of CLOB and BLOB. But a nice RAW(500) column is a perfectly appropriate way to store 500 bytes of binary data.

[Admin]

It's actually very secure. The keys are randomly generated per encrypted string, and are salted with a single system property. We also use iterations during encryption to prevent rainbow table attacks.

[user3437460]

Just wondering, shall the pwd be stored as BLOB or CLOB ?

[CAMD_3441]

Is storing byte arrays as a BLOB or RAW still the preferred way to store byte arrays in 2019? Or is there another type to store it as?

[Justin Cave]

@CDVAProgrammer - Yes. If you have binary data, you'd still want to use a RAW or a BLOB.

[CAMD_3441]

awesome thanks! I just tested it out and can write byte arrays as RAW to the DB and can read it just fine from the DB as well.