CODEWITHSUNDEEP
phpmysql
user5908308
user5908308

Reputation:

mysql: Ignoring INSERT if table and/or field doesn't exist?

Is there an accepted/safe way to ignore an insert statement if either the table or a field in a table doesn't exist?

I'm spending a lot of time trying to filter an array against an 'exclude list' (also an array) and I realized that if the 'exclude list' changes often it could be an maintenance headache. I'm wondering if it would not be easier to process the entire array, create the desired INSERT/UPDATE queries and if the data isn't required (at this time anyway) just ignore the INSERT/UPDATE query. In the future, if the previously ignored data was desired going forward then alter the DB instead of rewriting the exclude list... totally dumb idea??

Clarification the data array we are starting with:

A sample of the array (which comes from third parties and is subject to change) would look like this:

{
            "ItemTypeCode": 0,
            "ItemTypeDescription": "Normal",
            "VendorId": "621eb496-d6d1-4860-af9b-ccae97bf4ef8",
            "PurchaseOrderId": "d10991e0-a3b5",
            "FreightDataId": null,
            "Quantity": 1,
            "Model": "ZZEAGLEUPGR",
            "StockModel": "",
            "AltStockModel": "",
            "AltModel": "",
            "CatalogProdId": "723b8e0b",
            "CustomerSpecificPricings": [],
            "GasSteamUtilityGrids": [],
            "HvacUtilityGrids": []
        }]

where the end user may not need (at this time anyway) the "FreightDataId" or any of the data in "GasSteamUtilityGrids" sub-array.

Upvotes: 0

Views: 139

Answers (1)

trincot
trincot

Reputation: 350477

Don't be tempted to go that way.

If you allow users to specify the table names and columns you are already on a dangerous path. I would strongly advise against that. Protection is primordial.

But if this is how you want to proceed, you should in fact not use a black list (exclude list), but a white list (include list). Only the listed tables and columns may be specified by the user for performing database actions. This means that when you add new tables and/or fields, you are safe, even if you forgot to adapt the white list.

Don't try to do without such a list. Protection is much more important than the benefit of not having to maintain such a (white) list.

Upvotes: 0

Related Questions