I have super ugly code that looks like this:
class User < ActiveRecord::Base
self.table_name = 'users'
User.where(Owner: @owner, User: @user).pluck(:Pass).first
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:
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 end 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 end end # 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 User.reset_column_information # Setup for AES 256 bit cipher-block chaining symetric encryption alg = "AES-256-CBC" digest = Digest::SHA256.new digest.update("symetric key") key = digest.digest iv = OpenSSL::Cipher::Cipher.new(alg).random_iv key64 = Base64.encode(key) # Don't update timestamps ActiveRecord::Base.record_timestamps = false begin # 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) end ensure # Reset timestamp recording ActiveRecord::Base.record_timestamps = true end end def down # To undo or not undo, that is the question... end end
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:
updated_atcolumn to prevent overwriting prior values that may be useful to keep (this is not a material change, so
updated_atdoesn't require updating)
passfield, 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.