in reply to Detect SQL injection
I have this situation in which I have to create a table, but the user defines the column names and column definitions.
If this is being done via an http request, I would structure the cgi query parameters for table columns as a list of related pairs: one member of each pair would be the "column name" (which needs to match a very simple regex) and the other would be the data type (which needs to come from a closed set of known alternatives).
How you structure the user's input form to support that is up to you -- the cleanest approach would probably involve some javascripting that allows the user to accumulate one or more column definitions, each of which adds two related parameters to the request (a type-in for the column name, a pull-down menu for the data type) -- e.g. the query might contain params sort of like this:
(You don't have to support all possible variations and sizes of data types -- a minimal set with default sizes ought to suffice.)...colname_1=foo&coltype_1=int&colname_2=bar&coltype_2=varchar...
When it comes to structuring the server-side activity to handle the request, of course, you can't assume that every request will come from your nifty input form page, but all you have to do is grep out the paired parameters, make sure that you have a usable name and a known data type in each pair, and assemble your SQL "create table ..." statement accordingly.
That all assumes that the user requirements for creating tables do not involve "special features" like uniqueness constraints on one or more given columns, foreign-key relations to other tables, particular character-set and/or collation specs on varchars, and so on. (To some extent, some of these features could be included as options in a more elaborate input form.)
In any case, by structuring the input task so that each user-specified column involves two separate user inputs (name and data type), I think you'll be making things easier and safer overall, for the user and for the server.
(updated 2nd paragraph to be more coherent)
|
|---|