August 5, 2013 at 1:31 pm
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'
August 5, 2013 at 1:41 pm
Annee (8/5/2013)
I am using SQL server 2005Here 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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 5, 2013 at 1:54 pm
Sure.. thanks for the inputs.. what would the dynamic sql syntax look like??
Appreciate any help with the syntax to accomplish this.. thanks!
August 5, 2013 at 2:22 pm
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2013 at 8:51 am
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
August 6, 2013 at 9:17 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2013 at 9:36 am
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.
August 6, 2013 at 9:53 am
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 http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
August 6, 2013 at 10:42 am
Before you decide to go and intentionally write code that's vulnerable to SQL injection, maybe take a read through these.
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
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply