ADO.NET Security

 

Several areas in ADO.NET let you achieve improved security. The rest of this chapter focuses on these areas.

Partial Trust Support

AllowPartiallyTrustedCallersAttribute is fully implemented in the System.Data.dll assembly. Previous releases of the ADO.NET supported only System.Data.SqlClient, with partially trusted applications. In ADO.NET 2.0, the SQL Server, OLEDB, ODBC, and Oracle providers can all run in partially trusted environments.

To use SQL Server in a partially trusted environment, your application must have SqlClientPermission permissions. You can also use SqlClientPermissionAttribute members to further restrict the capabilities of the SQL Server provider, as shown in Table 13-3.

Table 13-3: SqlClientPermissionAttribute Properties

Property

Description

AllowBlankPassword

Controls the use of a blank password in a connection string.

ConnectionString

Restricts the permitted connection strings to the set specified by this property. Do not include user names and passwords.

KeyRestrictions

Sets connection string parameters that are allowed or disallowed. Parameters are assigned in the form <parameter name>=. You can specify multiple, semicolon (;) delimited parameters. You can use the KeyRestrictionBehavior property with this property to set the keys as allowed or disallowed.

KeyRestrictionBehavior

Specifies that the connection string parameters identified by the KeyRestrictions property are the only additional connection string parameters allowed (AllowOnly) or are not allowed (PreventUsage). The default is AllowOnly.

If you don't specify any KeyRestrictions and the KeyRestrictionBehavior property is set to AllowOnly, no additional connection string parameters are allowed. If you don't specify any KeyRestrictions and the KeyRestrictionBehavior property is set to PreventUsage, no additional connection string parameters are allowed.

Testing ADO.NET with a Partially Trusted Caller

To test ADO.NET in a partially trusted environment, create a Windows application project called PartialTrustTest. Add a reference to the System.Configuration.dll library. Add the Northwind database to the project, and add a button to the form. Change the button text to Get Version. Double-click the button, and add the following code.

image from book

Visual Basic

Imports System.Data.SqlClient Imports System.Configuration Public Class Form1    Private Sub button1_Click(ByVal sender As System.Object, _          ByVal e As System.EventArgs) Handles button1.Click       Dim cnSetting As ConnectionStringSettings = _          ConfigurationManager.ConnectionStrings("nwString")       Using cn As New SqlConnection(cnSetting.ConnectionString)          cn.Open()          MessageBox.Show(cn.ServerVersion)       End Using    End Sub End Class 
image from book

image from book

C#

using System; using System.Windows.Forms; using System.Configuration; using System.Data.SqlClient; namespace PartialTrustTest {    public partial class Form1 : Form    {       public Form1()       {          InitializeComponent();       }       private void button1_Click(object sender, EventArgs e)       {          ConnectionStringSettings cnSetting =             ConfigurationManager.ConnectionStrings["nwString"];          using (SqlConnection cn =             new SqlConnection(cnSetting.ConnectionString))          {             cn.Open();             MessageBox.Show(cn.ServerVersion);          }       }    } } 
image from book

This code simply reads the connection information from the app.config file, opens the connection, and gets the current server version. To make the code work, add an app.config file to the project that contains the following connection string information.

image from book

App.Config File

<?xml version="1.0" encoding="utf-8" ?> <configuration>    <connectionStrings>       <add name="nwString" connectionString=          "Data Source=.\SQLEXPRESS;             AttachDbFilename=|DataDirectory|\northwnd.mdf;             Integrated Security=True;User Instance=True"           providerName="System.Data.SqlClient" />       <add name="badString" connectionString=          "Data Source=.;AttachDbFilename=|DataDirectory|\northwnd.mdf;          Integrated Security=True;User Instance=True"           providerName="System.Data.SqlClient" />    </connectionStrings> </configuration> 
image from book

This file contains a valid connection string and a connection string that will be disallowed. After you add the app.config file, run the application and click the button; you should see the SQL Server version. At this point, the application is running with FullTrust permissions.

Add the following attributes to the AssemblyInfo file to make this application partially trusted.

image from book

Visual Basic

