public void None_Childs()
{
SqlClientPermission perm = new SqlClientPermission(PermissionState.None);
perm.Add("data source=localhost;", String.Empty, KeyRestrictionBehavior.AllowOnly);
perm.Add("data source=127.0.0.1;", "password=;", KeyRestrictionBehavior.PreventUsage);
Check("None-Childs-1", perm, false, false, 2);
perm.AllowBlankPassword = true;
Check("None-Childs-2", perm, true, false, 2);
SqlClientPermission copy = (SqlClientPermission)perm.Copy();
Check("Copy_None-Childs-1", copy, true, false, 2);
copy.AllowBlankPassword = false;
Check("Copy_None-Childs-2", copy, false, false, 2);
}
public void Unrestricted_Add()
{
SqlClientPermission perm = new SqlClientPermission(PermissionState.Unrestricted);
Check("Unrestricted-NoChild", perm, false, true, 0);
perm.Add("data source=localhost;", String.Empty, KeyRestrictionBehavior.AllowOnly);
// note: Lost unrestricted state when children was added
Check("Unrestricted-WithChild", perm, false, false, 1);
}
public static void InsertLatestStockPrice(string symbol)
{
try
{
PermissionSet perms = new PermissionSet(PermissionState.None);
string url = "http://finance.yahoo.com/d/quotes.csv?s=" + symbol +
"&f=sl1d1t1c1ov";
WebPermission webPerm = new WebPermission(NetworkAccess.Connect, url);
perms.AddPermission(webPerm);
SqlClientPermission sqlPerm = new SqlClientPermission(
PermissionState.None);
sqlPerm.Add("context connection=true", "",
KeyRestrictionBehavior.AllowOnly);
perms.AddPermission(sqlPerm);
perms.PermitOnly();
string[] data = HttpFileReader.ReadFile(url);
string[] cols = data[0].Split(new char[] { ',' });
string date = cols[2].Substring(1, cols[2].Length - 2);
string time = cols[3].Substring(1, cols[3].Length - 2);
DateTime tradetime = DateTime.Parse(date + " " + time);
double price = Double.Parse(cols[1]);
double change = Double.Parse(cols[4]);
SqlDouble openprice = cols[5] == "N/A" ? SqlDouble.Null :
SqlDouble.Parse(cols[5]);
int volume = Int32.Parse(cols[6]);
using (SqlConnection cn = new SqlConnection("context connection=true"))
{
cn.Open();
string cmdStr = "INSERT INTO StockPrices VALUES (@symbol, @price, @tradetime, @change, @openprice, @volume)";
SqlCommand cmd = new SqlCommand(cmdStr, cn);
cmd.Parameters.AddWithValue("@symbol", symbol);
cmd.Parameters.AddWithValue("@price", price);
cmd.Parameters.AddWithValue("@tradetime", tradetime);
cmd.Parameters.AddWithValue("@change", change);
cmd.Parameters.AddWithValue("@openprice", openprice);
cmd.Parameters.AddWithValue("@volume", volume);
cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
SqlPipe pipe = SqlContext.Pipe;
pipe.Send(e.Message);
}
}
static void TestCAS(string connectString1, string connectString2)
{
// Create permission set for sandbox AppDomain.
// This example only allows execution.
PermissionSet permissions = new PermissionSet(PermissionState.None);
permissions.AddPermission(new SecurityPermission(SecurityPermissionFlag.Execution));
// Create sandbox AppDomain with permission set that only allows execution,
// and has no SqlClientPermissions.
AppDomainSetup appDomainSetup = new AppDomainSetup();
appDomainSetup.ApplicationBase = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
AppDomain firstDomain = AppDomain.CreateDomain("NoSqlPermissions", null, appDomainSetup, permissions);
// Create helper object in sandbox AppDomain so that code can be executed in that AppDomain.
Type helperType = typeof(PartialTrustHelper);
PartialTrustHelper firstHelper = (PartialTrustHelper)firstDomain.CreateInstanceAndUnwrap(helperType.Assembly.FullName, helperType.FullName);
try {
// Attempt to open a connection in the sandbox AppDomain.
// This is expected to fail.
firstHelper.TestConnectionOpen(connectString1);
Console.WriteLine("Connection opened, unexpected.");
}
catch (System.Security.SecurityException ex) {
Console.WriteLine("Failed, as expected: {0}",
ex.FirstPermissionThatFailed);
// Uncomment the following line to see Exception details.
// Console.WriteLine("BaseException: " + ex.GetBaseException());
}
// Add permission for a specific connection string.
SqlClientPermission sqlPermission = new SqlClientPermission(PermissionState.None);
sqlPermission.Add(connectString1, "", KeyRestrictionBehavior.AllowOnly);
permissions.AddPermission(sqlPermission);
AppDomain secondDomain = AppDomain.CreateDomain("OneSqlPermission", null, appDomainSetup, permissions);
PartialTrustHelper secondHelper = (PartialTrustHelper)secondDomain.CreateInstanceAndUnwrap(helperType.Assembly.FullName, helperType.FullName);
// Try connection open again, it should succeed now.
try {
secondHelper.TestConnectionOpen(connectString1);
Console.WriteLine("Connection opened, as expected.");
}
catch (System.Security.SecurityException ex) {
Console.WriteLine("Unexpected failure: {0}", ex.Message);
}
// Try a different connection string. This should fail.
try {
secondHelper.TestConnectionOpen(connectString2);
Console.WriteLine("Connection opened, unexpected.");
}
catch (System.Security.SecurityException ex) {
Console.WriteLine("Failed, as expected: {0}", ex.Message);
}
}
public static void GetSalesForNames(SqlString filename)
{
try
{
PermissionSet perms = new PermissionSet(PermissionState.None);
// Ensure that only correct file can be accessed through this method
FileIOPermission ioPerm = new FileIOPermission(
FileIOPermissionAccess.Read, @"C:\names.txt");
perms.AddPermission(ioPerm);
// Permit access to SQL Server data
SqlClientPermission sqlPerm = new SqlClientPermission(
PermissionState.None);
sqlPerm.Add("context connection=true", "",
KeyRestrictionBehavior.AllowOnly);
perms.AddPermission(sqlPerm);
perms.PermitOnly();
// Get the names from the text file as a string array
string[] names = FileReader.ReadFile(filename.ToString());
// Build SQL statement
StringBuilder sb = new StringBuilder();
sb.Append(@"SELECT emp.EmployeeID,
sp.SalesYTD + sp.SalesLastYear AS RecentSales
FROM Sales.SalesPerson sp
INNER JOIN HumanResources.Employee emp
ON emp.EmployeeID = sp.SalesPersonID
WHERE sp.SalesPersonID IN
(
SELECT emp.EmployeeID
FROM HumanResources.Employee emp
INNER JOIN Person.Contact c
ON c.ContactID = emp.ContactID
WHERE c.FirstName + ' ' + c.MiddleName + ' ' +
c.LastName
IN (");
// Concatenate array into single string for WHERE clause
foreach (string name in names)
{
sb.Append("'");
sb.Append(name);
sb.Append("', ");
}
sb.Remove(sb.Length - 2, 2);
sb.Append("))");
// Execute the SQL statement and get back a SqlResultSet
using (SqlConnection cn = new SqlConnection(
"context connection=true"))
{
cn.Open();
SqlCommand cmd = new SqlCommand(sb.ToString(), cn);
SqlDataReader dr = cmd.ExecuteReader();
// Send success message to SQL Server and return SqlDataReader
SqlPipe pipe = SqlContext.Pipe;
pipe.Send(dr);
pipe.Send("Command(s) completed successfully.");
cn.Close();
}
}
catch (Exception e)
{
SqlPipe pipe = SqlContext.Pipe;
pipe.Send(e.Message);
pipe.Send(e.StackTrace);
pipe.Send("Error executing assembly");
}
}