Perry Whittle (2/10/2012)
The problem with a vendor supplied CLR is you don't have access to the source, it could be doing literally anything, you place an awful lot of trust in them :w00t:
Hi Perry. I have heard this concern voiced once or twice before and I always felt it was rather mind-boggling for several reasons:
1) Unless are you using mostly or all open source software (e.g. Linux/Apache/MySQL/PHP), you generally do not have any source code to the major software that you are using. Not having source code to Windows, SQL Server, .Net, Visual Studio, or even smaller tools like SQL Prompt, .Net Reflector, Fiddler, etc. doesn't seem to bother most people.
2) Why would there be an assumption that anything malicious is being done in the code? Sure, I suppose it is always possible, but how often has this really happened within the realm of business software? Maybe more often in free software as that is a wider audience and more likely to accomplish whatever goal of the malicious code, but for paid software? What incentive would there ever be for that? In fact, as a software vendor I have every incentive to NOT do anything malicious and to make sure that my code performs as best as possible.
3) If you want to claim that some business software vendors still can, or maybe even have in the past, put in malicious code, you still can't distinguish between large/established companies and smaller/newer companies. Microsoft Word had personal identifying information in it, at least as of Office 2000 or 2003 which is how they tracked down the author of one of the more famous viruses. And Excel (I think also Office 2003) had a flight simulator Easter Egg in it?
4) What would most people do even if they had the source code? Would they be able to read it? Depending on how much there was, would they have time to read through it? Sure, for a project that has its source code available for all to see online, then theoretically over time you would expect that eventually someone will find it and point it out. But that doesn't guarantee that anyone who previously downloaded it would be notified of that.
5) Being developed in-house in either SQLCLR or even T-SQL doesn't truly guarantee safety. Yes, more likely to be safe as you at least have the option to review the code, but in larger shops with many developers it is highly unlikely that the DBAs ever see even the T-SQL code that is running until it shows up in search for longest-running queries or seen in Profiler as taking up too much CPU, memory, or reads. But we have well over 10,000 stored procs, functions, views, triggers, etc and there is no real practical way to guarantee against bad code, whether malicious or accidental. And I have seen DBA’s write absolutely horribly inefficient SQL code and it didn’t help that other DBA’s had seen it as none of them knew exactly why it was inefficient.
It seems that there are really only two reasons to want or even need the source code for externally produced software:
1) Performance. You want code that goes into Production to perform well. If you do not have the source code you most likely can’t see what it is doing, BUT you can test the effects of the code on the system before it goes to Production. This is something that should be done for inhouse code as well. Hence this is not a true reason to shy away from vendor supplied software as you would have fair warning against poor performing code.
2) Maintenance. Some vendors do go out of business and it is reasonable that people incorporating someone else’s software as part of their software that generates their revenue would want some safety net in case a problem is found and needs to be fixed. In these cases many companies provide a source-code license for an additional cost that mitigates this particular concern while still protecting the software vendor.