November 21, 2013 at 9:49 pm
Hi Professionals.
I have been stuck on this for days and I cannot figure it out. I am passing in the column name INSTALLATIONS as a variable but it wont work
here is my code
ALTER procedure [dbo].[totalinstallations]
@installations nvarchar(1000)
AS
BEGIN
declare @sql nvarchar(1000)
SET @sql = 'SELECT softwaremanufacturer,productname,productversion,
sum(distinct case when isnumeric(@installations)=1 then convert(int, @installations) else 0 end) as FirstTotal,
Licensable
FROM newtable
where productname in (''Office Proof 2010'',''Project Standard 2010'')
GROUP BY softwaremanufacturer,productname,productversion,
Licensable
order by FirstTotal desc'
print @sql
END
go
the print just shows
SELECT softwaremanufacturer,productname,productversion,
sum(distinct case when isnumeric(@installations)=1 then convert(int, @installations) else 0 end) as FirstTotal,
Licensable
FROM newtable
where productname in ('Office Proof 2010','Project Standard 2010')
GROUP BY softwaremanufacturer,productname,productversion,
Licensable
order by FirstTotal desc
if I take the @ sign away then the query is fine. any ideas what I am doing wrong
exec totalinstallations 'installations'
November 21, 2013 at 10:09 pm
You need to concat your variable in to your string...
ALTER procedure [dbo].[totalinstallations]
@installations nvarchar(1000)
AS
BEGIN
declare @sql nvarchar(1000)
SET @sql = 'SELECT softwaremanufacturer,productname,productversion,
sum(distinct case when isnumeric(' + @installations + ')=1 then convert(int, ' + @installations + ') else 0 end) as FirstTotal,
Licensable
FROM newtable
where productname in (''Office Proof 2010'',''Project Standard 2010'')
GROUP BY softwaremanufacturer,productname,productversion,
Licensable
order by FirstTotal desc'
print @sql
END
November 21, 2013 at 10:19 pm
Thanks so much Grasshopper that is good.
One problem I have introduced now is when I try to execute it with EXEC master..xp_cmdshell @sql command like so
ALTER procedure [dbo].[totalinstallations]
@installations nvarchar(1000)
AS
BEGIN
declare @sql nvarchar(1000)
SET @sql = 'SELECT softwaremanufacturer,productname,productversion,
sum(distinct case when isnumeric(' + @installations + ')=1 then convert(int, ' + @installations + ') else 0 end) as FirstTotal,
Licensable
FROM newtable
where productname in (''Office Proof 2010'',''Project Standard 2010'')
GROUP BY softwaremanufacturer,productname,productversion,
Licensable
order by FirstTotal desc'
print @sql
EXEC master..xp_cmdshell @sql
END
go
exec totalinstallations 'installations'
I get an error saying
'SELECT' is not recognized as an internal or external command,
operable program or batch file.
NULL
November 21, 2013 at 10:33 pm
xp_cmdshell is used to run command shell commands like batch files, executables etc which is probably not what you want. You should simply call
EXEC (@sql)
November 21, 2013 at 10:38 pm
thanks again grasshopper
I have done that and it works perfect
your a star much appreciated
November 21, 2013 at 11:25 pm
burfos (11/21/2013)
You need to concat your variable in to your string...
ALTER procedure [dbo].[totalinstallations]
@installations nvarchar(1000)
AS
BEGIN
declare @sql nvarchar(1000)
SET @sql = 'SELECT softwaremanufacturer,productname,productversion,
sum(distinct case when isnumeric(' + @installations + ')=1 then convert(int, ' + @installations + ') else 0 end) as FirstTotal,
Licensable
FROM newtable
where productname in (''Office Proof 2010'',''Project Standard 2010'')
GROUP BY softwaremanufacturer,productname,productversion,
Licensable
order by FirstTotal desc'
print @sql
END
Gosh, No! Such code is highly suseptible to SQL Injection
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2013 at 11:26 pm
Oracle765 (11/21/2013)
thanks again grasshopperI have done that and it works perfect
your a star much appreciated
DO NOT IMPLEMENT THAT DYNAMIC CODE! It probably doesn't need to be dynamic SQL and it doesn't need to be subject to SQL Injection.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 21, 2013 at 11:40 pm
Yes very very good point. Apologies for not pointing that out. Just answering the problem that was asked. There is a previous thread with the actual original question of what is trying to be attempted.
November 21, 2013 at 11:45 pm
Let's try to science this out in a safer manner. What will you pass in the @installations variable?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2013 at 6:44 am
Jeff Moden (11/21/2013)
Oracle765 (11/21/2013)
thanks again grasshopperI have done that and it works perfect
your a star much appreciated
DO NOT IMPLEMENT THAT DYNAMIC CODE! It probably doesn't need to be dynamic SQL and it doesn't need to be subject to SQL Injection.
Can you explain the risk a bit more ?
Does the risk vary depending on whether a website is involved or not ?
Is there safe dynamic code ?
November 22, 2013 at 7:16 am
Jeff Moden (11/21/2013)
@Oracle765,Let's try to science this out in a safer manner. What will you pass in the @installations variable?
Heck! Explain the query first.
As far as I can tell the sum distinct will return the value of @installations (if numeric) or zero.
Which would be that same as changing @installations to zero, if not numeric, first and then selecting @installations in the query.
Far away is close at hand in the images of elsewhere.
Anon.
November 22, 2013 at 4:47 pm
homebrew01 (11/22/2013)
Can you explain the risk a bit more ?
Yes. Any dynamic SQL that makes use of concatenation or simple replacement of the parameters is subject to a hack attack using SQL Injection. SQL Injection is pretty much what its name says. It's a method of using a couple of well place quotes, a semi-colon, and some malicious code to drop a table or deliver a payload to the attacker.
Does the risk vary depending on whether a website is involved or not ?
Some will say that the risk is less if a website is not involved. I say that's only true for "Code for a DBA" where only DBAs will be using it. Any dynamic code that has not been made "injection proof" that can be used by non-DBAs can be attacked internally or externally. That's one of the many reasons why it's a good idea to have a DBA-only database. Of course, if someone get's into your box as SA, then they won't need to use SQL Injection. They can do anything they want. That's a part of why preventing SQL Injection is so important... it's one of the first and sometimes last steps to gaining access to your system with "SA" privs.
Is there safe dynamic code ?
Oh, yes. Gail Shaw has an excellent example on her "SQL in the Wild" website concerning a phenomenon called "Catch All Queries". Here's the link for that wonderful article…
[font="Arial Black"]http://sqlinthewild.co.za/index.php/2009/03/19/catch-all-queries/[/font][/url]
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2013 at 9:04 am
November 24, 2013 at 2:26 pm
hi there.
no I will not be using the internet just our local server.
Here is my full code fyi
USE [TestData]
GO
/****** Object: StoredProcedure [dbo].[totalinstallations] Script Date: 11/21/2013 09:58:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[totalinstallations]
@importedquery nvarchar(1000),
@installations nvarchar(1000)
AS
BEGIN
declare @sql nvarchar(1000)
SET @sql = 'SELECT ' + @importedquery + ',
sum(distinct case when isnumeric(' + @installations + ')=1 then convert(int, ' + @installations + ') else 0 end) as FirstTotal,
Licensable
FROM newtable
where productname in (''Office Proof 2010'',''Project Standard 2010'')
GROUP BY ' + @importedquery + ',
Licensable
order by FirstTotal desc'
--print @sql
EXEC (@sql)
END
go
--exec totalinstallations 'softwaremanufacturer,productname','installations'
results with totals and duplicate columns eliminated.
softwaremanufacturerproductnameFirstTotalLicensable
MicrosoftOffice Proof 2010149Non Licensable
MicrosoftProject Standard 201040Licensable
November 24, 2013 at 3:39 pm
Why in the world would ever think about using dynamic SQL? That is just horrible.
Stay far away from the dynamic SQL. That would never pass one of my code reviews.
Andrew SQLDBA
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply