File Handling A Different Way

  • Comments posted to this topic are about the item File Handling A Different Way

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

    Best Regards,

    Chris Büttner

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

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

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

    Dave

    Trainmark.com IT Training B2B Marketplace
    (Jobs for IT Instructors)

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

    Best wishes,
    Phil Factor
    Simple Talk

  • 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

    Best Regards,

    Chris Büttner

  • 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!)

    Best wishes,
    Phil Factor
    Simple Talk

  • 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;-)

    Best Regards,

    Chris Büttner

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

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

  • Hi Shashank

    I felt something missing in the article. Overall the article seems incomplete. The process may not the best but you tried something different. 🙂

  • 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

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

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

    Best wishes,
    Phil Factor
    Simple Talk

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

Viewing 15 posts - 1 through 15 (of 23 total)

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