0

I am writing an INSERT query that inserts a large amount of values into a table. Alas, I cannot use parameterized queries in this special case.

So I would like to have a function to remove all bad characters that lead to an INSERT failure. E.g. in the following query

INSERT INTO myTable (a,b,c) VALUES ("a","b","c")

If the value of a contains a quote ", this will probably ruin the entire INSERT. So is there a function to remove all these risky characters? Otherwise, what are the characters that are dangerous?

disasterkid
  • 6,948
  • 25
  • 94
  • 179

1 Answers1

0

Use sql-parameters to prevent any injection in your script:

SqlCommand command = new SqlCommand("INSERT INTO myTable (a,b,c) VALUES (@a,b,@c)", conn);
command.Parameters.AddWithValue("@a", "a");
command.Parameters.AddWithValue("@b", "b");
command.Parameters.AddWithValue("@c", "c");
user1666620
  • 4,800
  • 18
  • 27
Tushar Gupta
  • 15,504
  • 1
  • 29
  • 47
  • My problem is that I am not running a single INSERT. It's something like `INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);` with thousands of rows inserted at once. – disasterkid Feb 25 '15 at 09:48
  • @all Sorry It was typo ...rectified now but that would have also worked fine:) – Tushar Gupta Feb 25 '15 at 09:51
  • @Pedram why can't you just put the sqlcommand into a loop, using the one connection? – user1666620 Feb 25 '15 at 09:52
  • @user1666620 Correct me if I am wrong but isn't an INSERT with multiple rows faster than multiple INSERTS with one row? – disasterkid Feb 25 '15 at 09:57
  • @Pedram http://stackoverflow.com/questions/8218867/c-sharp-sql-insert-command - if you want performance, use BulkInsert : https://msdn.microsoft.com/en-US/library/ms188365.aspx – user1666620 Feb 25 '15 at 10:01
  • @user1666620 I think we have a different case here. In the link you provided, the user is running single INSERTS in both cases. In our case, we have one INSERT with multiple values versus multiple INSERTs that insert one row. – disasterkid Feb 25 '15 at 10:04
  • @Pedram I edited the comment and included Bulk Inserts. Either way, you have an answer as to how to do parameterize your query. – user1666620 Feb 25 '15 at 10:07
  • @user1666620 that bulk insert is an MSSQL command. My question is MySql. – disasterkid Feb 25 '15 at 10:14
  • @user1666620 no I don't have an answer because parameterized queries don't work in my case as I explained above. – disasterkid Feb 25 '15 at 10:16