ccravenbartle
asked on
Dynamic Select columns using SQL Server Stored Procedure
I have to write a single stored procedure that returns data to a client application that allows users to build dynamic queries based on a variable mix of selection parameters. The application presents the user with a check list of field names and the user ticks the fields which are to be included in the report, e.g.
[ ] Product Code
[ ] Product Description
[ ] Weight
[ ] Volume
[ ] Standard Price
[ ] Supplier
etc ..
(There are upwards of 100 different fields that the user may choose.)
Somehow I need to be able to pass the list of column names to a stored procedure and build the sql select statement within the stored procedure based on which columns have been selected by the user. I also have to do it in a way that does not make the stored procedure wide open to an injection attack.
What's the best way of doing this?
[ ] Product Code
[ ] Product Description
[ ] Weight
[ ] Volume
[ ] Standard Price
[ ] Supplier
etc ..
(There are upwards of 100 different fields that the user may choose.)
Somehow I need to be able to pass the list of column names to a stored procedure and build the sql select statement within the stored procedure based on which columns have been selected by the user. I also have to do it in a way that does not make the stored procedure wide open to an injection attack.
What's the best way of doing this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
that validation you have to put in the UI. parsing the columns and then using them will be costly. if you are asking the user to pass the values in a text box then it is dangarous, if you are using a list box and constructing the string in the application then there is no way the user will send some melicius code.
in situation like 'select ' + @abc + ' from tablename' we can not assure that the @abc will not have any non related code.
we did tried to find the words that need to be ignored in one of our applications, it will ignore the words delete,insert,update if they come from the user. that stoped us from useing the fieldnames like updatedDate,DateInsert, DeletedInformation etc.
so, use the UI to do the validatation.
in situation like 'select ' + @abc + ' from tablename' we can not assure that the @abc will not have any non related code.
we did tried to find the words that need to be ignored in one of our applications, it will ignore the words delete,insert,update if they come from the user. that stoped us from useing the fieldnames like updatedDate,DateInsert, DeletedInformation etc.
so, use the UI to do the validatation.
ASKER
Uday - thank you for your solution and for your additional comments.
ASKER
Before I close the question can you advise how I could get the stored procedure to parse the parameter string to check that each field in the string exists as a column in the table before executing the query. That way I can ensure that no rubbish or malicious code gets built into the select script.