Never create an SQL command by concatenating strings together!.
As you have discovered single quotes can break a statement. And that is the lucky version that your program throws an error.
IF someone should craft the values going in then they can "short-circuit" the command and then start running their own.
This was identified as a major vulnerability 20 years ago and is still one of the top 10 ways websites are hacked
The way to avoid this is to use a
Parameterized Query. Your original query will have placeholder variables put inline, and then parameters are added to the command
The nice thing about using parameters is you do not need to worry about the quotes (or other identifiers used (such as #date# in some versions of Access). You do need to be careful as the placeholders will be replaced with SQL data typing based on the values being put in; so if you have an INT inside of a textbox it will be formatted as a string; so make sure the values going in are properly typed.
Typically it would look something like this:
SqlCommand cmd = new SqlCommand("UPDATE table SET ColumnValue = @NewValue WHERE TableID = @TableID", _connection);
cmd.Parameters.AddWithValue("@NewValue", somevalue);
cmd.Parameters.AddWithValue("@TableID", indexvalue);
Your case is a little different,as you are passing the CommandText to some sort of helper.
So what we will need to do is to build a collection of some sort that the values can be <string,object> or <object,object>. So are new query will look something like this
var Query = "UPDATE temp SET HIBLCK14= @HIBLCK14,HICOND = @HICOND,HIATLEG = @HIATLEG,[HIATT#] = @HIATT,HIATNM = @HIATNM,HIOTHLEG = @HIOTHLEG,[HIOTH#] = @HIOTH,HIDOSF = @HIDOSF,HIDOST = @HIDOST,HIBLCK19 = @HIBLCK19,HIOCCD = @HIOCCD WHERE (HILOCX = @HILOCX) AND (HISEQ = @HISEQ) AND (HIDOS = @HIDOS);";
And then we will add in the values using an OrderedDictionary (does not need to be this way, could be any type of key& value object)
OrderedDictionary params = new OrderedDictionary();
params.add("@HIBLCK14", _billingDetails.Qual);
params.add("@HICOND", CondRelate);
params.add("@HIATLEG", billingDetails.AttendingLegacy);
params.add("@HIATT", _billingDetails.AttendingPhys);
params.add("@HIATNM", _billingDetails.AttendingPhysName);
params.add("@HIOTHLEG", _billingDetails.LOCATOR17A1);
params.add("@HIOTH", _billingDetails.LOCATOR17B);
params.add("@HIDOSF", _billingDetails.DOSFrom);
params.add("@HIDOST", ToDOS);
params.add("@HIBLCK19", _billingDetails.AdditionalClaimInformation);
params.add("@HIOCCD", _billingDetails.DateOfOccur);
params.add("@HILOCX", Locix);
params.add("@HISEQ", Sequence);
params.add("@HIDOS", DateOfService);And then is the last bit, adding in a new method which accepts the collection of parameters and can add them to the command.
public bool ExecNonQuery(string Query, OrderedDictionary QueryValues) {
try {
using (SqlCommand cmd = new SqlCommand(Query, _conn)) {
foreach (DictionaryEntry entry in QueryValues) {
cmd.Parameters.AddWithValue(entry.Key, entry.Value);
}
if (_conn.State != ConnectionState.Open) { _conn.Open(); }
cmd.ExecuteNonQuery();
return true;
}
}
catch (Exception) { throw; }
finally { _conn.Close(); }
}
NOTES:
- While this is functional, it is not my preferred method as it limits the parameter functionality
- Some of your column names contained special characters. Those should be escaped.
I am not on an IDE equipped machine, so there may be syntax or other errors. The principal is sound and if it does not work there are no refunds.
Submit an article or tip