6

I need to insert multiple rows at the same time (1000 rows) into a SQL Server database. I think best way is to use SqlBulkCopy but I'm not sure how to parametrize insert queries to be safe from SQL injection.

Can you please help me? What is best way to perform multiple insert statements (SQL injection safe)?

Thank you.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user2980426
  • 799
  • 5
  • 8
  • SqlBulkCopy is .net class copy managing transfer from one datasource to another. Bulk Insert is a feature of SQL Server. Neither has issues with injection attacks and both are good ways to insert lots of rows. This is not the place to ask for tutorials, show what your have tried and the problem your are having. – Gary Walker May 12 '15 at 20:39
  • Thank you for your answer. I don't want tutorial. I have a lot of rows (user inputs). E.g. I have 1000 rows. I need to use best way to insert all user defined rows into a MS SQL Server database. I can define SqlCommand and sql parameters for each row but it is slow. I just don't know whether SqlBulkCopy use SqlParameter internally or do I have to use my own SQL injection protection. Thank you very much. – user2980426 May 13 '15 at 08:30
  • S/O frowns upon opinion questions, e.g., what it the best way ... As asked, you pretty much ask how to avoid sql injection attacks. The bulk insert methods are injection safe as they do not depend upon generating sql. You can see previous similar answers. http://stackoverflow.com/questions/2624713/how-do-i-insert-multiple-rows-without-repeating-the-insert-into-dbo-blah-part or look up other tutorials and show what you have tried and the problem you are having. – Gary Walker May 13 '15 at 09:10

2 Answers2

3

The best way to insert multiple rows is by using SqlBulkCopy.

The SqlBulkCopy class is already safe from SQL Injection. So you don't have to worry about this.

Jonathan Magnan
  • 10,874
  • 2
  • 38
  • 60
0

I have used this solution on multiple occassions to do multiple inserts : http://www.sqlteam.com/article/sql-server-2008-table-valued-parameters

Keep in mind that there is an issue with the SQL server security for table valued types. You need to use a wonky syntax to set them:

grant execute on TYPE::dbo.tableType to role_or_user
Jim
  • 864
  • 1
  • 8
  • 16