Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Plugging in a variable column name in a stored procedure Expand / Collapse
Author
Message
Posted Monday, August 5, 2013 1:31 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 8:00 AM
Points: 38, 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'

Post #1481062
Posted Monday, August 5, 2013 1:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's 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)
Post #1481066
Posted Monday, August 5, 2013 1:54 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 8:00 AM
Points: 38, 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!
Post #1481073
Posted Monday, August 5, 2013 2:22 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's 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)
Post #1481077
Posted Tuesday, August 6, 2013 8:51 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 8:00 AM
Points: 38, 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

Post #1481382
Posted Tuesday, August 6, 2013 9:17 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's 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)
Post #1481398
Posted Tuesday, August 6, 2013 9:36 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Wednesday, April 2, 2014 8:00 AM
Points: 38, 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.
Post #1481408
Posted Tuesday, August 6, 2013 9:53 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 1:04 PM
Points: 13,086, Visits: 12,553
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 Moden's 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)
Post #1481417
Posted Tuesday, August 6, 2013 10:42 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:19 AM
Points: 40,208, Visits: 36,617
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 2008, MVP
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

Post #1481444
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse