Inserting Binary, Repo & Ecto

ccgintranet

Hello everyone,

First post here and I am hoping someone can help expand my knowledge as I am clearly not handling a situation we are having properly.

We are using ExCrypto which directs to the underlying :crypto.block_encrypt/decrypt.

Our Ecto schema looks something like this:

    schema "bank" do
      field :uuid, :binary
      field :iv, :binary
      field :tag, :binary
    end

Part of the logic calls to ExCrypto.rand_bytes which returns a random binary stream.

An example here is if I attempt to insert it using Repo I will receive an expected struct that allows me to decrypt some cipher text.

%Api.Bank{
  __meta__: #Ecto.Schema.Metadata<:loaded, "bank">,
  id: 2,
  iv: <<155, 6, 136, 177, 177, 49, 143, 88, 129, 95, 129, 232, 197, 222, 16,
    200>>,
  tag: <<76, 5, 248, 99, 14, 234, 17, 201, 220, 233, 231, 86, 228, 193, 77,
    102>>,
  uuid: "a2eQg6kJiQ82CdpQD5SjBA=="
}

By requerying the database with that particular uuid (this would be a random string) I seem to get a completely different output.

%Api.Crypt{
  __meta__: #Ecto.Schema.Metadata<:loaded, "bank">,
  id: 2,
  iv: <<63, 6, 63, 63, 63, 49, 63, 88, 63, 95, 63, 63, 63, 63, 16>>,
  tag: <<76, 5, 63, 99, 14, 63, 17, 63, 63, 63, 63, 86, 63, 63, 77, 102>>,
  uuid: "a2eQg6kJiQ82CdpQD5SjBA=="
}

This tells me that the data being passed over to Repo changed somewhere between that call and it going into the database.

Working from this dataset in memory is fine, using ExCrypto.encrypt & ExCrypto.decrypt but as soon as I persist it to the database and retrieve it its garbled.

The reason its being persisted is we are using a backend RabbitMQ queue which picks up something we are doing at a later date.

My question, is am I not understanding how binaries work in Elixir or is something happening to my raw binary down the line when Repo inserts it?

Base.encode64 and Base.decode64 works fine, nothing is lost to and fro the database which makes me question how is the binary stream being handled here.

We use a MySQL database (5.6), InnoDB, utf8mb4_0900_ai_ci using BLOB columns.

Help is much appreciated,

Many thanks

5 comments

/phoenix #ecto #repo

0 3025 5

2019-01-10 14:44:37 UTC

Showing Posts 1 to 5

Show Best Posts
Show All (oldest first)
Show All (newest first)

voughtdq

<<155, 6, 136, 177, 177, 49, 143, 88, 129, 95, 129, 232, 197, 222, 16, 200>>
<<63, 6, 63, 63, 63, 49, 63, 88, 63, 95, 63, 63, 63, 63, 16>>

Something is changing it. If the byte is greater than 128 (with the current data we have), it seems to get dropped to 63.

Post #1

NobbZ

128 and greater is not valid ASCII anymore, nore were the given string valid UTF8.
63 is ASCII (and UTF8) for a questionmark.

Can you please check the generated database query? Does that already contain the garbled data or is the query generated correctly? If you try to insert an opposing dataset manually into the database, containing only a single byte greater than 127, can you retrieve it back correctly?

Post #2

ccgintranet

Hello,

Thanks for your response, appreicated.

The generated query looks fine, it contains the correct data.

We also have another system written in PHP that writes data to this database (works perfectly); openssl_encrypt using AES_256_GCM (ciphered text); that same procedure has been replicated in Elixir.

Query looks OK as far as I can see

Inserting manually looks OK this end too.

1%20byte%20insert !

Extracting that seems fine.

I did notice bytes larger than 127 were being converted, question mark “?” in ASCII makes sense now.

I am not sure why its being represented as ASCII however.

Thanks for your help.

Post #3

voughtdq

It seems like PHP’s openssl_encrypt automatically encodes to base64. I wonder if you would have the same problem when using the OPENSSL_RAW_DATA option in your PHP implementation.

You may need to create a custom ecto type to encode/decode the binary.

Post #4

ccgintranet

Hello, apologies for the late reply.

Yes we are inserting the raw binary into the database from PHP’s end rather than base64.

Our PHP implementation and Elixir implemenation both share the same table in the database.

The data encrypted by PHPs end stores the IV and tag in the database and sends over the UUID to retrieve that same IV and tag from the database.

The same goes the other way and internally for both PHP and Elixir.

On the Elixir side we use ExCrypto to simply use that IV and tag to decrypt it; if the IV and tag were base64 ExCrypto would fail and our app wouldn’t function.

Thanks for the suggestion about the custom echo type. I will look into this!

Post #5

— All posts loaded —