May 2, 2009 at 10:42 am
Quick question - do you know of any other way without using Exec(@SQLText)
basically I'm trying to setup a DEV / UAT and Live environment on SQL server 2005 where each environment has 5 distinct databases so I want
Development on DEV to transfer to the LIVE db structure without any coding changes (using SQL Compare V8)
Select*
FromDEV_Data.dbo.Data_Customer join
DEV_Process.dbo.Customer_Extract on
Cust_PK=Cust_FK
Select*
FromLive_Data.dbo.Data_Customer join
Live_Process.dbo.Customer_Extract on
Cust_PK=Cust_FK
I thought about setting up synonyms but they seem to need to be table specific and it would take too long to set up all the tables
Any help would be appreciated
May 2, 2009 at 11:25 am
David Burrows (8/9/2006)
EXECUTE('select*from['+@dbname+'].dbo.sysfiles')to stop sql injection attack
Did you try QUOTENAME 😉
DECLARE @dbname NVARCHAR(256)
SELECT @dbname = 'tempdb]; DROP DATABASE master'
PRINT ('select * from [' + @dbname + '].dbo.sysfiles')
PRINT ('select * from ' + QUOTENAME(@dbname) + '.dbo.sysfiles')
Greets
Flo
May 3, 2009 at 2:23 pm
Did you try QUOTENAME
Nah, wanted to save the extra cpu cycles 😉
Besides, obviates the annoying question of what is QUOTENAME 😀
Far away is close at hand in the images of elsewhere.
Anon.
May 3, 2009 at 5:18 pm
Another way to go would be creating system procedure in master database.
Then you just call that procedure from any of databases and get results relevant to that particular database.
Here is an example:
USE MASTER
GO
IF Object_ID('dbo.SP_GeneralQuery') IS NOT NULL
DROP PROCEDURE dbo.SP_GeneralQuery
GO
--Makes all objects created after this as system ones
EXEC master.dbo.sp_MS_upd_sysobj_category 1
GO
CREATE PROCEDURE dbo.SP_GeneralQuery
AS
select * from dbo.sysfiles
GO
EXEC master.dbo.sp_MS_upd_sysobj_category 2
GO
USE msdb
GO
exec dbo.SP_GeneralQuery
_____________
Code for TallyGenerator
May 3, 2009 at 5:50 pm
Actually, there's not necessarily any danger of an Injection exploit yet. It all depends where @dbname cam from and whose authority is being used to execute the EXECUTE command.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 3, 2009 at 6:11 pm
QUOTENAME is probably safe (Microsoft recommends it but AFAIK has never affirmed that it is 100% safe), but "
PRINT ('select * from [' + @dbname + '].dbo.sysfiles')
Definitely is not. Try passing "master]; ..."
If you truly want to be safe, this is what I would do:
CREATE proc spWhatever( @DBName as NVARCHAR(256) ) AS
Declare @sql as NVarchar(MAX)
Set @sql = ''
SELECT @sql = 'Select * From ' + [name] + '.dbo.sysfiles'
From sys.databases
Where [name] = @DBName
PRINT (@sql)
EXEC (@sql)
Now that's safe.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 3, 2009 at 6:17 pm
Again, it's still not established that there is any SQL Injection attack possibility here. If the EXECUTE is not being given an enhanced security context (or relying on an Application Logon or Certificate), then it doesn't matter. You can't exploit yourself.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
May 5, 2009 at 3:57 am
Here is a very good documentation about SQL injections
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply