SQLServerCentral / Article Discussions / Article Discussions by Author / Discuss content posted by shashank bhide / File Handling A Different Way / Latest Posts

RE: File Handling A Different Way

voutmaster — Wed, 21 May 2008 09:00:49 GMT

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.

RE: File Handling A Different Way

shashank-666535 — Wed, 21 May 2008 02:06:38 GMT

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..

RE: File Handling A Different Way

voutmaster — Tue, 20 May 2008 11:01:23 GMT

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.

RE: File Handling A Different Way

Mike DiRenzo — Wed, 23 Apr 2008 16:20:48 GMT

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

RE: File Handling A Different Way

Brandon Forest — Wed, 23 Apr 2008 09:53:10 GMT

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 Kumarhttp://www.codeproject.com/KB/web-security/secure_connectionstrings.aspxSecurity 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

RE: File Handling A Different Way

Tom Garth — Wed, 23 Apr 2008 09:09:29 GMT

[quote][b]Samuel Clough (4/22/2008)[/b][hr]I'm surprised by this article. For one thing, as already stated, this stored proc should [b][i]never [/i][/b]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 ...[/quote]: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.

RE: File Handling A Different Way

Dave Vroman — Tue, 22 Apr 2008 14:03:22 GMT

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.DaveSr. Software Engineer - www.geeks.com

RE: File Handling A Different Way

timothyawiseman — Tue, 22 Apr 2008 11:43:17 GMT

There is nothing inherently wrong with doing file handling from within a T-SQL script [b] when needed [/b], 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 [b] non-production [/b] 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.

RE: File Handling A Different Way

Your Name Here — Tue, 22 Apr 2008 07:31:26 GMT

I have to agree with Phil. "New" is kind of misleading since any DBAs worth their paycheck knew about using xp_cmdshell about 15 minutes after it was introduced back around SQL 4.21 . Given the very narrow constraint of reserving this file creation to the handful of people blessed with membership in the sysadmins role, used for specialty troubleshooting sessions and subsequent mandatory file cleanup I'd say the article's suggestion has merit. In my environments that contain HIPAA data, this would be enabled only after that Ghost Whisperer lady put me at peace by shoving me into "the light" because saying "over my dead body" wouldn't be enough.Also, it's pretty shallow-minded to overlook that fact that while one execution of this command may seem harmless and convenient, I look at it from the fact that I have thousands of concurrent connections 7x24. Simply "appending to a file" can run my server out of space in a heartbeat if poor choices are made. In my humble opinion, [absolutely nothing] should consume space on my server's hard drives that I don't have control over.Sound harsh? I'm the "nice guy." My security counterparts would have a field day. Shashank would get hit harder than a bag of jellybeans at a daycare.

RE: File Handling A Different Way

Phil Factor — Tue, 22 Apr 2008 07:29:40 GMT

There are a lot of uses for this technique. Here is an example from Nigel Rivett for doing FTP in a stored procedure http://www.nigelrivett.net/FTP/s_ftp_GetFile.htmlhe also has a clever way of writing format files for BCP in the same way. Naturally, one wouldn't give the normal application user any access to this!.

RE: File Handling A Different Way

JMI — Tue, 22 Apr 2008 07:13:24 GMT

Does it work for everybodey?When I run it form QA it keeps creating an empty file.exec master..xp_cmdshell 'echo "XXX-XXX" >> c:\code\QueryResults.txt'Thanks,Rob

RE: File Handling A Different Way

Anipaul — Tue, 22 Apr 2008 06:53:12 GMT

Hi ShashankI felt something missing in the article. Overall the article seems incomplete. The process may not the best but you tried something different. :)

RE: File Handling A Different Way

rsconnolly — Tue, 22 Apr 2008 06:50:29 GMT

I cannot read the sample code on the form. All I see is a horizontal scroll bar.

RE: File Handling A Different Way

Samuel Clough — Tue, 22 Apr 2008 06:35:58 GMT

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 the obvious solution here is that if you don't want to get asp.net the rights to write to a file, simply create a wcf service on an app server and let it do file i/o for you. It's much cleaner and more secure.This article is certainly creative, but I think there are better ways to accomplish this.

RE: File Handling A Different Way

Christian Buettner-167247 — Tue, 22 Apr 2008 05:27:02 GMT

Hi Phil,Doing file handling in ASP.NET doesn't mean you can't make your SQL Server secure. And I am not denying that it is possible to do file handling in SQL Server securely. What I am saying is that "if there is a more appropriate way of doing something, then use this way". So far I can see no benefit in moving the file handling from ASP.NET to SQL Server. I even doubt that the file handling is faster with xp_cmdshell than with ASP.NET (but as I mentioned before, I am not a ASP.NET person so these are just my assumptions). The original reason for the author for moving the file handling to SQL Server was the missing knowledge of storing credentials securely in ASP.NET. And Phil, my intention was not to question your database design. I am just raising my concern that your initial design can be compromised as soon as a new developer has do code a change request, but isn't aware of the security concerns. This can quickly make your calls to xp_cmdshell insecure. (Ok, you can then blame it on the boss who is not paying enough for your colleagues training;-)

RE: File Handling A Different Way

Phil Factor — Tue, 22 Apr 2008 04:29:42 GMT

Christian,Of course, the use of OLE automation or the command shell presents a particular problem for your database security. The answer is, of course, to meet these security concerns if you need to use these facilities (well, I'd go further and say that you need to make your database secure anyway, whether you use them or not). Your conclusion that you need to do all file handling in ASP.NET, rather than make your SQL Server secure, is puzzling. You are implying that it is impossible to do file handling in SQL Server because of the security issues. I maintain that, if the security in your database is correctly designed, it is perfectly OK to do so.I apologise for arguing the point, but I'd be the first to be worried if there really was a way to 'un-secure' the database systems I design. (other than getting the key to the server room!)

RE: File Handling A Different Way

Christian Buettner-167247 — Tue, 22 Apr 2008 03:31:02 GMT

The problem with this is, that it is also perfectly simple to un-secure your database with xp_cmdshell.In general you should use the right tools for the right tasks. And to use xp_cmdshell to move file handling from ASP.NET to SQL Server is the wrong decision in my opinion.Btw, there is some useful information for those that want to secure xp_cmdshell:http://msdn2.microsoft.com/en-us/library/aa175398(SQL.80).aspx

RE: File Handling A Different Way

Phil Factor — Tue, 22 Apr 2008 02:47:29 GMT

I always prefer to use COM scripting from within TSQL for this sort of job. It is, however, perfectly simple to arrange your database security so that the use of xp_cmdshell is not dangerous. I have to admit that my only real criticism of this article is the title. It is not file-handling, and it is not different. It is one of the oldest tricks used by grey-muzzled SQL Server/Sybase DBAs. It is a rather limited method, though, when trying to write the '>' character to a file, or writing long lines.

RE: File Handling A Different Way

chisholmd — Tue, 22 Apr 2008 02:41:14 GMT

Thanks for the idea that does seem like an easy and performant way to write text files from SQL.As you noted not very secure for public facing web apps. Perhaps its was simply a matter of assigning the ASPNET account write permissions to a single directory and then forbid scripts or executables from that directory. I imagine it would be rather harder to launch an attack from that.

RE: File Handling A Different Way

will.swift — Tue, 22 Apr 2008 01:18:55 GMT

Much safer to impersonate. Take a look at [url]http://msdn2.microsoft.com/en-us/library/ms998283.aspx[/url] that shows how to use the Aspnet_regiis.exe tool to encrypt sections of your configuration files.

RE: File Handling A Different Way

Knut Boehnert — Tue, 22 Apr 2008 01:16:53 GMT

While it is a nice easy way I wouldn't use it in a customer facing application setup due to the identified security risk.All it needs is cracking the security context to allow access to xp_cmdshell.As a DBA using xp_cmdshell for internal purposes is a different matter. It really is a very fast way to get file and directory information and the nice thing is that this information can be loaded into a table variable.

RE: File Handling A Different Way

Christian Buettner-167247 — Tue, 22 Apr 2008 00:16:46 GMT

Hello shashank,In my opinion you should not use the SQL Server DB engine for file handling. I don't know ASP.NET in detail, but I am pretty sure that there must be a quick, easy and secure way to read and write files. (Of course I have no objections to do file handling via DTS or SSIS packages)But I would be interested to know the opinions of others.

File Handling A Different Way

shashank-666535 — Mon, 21 Apr 2008 20:54:54 GMT

Comments posted to this topic are about the item [B]File Handling A Different Way[/B]