Last post Jul 01, 2014 03:53 PM by rds80
Jul 01, 2014 10:46 AM|rds80|LINK
I have a query that I'm developing on the fly depending on which table selects from a dropdown list. The issue with the query is that it's prone to SQL injection. However, since I am developing the query on the fly, I'm not sure how I can create a parameterized
query. Here is the c# code:
public void InsertAuditQuery(IDictionary<string, string> NameValuePairWhere, string DatabaseName, string TableName, string UserAction, bool DeleteAll)
Database SqlDb = DatabaseFactory.CreateDatabase(DatabaseName);
StringBuilder Query = new StringBuilder();
Query.Append("INSERT INTO " + TableName + "_AUDIT (");
string ColumnNames = string.Empty;
FilteredFieldList ca = new FilteredFieldList();
foreach (FieldProperty fa in ca)
string cn = fa.FieldName.Trim();
ColumnNames = string.Concat("[", cn, "]", ",", ColumnNames);
Query.Append(" SELECT TOP 1 ");
Query.Append("'" + UserName + "','" + UserAction + "'");
Query.Append(" FROM " + TableName);
FilteredFieldList pk = new FilteredFieldList(true);
Query.Append(" ORDER BY ");
foreach (FieldProperty fp in pk)
Query.Append(string.Concat(fp.FieldName, ", "));
if (Query.ToString().EndsWith(", "))
Query.Remove(Query.Length -2, 2);
Query.Append(" WHERE ");
foreach (KeyValuePair<string, string> NameValue in NameValuePairWhere)
Query.Append(NameValue.Key + " = '" + NameValue.Value + "' AND ");
if (Query.ToString().EndsWith(" AND "))
Query.Remove(Query.Length - 4, 4);
ObjCmd = SqlDb.GetSqlStringCommand(Query.ToString());
iResult = SqlDb.ExecuteNonQuery(ObjCmd);
What I tried to do was use parameter variables, but when doing SqlDb.AddInParameter one of the parameters required is the data type. And not all of the parameters are of a single type such as string. And there is no overload method for AddInParameter that
doesn't take DBType as a parameter.
Jul 01, 2014 01:16 PM|PatriceSc|LINK
Question each and every string value you'll insert into your final SQL string. Does it come from the user directly or indirectly? (for example that could be changed by updating the HTML markup and posting again or whatever). I f yes, as it seems the string
is constructed from well identified pieces, before inserting a table name or column name inside this SQL Statement you could check this name against a white list of allowed table names, column names, action names...
For the criteria even though you construct dynamically the query you could still use parameters. For example it could be something such as :
Query.Append(ThrowIfNotAColumnName(NameValue.Key)+"=@p"+i.ToString()+" AND "); i being a numeric variable under your control so no risk here. Then you'll add the parameter value as you would have done with an hardcoded SQL query found directly in the code. ThrowIf...
will throw if the column name (that perhaps come from an http post?) is not one of the column name you let the user to select from (could be the same source you used for showing a drop down or whatever).
Fot the type issue, I assume that for each "key" (column name?), you should know what is the type of value you expect to have for this column?
Jul 01, 2014 03:53 PM|rds80|LINK
Thanks for your reply Patrice. Even if I change everything to .ToString() wouldn't there still be an error because the column in the table is of a different data type (i.e., int)?
Just to clarify all of the NameValue.Key fields are columns in a table. I'm passing the NameValue dictionary from a gridview. And the gridview is being populated from a table. Sorry for not clarifying that before.