Wednesday, June 11, 2014

xp_cmdshell in sql server

Hi,

today I got a request to enable xp_cmdshell on my sql server because an application needed the functionality to list the content of a directory out of a t-sql stored procedure.

The functionality is pretty old (already supported by sql server 2000), but evil referring to sql server security blog entry: http://blogs.msdn.com/b/sqlsecurity/archive/2008/01/10/xp-cmdshell.aspx

If security context is not set with caution enabling this setting enables a user to access a command line to the server with administrator rights. This is bad. (Default is to access the command line with the security context of the sql server service account, but it can be changed to other credentials using: sp_xp_cmdshell_proxy_account )

Syntax:
xp_cmdshell { 'command_string' } [ , no_output ]


Enabled the feature:
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
reference: http://msdn.microsoft.com/en-us/library/ms190693.aspx

Check if it is turned on or off:
1
2
3
4
5
6
7
SELECT 
   name AS [Configuration], 
   CONVERT(INT, ISNULL(value, value_in_use)) AS [IsEnabled]
FROM  
   master.sys.configurations
WHERE  
   name = 'xp_cmdshell'
reference: http://sqltidbits.com/scripts/check-if-xpcmdshell-enabled-across-multiple-servers

... and here an example how it can be used (found in the web):
1
2
3
4
5
6
7
8
9
create table #tmp(result varchar(255))
insert into #tmp exec master.dbo.xp_cmdshell 'ping yahoo.com'
 
if exists(select * from #tmp where result like '%request timed out%')
 print 'timeout'
else
 print 'reply'
 
drop table #tmp
references: http://www.nullskull.com/q/11008/xpcmdshell-output.aspx

and even better:

1
2
3
4
5
6
declare @results table(result varchar(255))

insert into @results
exec sp_executesql N'xp_cmdshell ''ping www.yahoo.com'''

select * from @results
reference: http://stackoverflow.com/questions/1842126/call-tracert-and-ping-from-sql-and-put-the-result-in-sql-table

kind regards,
Daniel

Tuesday, June 10, 2014

creating objects dynamically c#

Hi,

today I am writing on my term paper and I needed a way to create objects dynamically while having a type object.

I tried the old-school way as following:

1
2
var x = typeof(List<string>).GetConstructor(
   new Type[] { }).Invoke(new object[] { });

and got my expected result. This was kind a hard way but it worked as it did 10 years ago.

My problem was now: I knew that the object to create is List, but wanted to have string dynamically passed in by a parameter or generic into my function. I found the following solution after about an hour of googling:


1
2
var y = Activator.CreateInstance(
                typeof(List<>).MakeGenericType(typeof(string)));

MakeGenericType does the trick. Using activator is not necessary, but seems to be much easier to use and perfect for my use case. With shame I have to admit that Activator seems to exists since .NET framework 1.1 (= 2003). Anyway, I love it.

Kind regards,
Daniel