Imports System.Security.Permissions Imports System.Data.SqlClient <Assembly: SqlClientPermission(SecurityAction.RequestMinimum, _    Unrestricted:=True)> <Assembly: FileIOPermission(SecurityAction.RequestMinimum, _    Unrestricted:=True)> <Assembly: UIPermission(SecurityAction.RequestMinimum, _    Unrestricted:=True)> <Assembly: PermissionSet(SecurityAction.RequestOptional, _    Unrestricted:=false)> 
image from book

image from book

C#

using System.Security.Permissions; using System.Data.SqlClient; [assembly: SqlClientPermission(SecurityAction.RequestMinimum,    Unrestricted = true)] [assembly: FileIOPermission(SecurityAction.RequestMinimum,    Unrestricted = true)] [assembly: UIPermission(SecurityAction.RequestMinimum,    Unrestricted = true)] [assembly: PermissionSet(SecurityAction.RequestOptional,    Unrestricted = false)] 
image from book

If you run the application, it will still run properly as a partially trusted application. To prove that you are in a partially trusted environment, comment out the SQL Client permission request and restart the application; a SqlClientPermission exception is thrown. Uncomment the SQL Client permission request.

To test the ConnectionString, KeyRestriction, and KeyBehavior properties, add another button and change the button's text to Get Bad Version. Double-click the button, and copy the code from the first button and paste it into the second button. In the second button's code, change the name of the retrieved connection string to badString. If you have a default instance of SQL Server installed and you run the code, it will work.

Change the SqlClientPermission to add the ConnectionString, KeyRestriction, and KeyRestrictionBehavior, which will allow connections only to SQLEXPRESS and will limit the keys that can be used in the connection string. Also change the Unrestricted property to false to enable the other properties, as shown in the following code.

image from book

Visual Basic

<Assembly: SqlClientPermission(SecurityAction.RequestMinimum, _    Unrestricted:=False, _    ConnectionString:="Data Source=.\SQLEXPRESS", _    KeyRestrictionBehavior:=KeyRestrictionBehavior.AllowOnly, _    KeyRestrictions:="Data Source=;AttachDbFilename=;" + _       "Integrated Security=;User Instance=")> 
image from book

image from book

C#

[assembly: SqlClientPermission(SecurityAction.RequestMinimum,    Unrestricted = false,    ConnectionString = @"Data Source=.\SQLEXPRESS",    KeyRestrictionBehavior = KeyRestrictionBehavior.AllowOnly,    KeyRestrictions = "Data Source=;AttachDbFilename=;" +       "Integrated Security=;User Instance=")] 
image from book

If you test the application, clicking the first button displays the version and clicking the second button causes a SecurityException to be thrown, stating "Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture= neutral, PublicKeyToken=b77a5c561934e089' failed."

Storing Encrypted Connection Strings in Web Applications

It's common practice to store connection strings in the App.config file. This makes it easy to change the connection string without requiring a recompile of the application. The problem is that connection strings can contain login information such as user names and passwords. Quite frankly, I don't even want people to be able to see the location of the database server and the database name, but I also don't want to embed this information in my application.

In Web applications, the solution is to encrypt the connection string. You can do this by using the aspnet_regiis.exe utility to encrypt the connectionStrings section. You can use the /? option to get help on the utility.

You encrypt and decrypt the contents of a Web.config file by using the System.Configuration.DPAPIProtectedConfigurationProvider, which uses the Windows Data Protection API (DPAPI) to encrypt and decrypt data, or the System.Configuration.RSAProtectedConfigurationProvider, which uses the RSA encryption algorithm to encrypt and decrypt data.

When you use the same encrypted configuration file on many computers in a Web farm, only the System.Configuration.RSAProtectedConfigurationProvider allows you to export the encryption keys used to encrypt the data and import them on another server. This is the default setting.

Note 

For information on exporting and importing RSA encryption keys, see the ReadMe.txt file in the sample code for this chapter. The sample code has encrypted connection strings that use an RSA key that was generated on my computer. You will need to import the RSA key, just as you would need to do when working with Web farms

Implementing an Encrypted ConnectionString

