locked
SQLBulkCopy and protection from SQL injection RRS feed

  • Question

  • Does SQLBulkCopy automatically parameterize all the fields?

    Or does it need to be done separately?

    Essentially I am bulk loading data from a file into a staging table before processing it further. While unlikely, its possible the fields could contain an SQL injection and was hoping the SQL Bulk Copy would take care of this for me.

    Thanks,
    Jake
    Wednesday, June 18, 2008 8:37 PM

Answers

All replies

  • Sorry, I should have been more detailed.

    I parse the information from the file into the temporary staging table and then (once the data has been finessed) I insert it into multiple different tables using INSERTs and UPDATEs using the data from the staging table.

    So essentially I'd like to know if the SQLBulkCopy will take care of the parameterization the data and protect me with the INSERTs and UPDATEs that will occur later, or do I need to address those concerns with each statement as they occur.

    e.g.

    If I use SQLBulkCopy to load a URL into the staging table and the URL contains an attempted SQL injection and then I later take that value from the staging table and insert it into a table using an INSERT, will I need to parameterize the field in the INSERT statement? Or will it already be taken care of by the SQLBulkCopy.

    OR

    If I use SQLBulkCopy to load a user's name field into my staging table and the name has an apostrophe (O'Brien) and then I later take that value from the staging table and insert it into a table using an INSERT, will I need to parameterize the field in the INSERT statement? Or will it already be taken care of.


    Thursday, June 19, 2008 11:45 AM
  •  If I use SQLBulkCopy to load a user's name field into my staging table and the name has an apostrophe (O'Brien) and then I later take that value from the staging table and insert it into a table using an INSERT, will I need to parameterize the field in the INSERT statement? Or will it already be taken care of.

    No, it will not be take care of.

    You may double check on my response by posting in one of SQL Server forums but my answer to you will be unequivocal: SqlBulkCopy will not do any parametrization for you and the parameterization is the way to go not only in the face of injection attacks but for the reason you outlined as well as perhaps some other bizarre characters that may occasionally come your way. I don't think, however, that the parameterization alone will protect you against injections. Don't count on it. You have to analyze every input, do Regex validation, perhaps with a few patters, and do other things I may not be aware of.


    AlexB
    Thursday, June 19, 2008 12:30 PM
  •  For questions and discussions regarding data platform development using classic ADO.NET (v 1.1 and 2.0) and types in the System.Data namespace, please see http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=45&SiteID=1

    http://www.peterRitchie.com/blog
    • Marked as answer by Peter Ritchie Friday, June 20, 2008 4:51 PM
    Friday, June 20, 2008 4:50 PM