How should I handle a table created by the user?
(1) By Fab (fab980435908) on 2020-10-30 15:09:32 [link] [source]
I'm making a tool to manage a database. The table will be created by the user to hold different data depending on what they need to store. When validating what they can input is it best to check the table itself or make an extra metadata table to hold the info.
This is an extremely (potentially) dangerous option to even consider. Depends on many things: What language are you using for the server side? How efficient and skilled are you with security in that language? What DB / RDBMS are you going to be using? Why do you think you need to allow a user to access your DB engine?
(4) By Fab (fab980435908) on 2020-11-03 09:44:31 in reply to 2 [link] [source]
This will all be local for the user with electron. I don't think I need to worry about security as it will only ever effect the one user and they don't use anything online with my app.
Are you allowing your user to specify the columns of a SQL table which you'll store in your database ?
Are you allowing your user to specify the table's name and column names ?
Is there anything important in the database besides that table ? If so, you're opening yourself to numerous problems unless you give your user only very simple control over table and column names. Something like lower case letters only. If you. allow anything else you're going to have to develop some quite complicated code to make sure they're not going to corrupt the database.
You will need similar very strict validation of table contents. Without it, it will be possible, by crafting malicious table contents, to corrupt your database. It is traditional at this point to quote the following:
You mentioned a metadata table. If I understand you right, what you're talking about is simulating a SQL table: you won't create a genuine SQL table in your database which matches user input, you will instead simulate that table, probably using triplets (table, column number, value). This may be the way to go, though depending on how you validate inputs it may still be possible to corrupt your database.