Create an ASP.NET Web site called EncryptWebSite, and add the Northwind database to the App_Data folder. Add a GridView control to the Web form. Use the GridView Tasks menu to add a new data source. The new data source will be the northwnd.mdf file that you added to the App_Data folder. For the query, select all fields in the Customers table.

Try running the application it should run, and the Web.config file that Visual Studio generates will contain the connection string shown here.

image from book

Unencrypted Web.Config

<?xml version="1.0"?> <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">     <appSettings/>     <connectionStrings>         <add name="ConnectionString"             connectionString="Data Source=.\SQLEXPRESS;             AttachDbFilename=|DataDirectory|\northwnd.mdf;             Integrated Security=True;User Instance=True"             providerName="System.Data.SqlClient" />     </connectionStrings>     <system.web>             </system.web> </configuration> 
image from book

Next you can encrypt the Web.config file by running the Visual Studio .NET command prompt and executing the following command, specifying the full path to your Web site folder.

aspnet_regiis -pef "connectionStrings" "C:\...\EncryptWebSite" 

Note that the -pef switch requires you to pass the physical Web site path, which is the last parameter. Be sure to verify the path to your Web.config file. The encrypted Web.config file will look like the following.

image from book

Encrypted Web.Config File

<?xml version="1.0"?> <configuration xmlns="http://schemas.microsoft.com/.NetConfiguration/v2.0">    <protectedData>       <protectedDataSections>          <add name="connectionStrings"             provider="RsaProtectedConfigurationProvider"             inheritedByChildren="false" />       </protectedDataSections>    </protectedData>    <appSettings/>    <connectionStrings>       <EncryptedData Type="http://www.w3.org/2001/04/xmlenc#Element"        xmlns="http://www.w3.org/2001/04/xmlenc#">          <EncryptionMethod             Algorithm="http://www.w3.org/2001/04/xmlenc#tripledes-cbc" />          <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">             <EncryptedKey Recipient=""                xmlns="http://www.w3.org/2001/04/xmlenc#">                <EncryptionMethod                   Algorithm="http://www.w3.org/2001/04/xmlenc#rsa-1_5" />                <KeyInfo xmlns="http://www.w3.org/2000/09/xmldsig#">                   <KeyName>Rsa Key</KeyName>                </KeyInfo>                <CipherData> <CipherValue>PPWA1TkWxs2i698Dj07iLUberpFYIj6wBhbmqfmNK/plarau4i1k+xq5bZzB4VJW8 OkhwzcIIdZIXff6INJ1wlZz76ZV1DIbRzbH71t6d/L/qJtuOexXxTi2LrepreK/q3svMLpsJycnDPa t9xaGoaLq4Cg3P19Z1J6HquFILeo=</CipherValue>                </CipherData>             </EncryptedKey>          </KeyInfo>          <CipherData> <CipherValue>Q1re8ntDDv7/dHsvWbnIKdZF6COA1y3S91hmnhUN3nxYfrjSc7FrjEVyJfJhl5EDX 4kXd8ukAjrqwuBNnQbsh1PAXNFDflzB4FF+jyPKP/jm1Q9mDnmiq+NCuo3KpKj8F4vcHbcj+f3GYqq B4pYbblAvYnjPyPrrPmxLNT9KDtDr8pDbtGnKqAfcMnQPvA8l5w3BzPM4a73Vtt2kL/z9QJRu3Svd9 33taxOO/HufRJEnE2/hcBq30WcBmEuXx3LFNjV+xVmuebrInhhxQgM2froBKYxgjwWiWNjIIjIeTI2 FQ8nZ8V8kzAVohmDYkZpCj4NQGdrjD996h97phI6NnHZYZHJ7oPRz</CipherValue>          </CipherData>       </EncryptedData>    </connectionStrings>    <system.web>            </system.web> </configuration> 
image from book

If changes are made to the connectionStrings section for example, if another connection is added using the GUI tools the new connection will be encrypted, that is, you won't have to run the aspnet_regiis utility again.

You can decrypt the connectionStrings section by using the following command.

