InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comFri, 24 May 2013 02:10:19 GMT20http://www.sqlservercentral.com/Forums/Topic1387468-3077-1.aspxThanks for the input guys! :-D@Eugene[quote]That is not dynamic SQL...[/quote]I know. I was just demonstrating Jeffs suggestion of checking for a valid object and stopping if it isn't valid.[quote]So far as SQL injection goes, your code is wide open for it the way @DBName is currently used. The best way around it is to check that the content of @DBName actually exists in sys.databases or throw a fatal error.[/quote]Thanks for the injection example!! Time to test some of my apps and SPs! :-PThu, 22 Nov 2012 07:35:48 GMTDennis Posthttp://www.sqlservercentral.com/Forums/Topic1387468-3077-1.aspx[quote][b]D.Post (11/22/2012)[/b][hr]Ok, so we have to use dynamic queries for object names.Make sure to check the object against the sys views.[code="sql"]DECLARE @DBName NVarchar(50)SET @DBName = 'Master1'IF NOT EXISTS (SELECT * FROM sys.Databases WHERE Name = @DBName)BEGIN PRINT 'Database : ' + @DBName + ' does not exist' RETURNEND[/code]I'm assuming here that no injection is possible using PRINT.People could inject another valid object name though.Could cause a bit of a hassle when using DELETE or TRUNCATE.[/quote]That is not dynamic SQL...But if you build an SQL string like that and execute it using sp_executesql or EXEC() command, you can inject terrible code into it. Here is your code implemented as Dynamic SQL:[code="sql"]DECLARE @DBName NVarchar(50)SET @DBName = 'Master1'IF NOT EXISTS (SELECT * FROM sys.Databases WHERE Name = @DBName)BEGIN -- example of dynamic sql DECLARE @sql NVARCHAR(1000) SET @sql = 'PRINT ''Database : ' + @DBName + ' does not exist''' EXEC (@sql)END[/code]Now let's try to inject something to it (taking @DBName is input parameter): [code="sql"]CREATE TABLE check_injection (i int) GO -- Check for sql injection select * from check_injectiongoDECLARE @DBName NVarchar(50)-- SET @DBName = 'Master1'-- Someone injected this bit:SET @DBName = ''';DROP TABLE check_injection; PRINT ''bb:-)'';--'IF NOT EXISTS (SELECT * FROM sys.Databases WHERE Name = @DBName)BEGIN -- example of dynamic sql select QUOTENAME(@DBName) DECLARE @sql NVARCHAR(1000) SET @sql = 'PRINT ''Database : ' + @DBName + ' does not exist''' EXEC (@sql)ENDselect * from check_injection[/code]With enough rights, you may drop the whole database :-)Thu, 22 Nov 2012 05:21:51 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1387468-3077-1.aspxOk, so we have to use dynamic queries for object names.Make sure to check the object against the sys views.[code="sql"]DECLARE @DBName NVarchar(50)SET @DBName = 'Master1'IF NOT EXISTS (SELECT * FROM sys.Databases WHERE Name = @DBName)BEGIN PRINT 'Database : ' + @DBName + ' does not exist' RETURNEND[/code]I'm assuming here that no injection is possible using PRINT.People could inject another valid object name though.Could cause a bit of a hassle when using DELETE or TRUNCATE.Thu, 22 Nov 2012 01:50:44 GMTDennis Posthttp://www.sqlservercentral.com/Forums/Topic1387468-3077-1.aspx[quote][b]Jeff Moden (11/21/2012)[/b][hr]Not only would it be cool to be able to name object names with a variable but it would also be cool if we could use variables and column names in the same SELECT like we can with UPDATE. Instead of "quirky updates", we could use "not so quirky SELECTs" were we can use an ORDER BY and the whole shebang![/quote]Somewhere, somewhen I saw an article where someone had interviewed you and asked what you thought should be included in the next version of SQL or what your biggest gripes were. Add that one to the list.For the record, yeah that would also be way cool!The only question would be, if CELKO sits on the ISO standards committee would he approve of it? He might consider it a "hillbilly dialect." :hehe:Wed, 21 Nov 2012 23:36:54 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1387468-3077-1.aspx[quote][b]dwain.c (11/21/2012)[/b][hr][quote][b]Jeff Moden (11/21/2012)[/b][hr][quote][b]dwain.c (11/21/2012)[/b][hr]Would this work?[code="sql"]DECLARE @DB VARCHAR(100) = 'MyDB'SELECT *FROM @DB.sys.check_constraints[/code]If not, why would it work in dynamic SQL?Obviously, you've found an approach that does work though.[/quote]I haven't fired up 2k12 yet but I'd just bet that won't work. You can use variables for object names like that.The @CK thing being used in the queries is a pass through variable which is a trait of using sp_Execute SQL. Notice that where it's being used is not an object but acts just like what a variable would do in a norma query.[/quote]Did you mean "You can[b]not[/b] use variables for object names like that." ?I know. But wouldn't it be cool if you could. Think about it - naming columns on the fly! God what we could do with that to confuse the masses! :w00t:[/quote]Yes... that's what I meant. I went back and corrected the original post. Thanks for the catch on my phat phingering.Not only would it be cool to be able to name object names with a variable but it would also be cool if we could use variables and column names in the same SELECT like we can with UPDATE. Instead of "quirky updates", we could use "not so quirky SELECTs" were we can use an ORDER BY and the whole shebang!Wed, 21 Nov 2012 23:31:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1387468-3077-1.aspx[quote][b]D.Post (11/21/2012)[/b][hr]Is this correct or is there another way to do this?Something to do with SQL Injection? (No fear of)[/quote]No. No way to stipulate the database name in a variable without using dynamic SQL of some sort.So far as SQL injection goes, your code is wide open for it the way @DBName is currently used. The best way around it is to check that the content of @DBName actually exists in sys.databases or throw a fatal error.Items 2 and 4 may run but they don't actually work the way you would expect. Since you double single-quoted the @CK variable names, the variable names become quoted literals rather than variables.Wed, 21 Nov 2012 23:27:27 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1387468-3077-1.aspx[quote][b]Jeff Moden (11/21/2012)[/b][hr][quote][b]dwain.c (11/21/2012)[/b][hr]Would this work?[code="sql"]DECLARE @DB VARCHAR(100) = 'MyDB'SELECT *FROM @DB.sys.check_constraints[/code]If not, why would it work in dynamic SQL?Obviously, you've found an approach that does work though.[/quote]I haven't fired up 2k12 yet but I'd just bet that won't work. You can use variables for object names like that.The @CK thing being used in the queries is a pass through variable which is a trait of using sp_Execute SQL. Notice that where it's being used is not an object but acts just like what a variable would do in a norma query.[/quote]Did you mean "You can[b]not[/b] use variables for object names like that." ?I know. But wouldn't it be cool if you could. Think about it - naming columns on the fly! God what we could do with that to confuse the masses! :w00t:Wed, 21 Nov 2012 23:24:40 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1387468-3077-1.aspx[quote][b]dwain.c (11/21/2012)[/b][hr]Would this work?[code="sql"]DECLARE @DB VARCHAR(100) = 'MyDB'SELECT *FROM @DB.sys.check_constraints[/code]If not, why would it work in dynamic SQL?Obviously, you've found an approach that does work though.[/quote]I haven't fired up 2k12 yet but I'd just bet that won't work. You cannot use variables for object names like that.The @CK thing being used in the queries is a pass through variable which is a trait of using sp_Execute SQL. Notice that where it's being used is not an object but acts just like what a variable would do in a norma query.Wed, 21 Nov 2012 23:14:19 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic1387468-3077-1.aspxWould this work?[code="sql"]DECLARE @DB VARCHAR(100) = 'MyDB'SELECT *FROM @DB.sys.check_constraints[/code]If not, why would it work in dynamic SQL?Obviously, you've found an approach that does work though.Wed, 21 Nov 2012 22:52:17 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1387468-3077-1.aspxI was writing some code to check out the check_constraints of tables spread out accross multiple DBs in a partitioned viewMy idea was to use a parameterized query.Turns out you cannot use parameters for object names.Is this correct or is there another way to do this?Something to do with SQL Injection? (No fear of)Also, is it no longer necesseay to use + in dynamic SQL? (See test 4)[code="sql"]DECLARE @SQL NVarchar(1000), @DBName NVarchar(50), @CKName NVarchar(50), @Tbl NVarchar(50)SET @DBName = 'Master'SET @CKName = 'FakeName'SET @Tbl = 'Check_Constraints'-- 1: WorksSET @SQL = 'SELECT * FROM ' + @DBName + '.sys.Check_Constraints'EXEC (@SQL)-- 2: WorksSET @SQL = 'SELECT * FROM ' + @DBName + '.sys.Check_Constraints WHERE Name = ''@CK'''EXEC sp_ExecuteSQL @Stmt = @SQL, @Parms = N'@CK NVarchar(50)', @CK = @CKName-- 3: WorksSET @SQL = 'SELECT * FROM ' + @DBName + '.sys.Check_Constraints WHERE Name = @CK'EXEC sp_ExecuteSQL @Stmt = @SQL, @Parms = N'@CK NVarchar(50)', @CK = @CKName-- 4: Works ?? Same as WHERE Name = ''' + @CKName + '''' ??SET @SQL = 'SELECT * FROM ' + @DBName + '.sys.Check_Constraints WHERE Name = ''@CKName'''EXEC sp_ExecuteSQL @Stmt = @SQL, @Parms = N'@CK NVarchar(50)', @CK = @CKName-- 5: Doesn't workSET @SQL = 'SELECT * FROM ' + @DBName + '.sys.Check_Constraints WHERE Name = @CKName'EXEC sp_ExecuteSQL @Stmt = @SQL, @Parms = N'@CK NVarchar(50)', @CK = @CKName/*Msg 137, Level 15, State 2, Line 1Must declare the scalar variable "@CKName".*/-- 6: Doesn't workSET @SQL = N'SELECT * FROM Master.sys.@Tbl2 WHERE Name = @CK2'EXEC sp_ExecuteSQL @Stmt = @SQL, @Params =N'@Tbl2 NVarchar(50), @CK2 NVarchar(50)', @CK2 = @CKName, @Tbl2 = @Tbl/*Msg 102, Level 15, State 1, Line 1Incorrect syntax near '@Tbl2'.*/-- 7: Doesn't workSET @SQL = N'SELECT * FROM @DBName2.sys.Check_Constraints'EXEC sp_ExecuteSQL @Stmt = @SQL, @Params =N'@DBName2 NVarchar(50)', @DBName2 = @DBName/* ErrorMsg 102, Level 15, State 1, Line 1Incorrect syntax near '.'.*/[/code]ThanksWed, 21 Nov 2012 08:59:58 GMTDennis Post