Inserting Binary, Repo & Ecto

ecto
phoenix
repo

#1

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


#2

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


#3

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?


#4

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.


#5

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.