SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Plugging in a variable column name in a stored procedure


Plugging in a variable column name in a stored procedure

Author
Message
Annee
Annee
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 106
I am using SQL server 2005

Here is the ddl:
CREATE TABLE [dbo].[AgencySupplierPartner](
[ClientName] [nvarchar](15) NOT NULL,
[VendorName] [nvarchar](40) NOT NULL,
[TaxID] [nchar](9) NOT NULL,
[AcceptStandard] [nvarchar](10) NULL,
[AcceptSummary] [nvarchar](10) NULL
CONSTRAINT [PK_AgencySupplierPartner] PRIMARY KEY CLUSTERED
(
[ClientName] ASC,
[TaxID] ASC
)



I would like to write a stored procedure to which I would pass 3 parameters ClientName, VendorName and InvoiceType as parameters.
While ClientName and VendorName are the column names 'InvoiceType' is a variable which could be one of the column names(AcceptStandard or AcceptSummary)

I am trying to put it together as a stored proc(see below), but it doesn't work, probably due to syntax error where I am plugging in @InvoiceType.
Can someone please help me fix it? Thanks!

CREATE PROCEDURE [dbo].[pr_GetVendorInfo]
(
@ClientName nVarChar(15),
@VendorName nVarChar(40),
@InvoiceType nVarChar(10)
)
AS

SELECT COUNT(*) FROM AgencySupplierPartner
WHERE ClientName = @ClientName AND VendorName = @VendorName AND @InvoiceType = 'true'
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25996 Visits: 17528
Annee (8/5/2013)
I am using SQL server 2005

Here is the ddl:
CREATE TABLE [dbo].[AgencySupplierPartner](
[ClientName] [nvarchar](15) NOT NULL,
[VendorName] [nvarchar](40) NOT NULL,
[TaxID] [nchar](9) NOT NULL,
[AcceptStandard] [nvarchar](10) NULL,
[AcceptSummary] [nvarchar](10) NULL
CONSTRAINT [PK_AgencySupplierPartner] PRIMARY KEY CLUSTERED
(
[ClientName] ASC,
[TaxID] ASC
)



I would like to write a stored procedure to which I would pass 3 parameters ClientName, VendorName and InvoiceType as parameters.
While ClientName and VendorName are the column names 'InvoiceType' is a variable which could be one of the column names(AcceptStandard or AcceptSummary)

I am trying to put it together as a stored proc(see below), but it doesn't work, probably due to syntax error where I am plugging in @InvoiceType.
Can someone please help me fix it? Thanks!

CREATE PROCEDURE [dbo].[pr_GetVendorInfo]
(
@ClientName nVarChar(15),
@VendorName nVarChar(40),
@InvoiceType nVarChar(10)
)
AS

SELECT COUNT(*) FROM AgencySupplierPartner
WHERE ClientName = @ClientName AND VendorName = @VendorName AND @InvoiceType = 'true'



You can't switch out columns like that. What this is doing is finding rows where ClientName = [the value passed in] AND VendorName = [the value passed in] AND where the value you passed in for @InvoiceType = 'true'.

You need to use dynamic sql when you need to use the columns passed in. Be very careful here though because you start to open the possibility for sql injection.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Annee
Annee
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 106
Sure.. thanks for the inputs.. what would the dynamic sql syntax look like??
Appreciate any help with the syntax to accomplish this.. thanks!
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25996 Visits: 17528
Annee (8/5/2013)
Sure.. thanks for the inputs.. what would the dynamic sql syntax look like??
Appreciate any help with the syntax to accomplish this.. thanks!


Something like this.


CREATE PROCEDURE [dbo].[pr_GetVendorInfo]
(
@ClientName nVarChar(15),
@VendorName nVarChar(40),
@InvoiceType nVarChar(10)
)
AS

declare @SQL nvarchar(2000)

set @SQL = 'SELECT COUNT(*) FROM AgencySupplierPartner '
+ ' WHERE ClientName = @ClientName AND VendorName = @VendorName '

if @InvoiceType = 'AcceptStandard'
set @SQL = @SQL + 'AND AcceptStandard = ''true'''

if @InvoiceType = 'AcceptSummary'
set @SQL = @SQL + 'AND AcceptSummary = ''true'''

print @SQL

--EXEC sp_executesql @SQL, N'@ClientName nVarChar(15), @VendorName nVarChar(40)', @ClientName = @ClientName, @VendorName = @VendorName




You may ask yourself, "Why not just handle the @InvoiceType as part of the dynamic string? This seems like it would be a good idea but...you would then be introducing sql injection vulnerability because you would end up executing the parameter and that is what you need to avoid. Handling this with 2 separate statements means there is no sql injection vulnerability here.

Notice also that I commented out the exec statement. Make sure the code you are generating is actually want you want before executing any dynamic sql.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Annee
Annee
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 106
Thanks for the quick response and the solution, had to modify it slightly as below:


CREATE PROCEDURE [dbo].[pr_GetVendorInfo]
(
@ClientName nVarChar(15),
@VendorName nVarChar(40),
@InvoiceType nVarChar(30)
)
AS

declare @SQL nvarchar(2000)

set @SQL = 'SELECT COUNT(*) FROM AgencySupplierPartner '
+ ' WHERE ClientName = '''+@ClientName+''' AND VendorName = '''+@VendorName +''''

if @InvoiceType = 'AcceptStandard'
set @SQL = @SQL + ' AND AcceptStandard = ''true'''

if @InvoiceType = 'AcceptSummary'
set @SQL = @SQL + ' AND AcceptSummary = ''true'''

print @SQL
exec sp_executesql @SQL


Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25996 Visits: 17528
Annee (8/6/2013)
Thanks for the quick response and the solution, had to modify it slightly as below:


CREATE PROCEDURE [dbo].[pr_GetVendorInfo]
(
@ClientName nVarChar(15),
@VendorName nVarChar(40),
@InvoiceType nVarChar(30)
)
AS

declare @SQL nvarchar(2000)

set @SQL = 'SELECT COUNT(*) FROM AgencySupplierPartner '
+ ' WHERE ClientName = '''+@ClientName+''' AND VendorName = '''+@VendorName +''''

if @InvoiceType = 'AcceptStandard'
set @SQL = @SQL + ' AND AcceptStandard = ''true'''

if @InvoiceType = 'AcceptSummary'
set @SQL = @SQL + ' AND AcceptSummary = ''true'''

print @SQL
exec sp_executesql @SQL



You need to look closely at the dynamic sql I wrote. You removed the parameterization and this is wide open to sql injection attack.

Consider what would happen if somebody passed in for @VendorName

''; drop table AgencySupplierPartner;--



NEVEN NEVER NEVER execute a dynamic sql string with parameters unless the dynamic is parameterized.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Annee
Annee
Valued Member
Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)Valued Member (68 reputation)

Group: General Forum Members
Points: 68 Visits: 106
But the query you wrote is not working because the output sql looks something like:

select count (*) from table where clientName = @clientName....

which is actually supposed to be

select count (*) from table where clientName = 'xyz'....

--And then again, in this scenario, the parameters like clientName and vendorName are not the direct user inputs but they are extracted from an XmlDocument which is automatically generated by another applic where there is never a chance for such a sql injection..

Appreciate your suggestion, but I am not able to make ur query work without those paranthesis.
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25996 Visits: 17528
Annee (8/6/2013)
But the query you wrote is not working because the output sql looks something like:

select count (*) from table where clientName = @clientName....

which is actually supposed to be

select count (*) from table where clientName = 'xyz'....

--And then again, in this scenario, the parameters like clientName and vendorName are not the direct user inputs but they are extracted from an XmlDocument which is automatically generated by another applic where there is never a chance for such a sql injection..

Appreciate your suggestion, but I am not able to make ur query work without those paranthesis.


The reason that the output looks like that is because it is parameterized. That actual string still has reference to the parameters. I suspect you didn't actually execute the query, you only looked at what the string looks like. Of course the final choice is yours to make but just because today the only way to run that is from an xml doc doesn't mean anything. The other application could do something funky or the rules may change. The point I am making is to protect yourself by writing safe code. When your code allows it, there is always a chance. ;-)

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
GilaMonster
GilaMonster
SSC Guru
SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)SSC Guru (87K reputation)

Group: General Forum Members
Points: 87009 Visits: 45267
Before you decide to go and intentionally write code that's vulnerable to SQL injection, maybe take a read through these.
http://www.computerworld.com/s/article/9241084/SQL_flaws_remain_an_Achilles_heel_for_IT_security_groups
http://www.troyhunt.com/2013/07/everything-you-wanted-to-know-about-sql.html

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search