CODEWITHSUNDEEP
mysqldynamic-tables
Ultimate Gobblement
Ultimate Gobblement

Reputation: 1879

What is the best way to create a dynamic data structure off use input?

I wrote some code that creates database tables based on user input. From what I can tell, this seems to be bad form. I'm also not sure if user input is sanitized sufficiently to prevent SQL injection. This is the code I have in Javascript:

  function tableName(dialog_id){
    return settings.table_prefix+parseInt(dialog_id);
  }   
  
  function generateTableSQL(dialog_id, fields){
    function generateColumnSQL(field){
      // take first word then strip non alphanumeric or underscore from field name
      let name = field.name.replace(/ .*/,'').replace(/\W/g, '');
      let type = COLUMN_TYPES[field.type];
      if(! (name && type)){
        return null;
      }else if(name[0].match(/[0-9]/)){
        return null;
      }
      return "`"+name+"` "+type;
    }
    let columns = _.map(fields, generateColumnSQL);
    let isInvalid = _.find(columns, (c) => {c==null});
    if(isInvalid){
      return null;
    }
    return [
      "CREATE TABLE `"+tableName(dialog_id)+"` (\n",
      "`id` INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, \n",
      columns.join(", \n"), 
      ") ENGINE="+DB_ENGINE+" DEFAULT CHARSET=utf8;"
    ].join("\n");
  }

I want to know what the best way to store data with a structure that's generated by a user. I could do something like create a single table then call SQL code to insert a row for each column of data. Something like:

INSERT INTO saved_data (table_name, field, value) VALUES (?, ?, ?)

But then each field would have to be stored as the same datatype. What's the best way to achieve what I am trying to do?

Upvotes: 0

Views: 56

Answers (0)

Related Questions