File Handling A Different Way

Question

File Handling A Different Way

Viewing 8 posts - 16 through 22 (of 22 total)

You must be logged in to reply to this topic. Login to reply

TimothyAWiseman SSCrazy Eights Points: 8819 More actions · Answer 1

There is nothing inherently wrong with doing file handling from within a T-SQL script when needed , and it is possible to set the security on the windows system and on the SQL Server in such a way that it would be just as secure as using .NET code. I have in fact used precisely the technique this article describes to create schema.ini files on an as needed basis in conjunction with using opendatasource to read text files on a non-production system.

While there are certain situations where this is a good technique though, it will not often be the best technique. Depending on the amount and type of information being processed there will very often be a better answer. If it is information the user will need immediately, it is probably better to return it immediately through whatever interface is used. If it is say a report meant to be picked up and read the script could be sent to e-mail it instead of leaving it in a file. If it is a large amount of information then something along the lines of DTS, SSIS, or BCP would be more appropriate.

The technique does have its place, but it is rarely the best one.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/

Dave Vroman SSC Eights! Points: 821 More actions · Answer 2

xp_cmdshell is such a dangerous extention that we've removed it from the SQL servers.

This sounds nice, but it is very dangerous for any web application.

Dave

Sr. Software Engineer - http://www.geeks.com

Tom Garth SSCertifiable Points: 6193 More actions · Answer 3

Samuel Clough (4/22/2008)
I'm surprised by this article. For one thing, as already stated, this stored proc should never be enabled. Secondly, why use a database to do file i/o? I wouldn't want someone writing files on my database server. It sorta smells like a hack.
To me ...

:pinch: I hate infinitives.

Security is a separate issue that always must be dealt with, but there is nothing at all unsecure about using xp_cmdshell in your code.

The article is not in depth enough to make a judgement regarding whether or not using xp_cmdshell was the best method. However when weighing the pros and cons, one must always consider the maintainability of the code. It doesn't get much more maintainable than a stored procedure.

Tom Garth
Vertical Solutions[/url]

"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers

Brandon Forest SSCommitted Points: 1977 More actions · Answer 4

There are times when it is necessary to use xp_cmdshell, but this isn't one of them. As stated in a post above the real issue is securing credentials in the web config files. Using hashing functions is one approach that works there. Other approaches use the built in security of ASP.NET. Several good articles are:

Enhanced and Secure Connection Strings in Web.Config by Vasudevan Deepak Kumar

http://www.codeproject.com/KB/web-security/secure_connectionstrings.aspx

Security Features in ASP.NET - Authentication by Cynthia Carolina

http://www.asp101.com/articles/cynthia/authentication/default.asp

How to use the ASP.NET utility to encrypt credentials and session state connection strings.

http://support.microsoft.com/kb/329290

Brandon Forest

Mike DiRenzo SSCrazy Points: 2013 More actions · Answer 5

Hmm, Xp_cmdShell huh? Hmmm. If it works, it works. Although....ah forget it. This one has been beat to pulp.

Thanks for the article.

-M

voutmaster Mr or Mrs. 500 Points: 517 More actions · Answer 6

With SQL CLR you can build something more elegant if you truly wanted to be able to write files through your data engine. However, I think this type of activity belongs in your app-tier.

Also, at first glance it seems you're limited to writing files that contain only ascii and one line at a time.

shashank-666535 Old Hand Points: 348 More actions · Answer 7

SQL CLR will require sql-server 2005 only. right??? secondly, the idea of writing to a file this way shows a way to anyhow pass a "string(any big, any format, embedded with n number of \r)" to xp_cmdshell.

Hope i could put up my point..

voutmaster Mr or Mrs. 500 Points: 517 More actions · Answer 8

Yes, the use of CLR requires that you're on SQL 2005... Given that we're mid-way through the year 2008 and SQL 2008 is now available, I don't think it's unreanosable that anyone starting new development would be using SQL 2005.

Also, it's not unlimied, the command string you pass to xp_cmdshell can be either an nvarchar(4000) or a varchar(8000). I was wrong about the ascii requirement.