Protect a dynamically generated SQL query from SQL injections. C#

Question

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?

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
– cdhowie, Commented Apr 20, 2018 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
– Christopher, Commented Apr 20, 2018 at 20:11

Sergey Kalinichenko · Accepted Answer · 2018-04-20 22:32:23Z

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.

edited Apr 20, 2018 at 22:32

answered Apr 20, 2018 at 20:27

Sergey Kalinichenko

729k85 gold badges1.2k silver badges1.6k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Bill Karwin Over a year ago

What if the key names in data are values that exploit SQL injection?

Sergey Kalinichenko Over a year ago

@BillKarwin Right, I made an assumption here that keys are pre-validated. I edited the answer to state that explicitly. Thank you!

Bill Karwin Over a year ago

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.

Sergey Kalinichenko Over a year ago

@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.

Collectives™ on Stack Overflow

Protect a dynamically generated SQL query from SQL injections. C#

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related