How can I safely allow a user to define the name of a new column in SQL?

Viewed 44

I'm building a very basic inventory management application using Xamarin Forms to construct the UI and MySQL to build and maintain the database. I'd like the user to be able to define their own properties for each item to allow flexibility in what the application can tell the user about inventory. My plan was to have things that all items of one type share (ex. all cameras with model number 0000 are 5mp cameras) be inserted into the the table of all items as a new column that will just be NULL for items that don't have that particular attribute (ex. refrigerators don't have a resolution).

The following is how I would accomplish the task if I cared nothing for the threat of SQL injection.

    //this Dictionary would be populated by data pulled from user inputs.        
    IDictionary<string, object> userProps = new Dictionary<string, object>;

    MySQLCommand dBCommand = new MySQLCommand();

    foreach(string prop in userProps.Keys)
    {
       dbCommand.CommandText = dbCommand.CommandText + "ALTER TABLE items ADD COLUMN " + prop + " " + userProps[prop].GetType().ToString() + ";";  
    }

Then of course open a connection and execute the statement. I know I can replace userProps[prop].GetType().ToString() with an instance of the SQLParameter class but it does not appear to have a datatype suitable to a column or table name (which becomes important for properties with variable values like MAC addresses). I'm hoping I'm a fool and there is a simple way to accomplish this without exposing the application to SQL injection. If not I am open to suggestions for a more intensive solution. Much gratitude to anyone who takes the time to read this and render assistance.

0 Answers
Related