aspnet_regiis -pdf "connectionStrings" "C:\...\EncryptWebSite" 

After the connectionStrings section is decrypted, it will look just as it did before it was encrypted.

Preventing SQL Injection Attacks

A SQL injection attack occurs when an attacker finds a way to insert additional SQL statements into commands that are sent to the database server. The inserted commands can be used to destroy, modify, or retrieve private data. The inserted commands come from user input, so it's important to take steps to ensure that user input is validated and cannot contain inserted commands.

Your code is susceptible to SQL injection attacks if it builds a SQL statement by concatenating strings that come from user input. If the resultant SQL statement is syntactically correct and the caller has the appropriate permissions, SQL Server executes the commands.

Creating the SqlInjectionTest Project

To show how SQL injection works, create a Windows application called SqlInjectionTest. Add the Northwind database to the project, and set its Copy To Output Directory property to Copy Always. This will ensure that we use a clean copy of the database every time the application is run in Visual Studio .NET. Add a Label control, a Button control, and a DataGridView control to the form. Set the label's Text property to Enter Last Name: and set the button's Text property to Run Query. Double-click the button, and add the following code.

image from book

Visual Basic

Imports System.Data Imports System.Data.SqlClient Imports System.Configuration Public Class Form1    Private Sub Button1_Click(ByVal sender As System.Object, _          ByVal e As System.EventArgs) Handles Button1.Click       Dim cnSetting As ConnectionStringSettings = _          ConfigurationManager.ConnectionStrings("nwString")       Dim employees As New DataTable()       Using cn As New SqlConnection()          cn.ConnectionString = cnSetting.ConnectionString          Dim cmd As SqlCommand = cn.CreateCommand()          Dim sql As String = _             "SELECT * FROM employees WHERE LastName LIKE '" _             + TextBox1.Text.Trim() + "%'"          cmd.CommandText = sql          cn.Open()          employees.Load(cmd.ExecuteReader())          DataGridView1.DataSource = employees       End Using    End Sub End Class 
image from book

image from book

C#

using System; using System.Data; using System.Windows.Forms; using System.Configuration; using System.Data.SqlClient; namespace SqlInjectionTest {    public partial class Form1 : Form    {       public Form1()       {          InitializeComponent();       }       private void Button1_Click(object sender, EventArgs e)       {          ConnectionStringSettings cnSetting =             ConfigurationManager.ConnectionStrings["nwString"];          DataTable employees = new DataTable();          using (SqlConnection cn = new SqlConnection())          {             cn.ConnectionString = cnSetting.ConnectionString;             SqlCommand cmd = cn.CreateCommand();             string sql =                "SELECT * FROM employees WHERE LastName LIKE '"                + TextBox1.Text.Trim() + "%'";             cmd.CommandText = sql;             cn.Open();             employees.Load(cmd.ExecuteReader());             dataGridView1.DataSource = employees;          }       }    } } 
image from book

The button click code gets a list of employees based on the characters you type in the text box.

Add the following app.config file to the project, which has a connection string for the Northwind database.

image from book

App.Config File

<?xml version="1.0" encoding="utf-8" ?> <configuration>    <connectionStrings>       <add name="nwString" connectionString=          "Data Source=.\SQLEXPRESS;          AttachDbFilename=|DataDirectory|\northwnd.mdf;          Integrated Security=True;User Instance=True"           providerName="System.Data.SqlClient" />    </connectionStrings> </configuration> 
image from book

Run the application, type the letter S, and run the query, which yields a single employee. If you clear the text box and run the query again, all employees are returned.

What's wrong with this code? The problem is that it uses user input to build the query. There is no validation that the user has typed an appropriate input string. What happens if the user types the following text and runs the query?

%'; UPDATE employees SET lastname = 'you been hacked'; -- 

At first glance, you might not notice anything strange. The query returns the complete list of employees. If you clear the text from the text box and run the query again, you will see the results shown in Figure 13-16. Fortunately, you configured the Northwind database to be copied to the output folder on every build, but that overwrites all changes that might have been made, including the changes that you want to keep.

image from book
Figure 13-16: The last name was hacked using SQL injection

