Nodir Nasirov Nodir Nasirov - 2 years ago 84
Ruby Question

AES Decryption in ruby and activerecord

I have super ugly code that looks like this:

class User < ActiveRecord::Base
self.table_name = 'users'
def get_password
@test_password = User.find_by_sql "SELECT CAST(AES_DECRYPT(Pass, 'kkk') AS CHAR(50)) Pass From prod.sys_users Where Owner = '"[email protected]+"' AND User = '"[email protected]+"'"

This code works, but it makes me sick, since it is not written according to Ruby Coding Style. So I decided to fix this code, and here what I have so far:

class User < ActiveRecord::Base
self.table_name = 'users'
def get_pass
User.where(Owner: @owner, User: @user).pluck(:Pass).first

So, I am getting encrypted password, how can I decrypt it?
I tired
, but key
here is too short.
How can I resolve this issue?

Answer Source

In a situation like this, you might be better off converting the field values entirely. This could be done in a migration and once it's done, you never have to be concerned about how MySQL has stored the data. It's also one step toward database independence.

So, the migration would basically do 3 things:

  • add a flag column to track which records have been converted
  • iterate over each records, converting the encrypted value and setting the flag
  • remove the flag column once all records have been processed

The migration might look like this:

class ConvertMySqlEncryptedData < ActiveRecord::Migration
  # Local proxy class to prevent interaction issues with the real User class
  class User < ActiveRecord::Base

  def up
    # Check to see if the flag has already been created (indicates that migration may have failed midway through)
    unless column_exists?(:users, :encrypted_field_converted)
      # Add the flag field to the table
      change_table :users do |t|
        t.boolean :encrypted_field_converted, null: false, default: false

    # Add an index to make the update step go much more quickly
    add_index :users, :encrypted_field_converted, unique: false

    # Make sure that ActiveRecord can see the new column

    # Setup for AES 256 bit cipher-block chaining symetric encryption
    alg = "AES-256-CBC"
    digest =
    digest.update("symetric key")
    key = digest.digest
    iv =
    key64 = Base64.encode(key)

    # Don't update timestamps
    ActiveRecord::Base.record_timestamps = false
      # Cycle through the users that haven't yet been updated
      User.where(encrypted_field_converted: false).pluck("CAST(AES_DECRYPT(Pass, 'kkk') AS CHAR(50)) Pass").each do |user|
        # Re-encode the password with OpenSSL AES, based on the setup above
        new_pass = aes.update(user.pass).final

        # Update the password on the row, and set the flag to indicate that conversion has occurred
        user.update_attributes(pass: new_pass, encrypted_field_converted: true)
      # Reset timestamp recording
      ActiveRecord::Base.record_timestamps = true

  def down
    # To undo or not undo, that is the question...

This was off the top of my head, so there may be issues with the encryption. Structure-wise, it should be in good shape, and it takes into account a number of things:

  • Provides incremental database processing by using a flag to indicate progress
  • Uses an index on the flag field to improve query performance, particularly if multiple runs are required to complete processing
  • Avoids updating the updated_at column to prevent overwriting prior values that may be useful to keep (this is not a material change, so updated_at doesn't require updating)
  • Plucks only the pass field, so that transfer overhead is minimized

Now, you can query pass and encrypt/decrypt as needed by the application. You can document and support the field at the application level, rather than rely on the database implementation.

I spent a few years consulting and doing database conversion, either from one database product to another, or as part of a significant version upgrade. It also allows development to use lighter-weight databases (e.g. SQLite) or test viability with other products when upscaling is needed. Avoiding database-specific features, like the MySQL encryption, will save you (or your employer) a LOT of money and hassle in the long run. Database independence is your friend; embrace it and use what ActiveRecord provides to you.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download