Changing the manner in which columns get logged

Hello,

I'm evaluating LS for replacing EF.  

Consider the case where you have information of a sensitive nature, such as a U.S. Social Security Number, stored in the database.  We'd typically do whatever we can not to display the information to unauthorized users.  When logging is enabled, LS4 will happily log this data to logs and make it available to who ever has access to it.  This poses a problem in production if we'd like to diagnose issues.

Can one mask certain columns when they are logged?  

For example, instead of logging 123-45-6789 to the log when I create a Person entity with a SSN column I would like to see "***********", [FILTERED], "###########" or another mask rather than the value "123-45-6789" in the logs.  I hope it makes sense. 

Thanks,

Werner

The built-in loggers don't support this, but for production logging you'll probably be writing a custom logger (e.g. a file or log4net logger) by implementing the ILogger interface.  The object passed to ILogger.LogSql is a CommandLog object, not a string, so you can format that however you like in the log output.  Just implement your ILogger to mask out the sensitive command parameters when writing out the CommandParameters collection.

Hi Ivan,

I gave it a go and was unable to determine which parameters were the ones I wanted to filter. The parameters looked as follows: @p0, @p1, @p2 and so forth.  Am I missing something?

Werner

P.S. Our aim is to support multiple database engines, so I'd like to avoid provider specific code where possible.

No, you're not missing anything -- it's me not thinking things through.  You're right, there's no easy way to relate parameters to the fields they're coming from, and I'm not sure how feasible it will be to add this.

What scenarios are you looking at -- values being sent in INSERT and UPDATE, or do you also need to filter on comparands in SELECT (e.g. if the user is searching for their own SSN)?  We may be able to implement the former, the latter could be more difficult...

Thanks.  I have not seen results being written logged for SELECT statements, so while things remain like that, INSERT and UPDATE will suffice.  

Werner

Just realized you were talking about queries in SELECT statements.  That does pose a problem.  The same with parameters of stored procedures (if they are supported by LS).  

Werner

Beginning with the next nightly build, you will be able to look at IDataParameter.SourceColumn in a CommandLog.CommandParameters collection, and it will contain the column name with which the parameter is associated.  However, some caveats:

* This is somewhat heuristic, and has not been tested on extremely complex queries.  It should be reliable for inserts, updates and simple select queries, but it should be considered beta -- let us know if you see bugs.

* The name is the database column name, not the LightSpeed property name.  This will only matter if you have explicit column mappings.

* LightSpeed recycles parameters, so if you use the same value several times in the same SQL batch, we will create only one parameter object and reuse it each time that value occurs.  This can result in the same parameter object being used in different columns.  In this case, the SourceColumn will be whichever one we found it against first.  We do not expect this to be an issue for your use case, as you won't be using the same data with both 'sensitive' and 'non-sensitive' columns.

Again, please consider this beta and let us know about any bugs you run into.

So far, its works great for me.