How to disable ActiveRecord logging for a certain column?

Question

I'm running into a problem which, in my opinion, must be a problem for most rails users but I could not find any solution for it yet.

When, for instance, performing a file upload of a potentially large, binary file and storing it in the database, you most certainly don't want rails or ActiveRecord to log this specific field in development mode (log file, stdout). In case of a fairly big file, this causes the query execution to break and almost kills my terminal.

Is there any reliable and non-hacky method of disabling logging for particular fields? Remember, I'm not talking about disabling logging for request parameters - this has been solved quite nicely.

Thanks for any information on that!

Venation

There is an option to filter that like passwords, do not know if that would help. Would you consider overriding ActiveRecord execute hacky? if not then there's your hint. – Eucaine

Answer 1

If this helps anyone, here is a Rails 4.1 compatible version of the snippet above that also includes redaction of non-binary bind params (e.g. a text or json column), and increases the logging to 100 char before redaction. Thanks for everyone's help here!

class ActiveRecord::ConnectionAdapters::AbstractAdapter
  protected

  def log_with_binary_truncate(sql, name="SQL", binds=[], statement_name = nil, &block)
    binds = binds.map do |col, data|
      if data.is_a?(String) && data.size > 100
        data = "#{data[0,10]} [REDACTED #{data.size - 20} bytes] #{data[-10,10]}"
      end
      [col, data]
    end

    sql = sql.gsub(/(?<='\\x[0-9a-f]{100})[0-9a-f]{100,}?(?=[0-9a-f]{100}')/) do |match|
      "[REDACTED #{match.size} chars]"
    end

    log_without_binary_truncate(sql, name, binds, statement_name, &block)
  end

  alias_method_chain :log, :binary_truncate
end

Domineca

This definitely works up to Rails 4.2 but doesn't work in 5.0+. As best I can tell, it's not needed in Rails 5 either because Rails seems to have similar functionality built in. – Near

@Near what is built in functionality? – Minorca

Answer 2

NOTE: Works with rails 3, but apparently not 4 (which was not released when this question was answered)

In your application.rb file:

config.filter_parameters << :parameter_name

This will remove that attribute from displaying in your logs, replacing it with [FILTERED] The common use case for filtering parameters is of course passwords, but I see no reason it shouldn't work with your binary file field.

Fluoresce

Doesn't this just filter the HTTP request parameters rather than the parameters in the SQL log? – Venation

Nope, it filters everything in the rails logs. Both the params hash on incoming requests and SQL statements logged will show [FILTERED] – Fluoresce

Thank's! I'm gonna try that and report back asap. – Venation

This does not work for me. Paremeter logging is filtered, but my file's data attribute is still printed as a string in the sql query logging. – Shavonneshaw

The question was not about mysql logs, but about rails logs. This method works for all rails logs. – Fluoresce

This will not hide stuff from the active record logging. Tested with Rails 4. See @tovodeverett solution. – Sherlene

@Sherlene this works in Rails 3, sounds like not in 4 though. Notice the answer date though :) – Fluoresce

ah, that might be. Well, if it works your answer is nicer :). – Sherlene

I don't think this works in Rails 3.2, but it's very possible I'm just being stupid. – Uranalysis

This approach works well in the newest versions of Rails, tested on 7.1.3.2. On current Rails versions, new applications were created with an initializer file: config/initializers/filter_parameter_logging.rb which already filters some obvious cases (eg: password), and yes, it does remove from the loggers in all cases, including ActiveRecord insert/update SQLs. Refs.: api.rubyonrails.org/classes/ActiveSupport/ParameterFilter.html – Cattle

Answer 3

Create a file in config/initializers whitch modifies ActiveRecord::ConnectionAdapters::AbstractAdapter like so:

class ActiveRecord::ConnectionAdapters::AbstractAdapter
   protected

   def log_with_trunkate(sql, name="SQL", binds=[], &block)
     b = binds.map {|k,v|
       v = v.truncate(20) if v.is_a? String and v.size > 20
       [k,v]
     }
     log_without_trunkate(sql, name, b, &block)
   end

   alias_method_chain :log, :trunkate
end

This will trunkate all fields that are longer than 20 chars in the output log.

Diggins

Should also note that this will only truncate insert requests. in the event of update everything is thrown in in the sql param so that also needs to be truncated. – Diggins

wrong number of arguments (3 for 2) in the 'log_without_trunkate' call in Rails 3.0.9. – Shavonneshaw

The log method for 3.0.x rails use a log method that only takes two arguments 'sql' and 'name'. so remove binds and the block from argument and just filter out from the sql argument. – Diggins

wrong number of arguments (4 for 1..3) in the 'log_without_trunkate' call in Rails 4.1.4 – Carthy

@Carthy see dbortz solution below for rails 4.1 – Carthy

Answer 4

Here's an implementation of the approach suggested by @Patrik that works for both inserts and updates against PostgreSQL. The regex may need to be tweaked depending upon the formatting of the SQL for other databases.