The user input that caused this problem starts with the %';, which closes the WHERE clause in the query, retrieving all employees, and the semicolon is a statement separator that is optional in SQL Server but might be required in other database servers. Next is the hack, which is an UPDATE statement to change the last name of the Employees table. Note that this could be any SQL statement, as long as the statement has the required permissions to run. So the deletion of table data can also happen if the deletion does not cause foreign key constraint exceptions. The hacker's text ends with comment syntax, --, which comments any existing text that is being added after the user input in the button click method. This is needed by the hacker to ensure that an exception is not thrown.

Protecting Against SQL Injection

You can imagine that this code opens the door for a hacker to do almost anything to your database. The scope of the damage depends on the permissions granted to the calling process. Running under a least-privilege account and validating user input is essential.

You can also pass column values as parameters instead of concatenating values. Add another button to the form, and add the following code to the new button, which uses a parameter with the command.

image from book

Visual Basic

Private Sub button2_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles button2.Click    Dim cnSetting As ConnectionStringSettings = _       ConfigurationManager.ConnectionStrings("nwString")    Dim employees As New DataTable()    Using cn As New SqlConnection()       cn.ConnectionString = cnSetting.ConnectionString       Dim cmd As SqlCommand = cn.CreateCommand()       Dim sql As String = _          "SELECT * FROM employees WHERE LastName LIKE @name + '%'"       cmd.CommandText = sql       cmd.Parameters.AddWithValue("@name", TextBox1.Text.Trim())       cn.Open()       employees.Load(cmd.ExecuteReader())       DataGridView1.DataSource = employees    End Using End Sub 
image from book

image from book

C#

private void button2_Click(object sender, EventArgs e) {    ConnectionStringSettings cnSetting =       ConfigurationManager.ConnectionStrings["nwString"];    DataTable employees = new DataTable();    using (SqlConnection cn = new SqlConnection())    {       cn.ConnectionString = cnSetting.ConnectionString;       SqlCommand cmd = cn.CreateCommand();       string sql =          "SELECT * FROM employees WHERE LastName LIKE @name + '%'";       cmd.CommandText = sql;       cmd.Parameters.AddWithValue("@name", TextBox1.Text.Trim());       cn.Open();       employees.Load(cmd.ExecuteReader());       dataGridView1.DataSource = employees;    } } 
image from book

Notice that it was not too difficult to change the query to use a parameter. In fact, this SQL statement might be easier to read in complex queries. In the SQL statement, the parameter is a SQL variable. In the .NET code, the parameter can be quickly initialized by using the AddWithValue method.

Try running the same tests again, using the second button. Everything works as expected, and when the would-be attacker tries to inject the same SQL, the resultant data is empty because the complete input was used to find an employee named "%'; update employees set lastname = 'you been hacked'; --". If you rerun the query with an empty text box, you see all employees with their correct last name the attack failed.

This was a quick and easy fix, but you should also consider limiting the size of the text input passed into the parameter by setting the Size property on the parameter. After all, if the LastName field is only 20 characters long, you don't need to allow more than 20 characters in the parameter value.

Using Regular Expressions to Protect Against SQL Injection You can also protect against SQL injection attacks by using regular expressions to validate the data before placing the data into a parameter. Regular expressions offer a very powerful means to test for valid sequences of characters. Table 13-4 contains a small list of common regular expressions, and a search for "Regular Expressions" on the Web will yield many more.

Table 13-4: Common Regular Expressions

Description

Regular Expression

Sample

Name Up to 40 uppercase and lowercase characters, spaces, dashes, and apostrophes that are common to some names

