Solved

Dynamic Select columns using SQL Server Stored Procedure

Posted on 2009-04-02
4
419 Views
Last Modified: 2012-05-06
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?


0
Comment
Question by:ccravenbartle
  • 2
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
udayakumarlm earned 500 total points
ID: 24048436
you can use the code to start with. the program has to call the procedure with comma separated values as parameter.
Procedurename '*' -- for all fields

Procedurename ' Product Code, Product Description, Weight, Volume, Standard Price, Supplier'  --for seleccted fields
create Proc Procedurename

@abc varchar(2000)

as

declare @str Nvarchar(1000)

set @str = 'select ' + @abc + ' from tablename'

print @str

Execute Sp_ExecuteSql @Str

Open in new window

0
 

Author Comment

by:ccravenbartle
ID: 24049841
Uday: Thank you for your response - I've tested it and it does work.  The points are yours.  

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.
0
 
LVL 12

Expert Comment

by:udayakumarlm
ID: 24050917
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.
0
 

Author Closing Comment

by:ccravenbartle
ID: 31565728
Uday - thank you for your solution and for your additional comments.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL query to summarize items per month 5 54
SQL join help to a thrid table 51 76
mysql joining from the same table 6 37
Extract string portion 2 16
PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Video by: Mark
This lesson goes over how to construct ordered and unordered lists and how to create hyperlinks.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now