What is the best way to add a row to a SQL Server table? Assume that stored procedures aren't an option.
There are 3 methods I can think of using ADO .Net:
1. Execute an Insert statement string
e.g. "Insert into Books (Field1, Field2) Values ('" & myval.replace("'","''") & "', '" & myval2.replace("'","''") & "')"
As long as you double any single quotes in the values, this method seems fine.
2. Paramterized Insert statement
You don't need to worry about single quotes using this method, but seems like it would take longer to write.
3. DataSet and SqlDataAdapter.Update
Dim objConnection As New SqlConnection("Connection String")
Dim objAdapter As New SqlDataAdapter
objAdapter.SelectCommand = New SqlCommand("select * from Books", objConnection)
Dim objBuilder As New SqlCommandBuilder(objAdapter)
Dim objDataSet As New DataSet
This method seems fairly quick to write and you don't need to worry about single quotes. Seems like a good method but I haven't used it much.
Which way is best?
In my opinion the absolute best and safest way to do anything regarding your db interacting with a web app / site is to use stored procedures and pass in the parameters.
If everything happens for a reason what is the reason for this error?
Notwithstanding the Store Procedure argument...
Between options 1 and 2 there's been a bunch of talk avoiding option 1 for the reason you mentioned and in particular, SQL injection.
So yeah, Option 2 is the way to go.
From what I can tell, simply doubling any single quotes in strings that come from users eliminates any threats of SQL injection. Can anyone tell me a scenario where a SQL injection attack could happen if all single quotes are replaced with 2 single quotes?
When you go with option 2, values passed as parms are treated as data rather than as SQL so, the SQL along with the parm isn't expanded into a full SQL string. So even if you don't perform ANY validation on the parm, you are pretty much guaranteed NOT to have an issue.
When you go with Option 1, you really need to make sure that your validation is tight but even then, something could go wrong... Not really an expert on it but to say that you generally have to outsmart every hacker trying to crack your SQL and that's difficult.
You raise a good point about moving from a single to a pair of single quotes and the only thing that comes to mind from the devils perspective is that you'd need to really know the routine used to fix the quotes... For example, what if replacing a single with a pair of singles makes the input string 1 or more characters greater than maximum field size your db is expecting? The hacker plants the seed and makes the last character a single quote and when you replace it with two, when it hits the database it is cropped back to just one?
One thing to consider is that although you might be able to write an air-tight routine to fix the quotes but someone less experienced might not be so gifted... what if the string has so many single quotes that, if for example someone is using a BYTE to loop though the string, it might exceed 255 and jack the code?
Yeah I streching here but hoping to point out that it can be tough to not only out-think the hackers but also to hope that everyone will out-think hackers in the same way.
Did this help?
Ya, no one addressed Option 3.
Hmmm, didn't see that you were asking for Option 3 to be addressed but instead questioned what peeps consider to be the best option of the 3
I suppose O3 is similar to O2 in that your updates to the data are treated as 'data' rather than to be parsed out into SQL as it is in O1.
So I suppose O2 and O3 are similar in approach and which to chose would be dependant on the situation.
Further, I think of O3 strategy as being more applicable towards updating a larger set of records kinda like a mass update scenario. However, I generally don't find myself using DataAdapters to update data, perhaps because, I'm old-school or probably better put, haven't found a situation where it's applicable. The approach generally loads a bunch of records into memory, perhaps even a number of tables - seems expensive. And when you attempt to update the database, there's a process going on behind the scenes to iterate through each of the rows in each of the tables to assess which records are 'dirty' and therefore which source records to update on back-end. Just seems like a lot of effort but, based on your needs, it might be the way to go.