^[a-zA-Z''-'\s]{1,40}$

Joe O'Boy

Social Security Number Consists of 3 numeric characters, a dash, 2 numeric characters, another dash, and 4 numeric characters

^\d{3}-\d{2}-\d{4}$

123-45-6789

U.S. Phone Number Consists of 3 numeric characters that can optionally be enclosed in parentheses, followed by 3 numeric characters, a dash, and 4 numeric characters

^[01]?[- .]?(\([2-9]\d{2}\)|[2-9]\d{2})[- .]?\d{3}[- .]?\d{4}$

555 555-5555

-555 555-5555

-(555) 555-5555

555-555-5555

E-mail Address Allows sets of alphanumeric characters delimited by the dot or dash, followed by the at symbol (@), and then more sets of alphanumeric characters (not starting with a dash), delimited with a dot, where the length of the last set of characters must be between 2 and 9 characters

^([0-9a-zA-Z]([-.\w]*[0-9a-zA-Z])*@([0-9a-zA-Z][-\w]*[0-9a-zA-Z]\.)+[a-zA-Z]{2,9})$

Glenn@GJTT.com

Glenn.Johnson@GJTT.com

G-Johnson@Local.GJTT.com

To test regular expression validation, add another Button control to the form and set its Text property to "Run Query with Regular Expression and Parameter". Copy the code from the previous Button control's event handler to this Button control's Click event handler, and add regular expression code to validate the user input as follows.

image from book

Visual Basic

Private Sub Button3_Click(ByVal sender As System.Object, _       ByVal e As System.EventArgs) Handles Button3.Click    If (TextBox1.Text.Length > 0) Then       If Not (System.Text.RegularExpressions.Regex.IsMatch( _          TextBox1.Text, "^[a-zA-Z'.]{1,40}$")) Then          MessageBox.Show( _           "Invalid Query. Must contain only characters (max 40)")          Return       End If    End If    Dim cnSetting As ConnectionStringSettings = _       ConfigurationManager.ConnectionStrings("nwString")    Dim employees As New DataTable()    Using cn As New SqlConnection()       cn.ConnectionString = cnSetting.ConnectionString       Dim cmd As SqlCommand = cn.CreateCommand()       Dim sql As String = _          "Select * from employees where LastName like @name + '%'"       cmd.CommandText = sql       cmd.Parameters.AddWithValue("@name", TextBox1.Text.Trim())       cn.Open()       employees.Load(cmd.ExecuteReader())       DataGridView1.DataSource = employees    End Using End Sub 
image from book

image from book

C#

private void Button3_Click(object sender, EventArgs e) {    if (TextBox1.Text.Length > 0)    {       if (!System.Text.RegularExpressions.Regex.IsMatch(          TextBox1.Text,"^[a-zA-Z'.]{1,40}$"))       {          MessageBox.Show(           "Invalid Query. Must contain only characters (max 40)");          return;       }    }    ConnectionStringSettings cnSetting =       ConfigurationManager.ConnectionStrings["nwString"];    DataTable employees = new DataTable();    using (SqlConnection cn = new SqlConnection())    {       cn.ConnectionString = cnSetting.ConnectionString;       SqlCommand cmd = cn.CreateCommand();       string sql =          "Select * from employees where LastName like @name + '%'";       cmd.CommandText = sql;       cmd.Parameters.AddWithValue("@name", TextBox1.Text.Trim());       cn.Open();       employees.Load(cmd.ExecuteReader());       dataGridView1.DataSource = employees;    } } 
image from book

Try running the tests again, using this button. You will find that you will not pass the regular expression validation because the length is more than 40 characters and special characters were used.

Last, if you are working on a Web application, you can easily implement regular expression validation by using the RegularExpressionValidator control, which can be attached to a TextBox control to perform client- and server-side validation using regular expressions.

Using Stored Procedures

Stored procedures provide an efficient way to execute T-SQL or SQLCLR code on the database server. If your data changes can be made without first moving the data to the client, performance will benefit.

Stored procedures also provide a simple means of assigning permissions. Consider situations in which a single stored procedure is performing inserts, updates, and deletes to multiple tables. You might want the user to have permission to execute this action, but you might not want the user to be able to execute ad hoc statements directly to the same tables. You can assign execute permissions to the stored procedure without assigning permissions at the table level, and the store procedure will execute, as long as the owner of the stored procedure is the same as the owner of the tables.

Although you can eliminate SQL injection attacks without implementing stored procedures, you would not have had any threat of SQL injection if you implemented stored procedures from the start, because parameters are the mechanism for passing user criteria to a stored procedure.

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net