1

For example, I have some sample code below.

updateData(Dictionary<string,string> data){
    string strTemp = string.Empty;
    foreach(KeyValuePair<string, string> values in data){
        strTemp = values.Key + "='" values.Value + "',";
    }
   string query = "update tablename set " + strTemp + "modDate = sysdate"
 //execute query against oracle db
}

Without knowing what is in the dictionary, data, and knowing very little about the rest of the application, what would be the best way to protect from SQL injections? Can I dynamically parameterize both the column names and the values?

Neema
  • 13
  • 5
  • just pass a parameter in your sql query , that's all – Software Dev Apr 20 '18 at 20:08
  • Dynamically parameterize the values. Whitelist the keys; they can't be parameterized, and you probably don't really want to allow them to touch all of the columns anyway. – cdhowie Apr 20 '18 at 20:09
  • You can indeed dynmaically build a Parametizeable Query. The other approach - Prepared Statements - had a limitation that prevents making this savely. – Christopher Apr 20 '18 at 20:11

1 Answers1

1

The "bulletproof" way of avoiding SQL injection is parameterizing your query:

UpdateData(Dictionary<string,string> data) {
    var assignments = string.Join(
        ", "
    ,   data.Keys.Select(key => $"{0}=:{0}", key)
    );
    string query = $"update tablename set {assignments}, modDate = sysdate"
    //execute query against oracle db
    Command cmd = ...
    foreach (var kvp in data) {
        cmd.Parameters.AddWithValue(kvp.Key, kvp.Value);
    }
    ...
}

First, the assignments string composed of FieldName=:FieldName parts is added into UPDATE statement. After that each item from the data dictionary is bound to its corresponding parameter using AddWithValue method on SQL parameter collection.

Note: The above assumes that you have full control over keys in the data dictionary, because they must match column names in your table. If the entire dictionary comes from external input, it is essential that you validate keys against your table's metadata before constructing the query to prevent an attempt to exploit key names for SQL injection.

Sergey Kalinichenko
  • 714,442
  • 84
  • 1,110
  • 1,523
  • What if the key names in `data` are values that exploit SQL injection? – Bill Karwin Apr 20 '18 at 21:03
  • 1
    @BillKarwin Right, I made an assumption here that keys are pre-validated. I edited the answer to state that explicitly. Thank you! – Sergey Kalinichenko Apr 20 '18 at 22:33
  • Thanks for the edit. Although it would be unlikely (and under your control), it's legal to have a column named `; drop table users --`. If you aren't sure you can avoid having that column name, then your query should delimit the column names. – Bill Karwin Apr 20 '18 at 22:54
  • @BillKarwin You are right. Delimiting names in Oracle brings an extra challenge, though, because as soon as you quote an identifier it becomes case-sensitive. – Sergey Kalinichenko Apr 20 '18 at 22:57