CODEWITHSUNDEEP
ruby-on-railsrails-activerecord
Will Gordon
Will Gordon

Reputation: 3573

Modify value before using in SQL where clause?

Is there a way that I can modify a value in my model before it goes to MySQL in the WHERE clause?

Specifically, I'm working with phone numbers, and to ensure user privacy, I'm only storing SHA256 hashed phone numbers in the DB. But, I want to be able to act on the model as if it's not hashed.

For example:

def phone_number=(number)
    write_attribute(:phone_number, OpenSSL::HMAC.hexdigest('SHA256', 'salt', number))
end

This lets me save the raw phone number and have it auto-hashed. But is there a way I can do

PhoneNumbers.where(phone_number: '555-1234')

and have '555-1234' get hashed before it gets translated into SQL?

Upvotes: 1

Views: 187

Answers (2)

Will Gordon
Will Gordon

Reputation: 3573

A custom Serializer solved my use case for this perfectly!

# lib/serializers/phone_hash_serializer.rb
class PhoneHashSerializer
  def self.load(value)
    value.to_s
  end

  def self.dump(value)
    value = value.to_s
    if value.start_with? 'hashed!'
      value
    else
      # Rails serializer's do not handle one-way hashing well, as the value ends up getting
      # getting dumped/hashed twice (once on the type-cast, then again before storing to DB).
      # So, we "tag" the value as "hashed!" to ensure we don't double hash since search's
      # only get hashed once, and would therefore never match the DB value
      'hashed!' + hash_string(value)
    end
  end

  private

  def self.hash_string(value)
    OpenSSL::HMAC.hexdigest('SHA256', 'hash_salt', value)
  end
end


# models/phone_number.rb
require 'serializers/phone_hash_serializer'
...
serialize :phone_number, PhoneHashSerializer
...

I used http://ruby-journal.com/how-to-write-custom-serializer-for-activerecord-number-serialize/ as my muse. Thanks to those who helped!

Upvotes: 0

arieljuod
arieljuod

Reputation: 15838

Check this https://api.rubyonrails.org/classes/ActiveRecord/Attributes/ClassMethods.html, specially the Creating Custom Types title.

You define a custom type and set that as the type of your attribute. You define a "serialize" method that converts your value to use SQL statements.

From the docs:

class Money < Struct.new(:amount, :currency)
end

class MoneyType < Type::Value
  def initialize(currency_converter:)
    @currency_converter = currency_converter
  end

  # value will be the result of +deserialize+ or
  # +cast+. Assumed to be an instance of +Money+ in
  # this case.
  def serialize(value)
    value_in_bitcoins = @currency_converter.convert_to_bitcoins(value)
    value_in_bitcoins.amount
  end
end

# config/initializers/types.rb
ActiveRecord::Type.register(:money, MoneyType)

# app/models/product.rb
class Product < ActiveRecord::Base
  currency_converter = ConversionRatesFromTheInternet.new
  attribute :price_in_bitcoins, :money, currency_converter: currency_converter
end

Product.where(price_in_bitcoins: Money.new(5, "USD"))
# => SELECT * FROM products WHERE price_in_bitcoins = 0.02230

Product.where(price_in_bitcoins: Money.new(5, "GBP"))
# => SELECT * FROM products WHERE price_in_bitcoins = 0.03412

As you can see, the where method receives a Money object, but the SQL statement has a decimal value.

Upvotes: 4

Related Questions