Building Connection Strings
Each of the .NET Framework data providers provides a strongly typed connection string builder class that inherits from DbConnectionStringBuilder. The connection string builders let developers programmatically create syntactically correct connection strings based on user input, as well as parse and rebuild existing connection strings by using methods of the class. Connection string builders provide strongly-typed properties that correspond to the known key/values pairs allowed by each provider.
The following table lists the providers and their associated strongly-typed ConnectionStringBuilder classes.
| Provider | ConnectionStringBuilder Class |
|---|---|
Handling Connection String Injection
In previous versions of ADO.NET there was no compile time checking of connection strings that consisted of concatenated string values. At runtime an incorrect keyword would generate an invalid ArgumentException. Since values received from a user were not checked or quoted appropriately, it was possible for an attacker to bypass expected settings.
The ConnectionStringBuilder classes all perform checks for valid key/value pairs. An invalid pair will throw an exception and injected values will be handled in a safe manner. Each class maintains a fixed collection of synonyms and can translate from a synonym to the corresponding well-known key name.
For example, the following code fragment using the SqlConnectionStringBuilder has an inserted extra value for the Initial Catalog setting.
Dim builder As New System.Data.SqlClient.SqlConnectionStringBuilder
builder("Data Source") = "(local)"
builder("Integrated Security") = True
builder("Initial Catalog") = "AdventureWorks;NewValue=Bad"
System.Diagnostics.Debug.WriteLine(builder.ConnectionString)
System.Data.SqlClient.SqlConnectionStringBuilder builder =
new System.Data.SqlClient.SqlConnectionStringBuilder();
builder["Data Source"] = "(local)";
builder["integrated Security"] = true;
builder["Initial Catalog"] = "AdventureWorks;NewValue=Bad";
System.Diagnostics.Debug.WriteLine(builder.ConnectionString);
The SqlConnectionStringBuilder handles this correctly by escaping the extra value in double quotes.
data source=(local);Integrated Security=True;
initial catalog="AdventureWorks;NewValue=Bad"
Building Connection Strings from Configuration Files
The overloaded constructor for a ConnectionStringBuilder can take a String. This example demonstrates retrieving a partial connection string from a configuration file and completing it by dynamically assigning additional values. This allows you to obtain additional information such as the data source, server name, or even user ID and password if you cannot use Windows authentication.
This code example assumes that the connectionStrings element in the configuration file looks like the following.
<connectionStrings>
<add name="databaseConnection"
connectionString="Data Source=(local);Initial Catalog=Northwind;"
providerName="System.Data.SqlClient" />
</connectionStrings>
The code retrieves the partial connection string from the configuration file, and then creates a new SqlConnectionStringBuilder to add the User ID and Password values to complete the connection string. The original connection string and the modified connection string are displayed in the console window. Note that this example requires a reference to the System.Configuration namespace as well as to System.Data.SqlClient.
Private Sub BuildConnectionString( _
ByVal userName As String, ByVal userPassword As String)
' Retrieve the partial connection string named databaseConnection
' from the application's app.config or web.config file.
Dim settings As ConnectionStringSettings = _
ConfigurationManager.ConnectionStrings("databaseConnection")
If Not settings Is Nothing Then
' Retrieve the partial connection string.
Dim connectString As String = settings.ConnectionString
Console.WriteLine("Original: {0}", connectString)
' Create a new SqlConnectionStringBuilder and set the User ID
' and Passworrrd to the strings supplied as an input parameters.
Dim builder As New SqlConnectionStringBuilder(connectString)
builder.UserID = userName
builder.Password = userPassword
Console.WriteLine("Modified: {0}", builder.ConnectionString)
End If
End Sub
private static void BuildConnectionString(
string userName, string userPassword)
{
// Retrieve the partial connection string named databaseConnection
// from the application's app.config or web.config file.
ConnectionStringSettings settings =
ConfigurationManager.ConnectionStrings["databaseConnection"];
if (null != settings)
{
// Retrieve the partial connection string.
string connectString = settings.ConnectionString;
Console.WriteLine("Original: {0}", connectString);
// Create a new SqlConnectionStringBuilder and set the User ID
// and Password to the strings supplied as an input parameters.
SqlConnectionStringBuilder builder =
new SqlConnectionStringBuilder(connectString);
builder.UserID = userName;
builder.Password = userPassword;
Console.WriteLine("Modified: {0}", builder.ConnectionString);
}
}