class ActiveRecord::ConnectionAdapters::AbstractAdapter
   protected

   def log_with_binary_truncate(sql, name="SQL", binds=[], &block)
    binds = binds.map do |col, data|
      if col.type == :binary && data.is_a?(String) && data.size > 27
        data = "#{data[0,10]}[REDACTED #{data.size - 20} bytes]#{data[-10,10]}"
      end
      [col, data]
    end

    sql = sql.gsub(/(?<='\\x[0-9a-f]{20})[0-9a-f]{20,}?(?=[0-9a-f]{20}')/) do |match|
      "[REDACTED #{match.size} chars]"
    end

    log_without_binary_truncate(sql, name, binds, &block)
   end

   alias_method_chain :log, :binary_truncate
end

I'm not deliriously happy with it, but it's good enough for now. It preserves the first and last 10 bytes of the binary string and indicates how many bytes/chars were removed out of the middle. It doesn't redact unless the redacted text is longer than the replacing text (i.e. if there aren't at least 20 chars to remove, then "[REDACTED xx chars]" would be longer than the replaced text, so there's no point). I did not do performance testing to determine whether using greedy or lazy repetition for the redacted chunk was faster. My instinct was to go lazy, so I did, but it's possible that greedy would be faster especially if there is only one binary field in the SQL.

Musculature

IMHO. This is the best solution in this question. – Sherlene

This is the regexp for mysql. Do you wanna add it to your solution? /(?<=x'[0-9a-f]{20})[0-9a-f]{20,}?(?=[0-9a-f]{20}')/ – Sherlene

Answer 5

In rails 5 you could put it in initializer:

module SqlLogFilter

  FILTERS = Set.new(%w(geo_data value timeline))
  def render_bind(attribute)
    return [attribute.name, '<filtered>'] if FILTERS.include?(attribute.name)
    super
  end

end
ActiveRecord::LogSubscriber.prepend SqlLogFilter

For filter attributes geo_data, value and timeline for instance.

Minorca

Answer 6

Here is a Rails 5 version. Out of the box Rails 5 truncates binary data, but not long text columns.

module LogTruncater
  def render_bind(attribute)
    num_chars = Integer(ENV['ACTIVERECORD_SQL_LOG_MAX_VALUE']) rescue 120
    half_num_chars = num_chars / 2
    value = if attribute.type.binary? && attribute.value
      if attribute.value.is_a?(Hash)
        "<#{attribute.value_for_database.to_s.bytesize} bytes of binary data>"
      else
        "<#{attribute.value.bytesize} bytes of binary data>"
      end
    else
      attribute.value_for_database
    end

    if value.is_a?(String) && value.size > num_chars
      value = "#{value[0,half_num_chars]} [REDACTED #{value.size - num_chars} chars] #{value[-half_num_chars,half_num_chars]}"
    end

    [attribute.name, value]
  end

end

class ActiveRecord::LogSubscriber
  prepend LogTruncater
end

Rapids

Rails 5.2 has changed, it expects 2 parameters. But the render_bind method seems to be gone at all according to the docs... – Cha

Answer 7

I didn't find much on this either, though one thing you could do is

ActiveRecord::Base.logger = nil

to disable logging entirely, though you would probably not want to do that. A better solution might be to set the ActiveRecord logger to some custom subclass that doesn't log messages over a certain size, or does something smarter to parse out specific sections of a message that are too large.

This doesn't seem ideal, but it does seem like a workable solution, though I haven't looked at specific implementation details. I would be really interested to hear any better solutions.

Essa

Yes, this is the only possibility that came in my mind, but I find this solution to be quite ugly (as you mentioned) since you definitely want the call to be logged but just not this field. If there will be no "perfect" answer I might go ahead and implement such a custom logger which parses the log statement...ugly as well. – Venation

Looks like you'd want to override the "add" method on Logger, the subclassed version can do your parsing and then call the superclass add method, passing the parsed message and other parameters. – Essa

Answer 8

I encountered the same problem, but I couldn't figure out a clean solution to the problem. I ended up writing a custom formatter for the Rails logger that filters out the blob.

The code above needs to be placed in config/initializers, and replace file_data with the column you want to remove and file_name with the column that appears after in the regular expression.

Karrykarst

Answer 9

version for Rails 5.2+

module LogTruncater
  def render_bind(attr, value)
    num_chars = Integer(ENV['ACTIVERECORD_SQL_LOG_MAX_VALUE']) rescue 120
    half_num_chars = num_chars / 2

    if attr.is_a?(Array)
      attr = attr.first
    elsif attr.type.binary? && attr.value
      value = "<#{attr.value_for_database.to_s.bytesize} bytes of binary data>"
    end

    if value.is_a?(String) && value.size > num_chars
      value = "#{value[0,half_num_chars]} [REDACTED #{value.size - num_chars} chars] #{value[-half_num_chars,half_num_chars]}"
    end

    [attr && attr.name, value]
  end

end

class ActiveRecord::LogSubscriber
  prepend LogTruncater
end

Rapids

Answer 10

This is what works for me for Rails 6:

# initializers/scrub_logs.rb

module ActiveSupport
  module TaggedLogging
    module Formatter # :nodoc:
      # Hide PlaygroundTemplate#yaml column from SQL queries because it's huge.
      def scrub_yaml_source(input)
        input.gsub(/\["yaml", ".*, \["/, '["yaml", "REDACTED"], ["')
      end

      alias orig_call call

      def call(severity, timestamp, progname, msg)
        orig_call(severity, timestamp, progname, scrub_yaml_source(msg))
      end
    end
  end
end

Replace yaml with the name of your column.

Alchemy