C# SQLite Insert function with dynamically parameters

Question

Alright, Maybe the title is a little bit weird, but i will try to explain it.

I'm working on an C# application with an SQLite database file. I've got an DB class where all my functions for communication with the db will be.

I've got an Insert function there with this code ( note, this is not made by me ):

public bool Insert(String tableName, Dictionary<String, String> data)
        {
            String columns = "";
            String values = "";
            Boolean returnCode = true;
            foreach (KeyValuePair<String, String> val in data)
            {
                columns += String.Format(" {0},", val.Key.ToString());
                values += String.Format(" '{0}',", val.Value);
            }
            columns = columns.Substring(0, columns.Length - 1);
            values = values.Substring(0, values.Length - 1);
            try
            {
                this.ExecuteNonQuery(String.Format("insert into {0}({1}) values({2});", tableName, columns, values));
            }
            catch (Exception fail)
            {
                MessageBox.Show(fail.Message);
                returnCode = false;
            }
            return returnCode;
        }

It all looks good. But i want to prevent some SQLInjection with parameterized queries. But the thing is, that the query never will have the same length. So how will I handle this?

I mean: I want to have this single function for an table with 3 columns, and for an table with 8 columns. All dynamically. Does anybody have any idea on how to do that ( in an nice / right way )?

@Steve, some may will be int, but i can convert them inside C# if necessary... — Mathlight
– Mathlight, Commented Jan 28, 2014 at 23:33
If memory serves, SQLite allows you to provide an array containing the values you want to parameterize. What prevents you from generating this array dynamically, just like your are doing with the column designators here? — Robert Harvey
– Robert Harvey, Commented Jan 28, 2014 at 23:35
@RobertHarvey, i realy didn't knew i could do that. I only thought that i could hard code the parameterize code... Can you provide me with some little code that will help me getting started? — Mathlight
– Mathlight, Commented Jan 28, 2014 at 23:39

Atiris · Accepted Answer · 2014-01-29 14:02:32Z

I rewrite your insert function into InsertSecure method,
and now you can insert all values using parameters.

I added some comment to code for better understanding of how to adapt your procedures.
Then, you can call it this way:

public void HowToCall()
{
    // I assume that "columnA" and "columnB" is set by you and it is safe, 
    // unsafe are values "valueA" and "valueB"
    Dictionary<string, string> data = new Dictionary<string, string>()
        { { "columnA", "valueA" }, { "columnB", "valueB" } };
    // then call new method
    InsertSecure("table_name", data);
}

InsertSecure content:

public bool InsertSecure(String tableName, Dictionary<String, String> data)
{
    // table name can not contains space
    if (tableName.Contains(' ')) { return false; }
    String columns = "";
    String values = "";
    Boolean returnCode = true;
    foreach (KeyValuePair<String, String> val in data)
    {
        columns += String.Format(" '{0}',", val.Key.ToString());
        // all values as parameters
        values += String.Format(" @{0},", val.Key.ToString());
    }
    columns = columns.Substring(0, columns.Length - 1);
    values = values.Substring(0, values.Length - 1);
    try
    {
        // setup your connection here
        // (connection is probably set in your original ExecuteNonQuery)
        SQLiteConnection cnn = new SQLiteConnection(); 
        cnn.Open();
        SQLiteCommand cmd = cnn.CreateCommand();
        cmd.CommandType = System.Data.CommandType.Text;
        // prepare insert command based on data
        cmd.CommandText = String.Format("insert into {0} ({1}) values ({2})",
            tableName, columns, values);

        // now your command looks like:
        // insert into table_name (columnA, columnB) values (@columnA, @columnB)
        // next we can set values for any numbers of columns
        // over parameters to prevent SQL injection
        foreach (KeyValuePair<String, String> val in data)
        {
            // safe way to add parameter
            cmd.Parameters.Add(
                new SQLiteParameter("@" + val.Key.ToString(), val.Value));
            // you just added for @columnA parameter value valueA
            // and so for @columnB in this foreach loop
        }
        // execute new insert with parameters
        cmd.ExecuteNonQuery();
        // close connection and set return code to true
        cnn.Close();
        returnCode = true;
    }
    catch (Exception fail)
    {
        MessageBox.Show(fail.Message);
        returnCode = false;
    }
    return returnCode;
}

I thing you can safe insert data with this procedure.

Note: I have no project to test it, if it is correctly writen,
but there is no syntax error and logic look ok. Please, try it and enjoy.

Thank you so verry much! It is working. There was only 1 problem... You need to add single quotes around the colomns. SO that you have this line of code columns += String.Format("'{0}',", val.Key.ToString()); Also the creation of the dictionary isn't quit working ( i tested it with 3 colloms ). So i've made a add for every collomn and value and now that is fully working. Thanks again for the help!
Thanks for testing, I modify new method code based on your note for others.

Collectives™ on Stack Overflow

C# SQLite Insert function with dynamically parameters

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related