Επεξεργασία

Κοινή χρήση μέσω


Connection string builders

Applies to: .NET Framework .NET .NET Standard

Download ADO.NET

In earlier versions of ADO.NET, compile-time checking of connection strings with concatenated string values didn't occur, so that at run time, an incorrect keyword generated an ArgumentException. The Microsoft SqlClient Data Provider for SQL Server includes the connection string builder class Microsoft.Data.SqlClient.SqlConnectionStringBuilder that inherits from DbConnectionStringBuilder.

Connection string injection attacks

A connection string injection attack can occur when dynamic string concatenation is used to build connection strings that are based on user input. If the string isn't validated and malicious text or characters not escaped, an attacker can potentially access sensitive data or other resources on the server. For example, an attacker could mount an attack by supplying a semicolon and appending another value. The connection string is parsed by using a "last one wins" algorithm, and the hostile input is replaced for a legitimate value.

The connection string builder classes are designed to eliminate guesswork and protect against syntax errors and security vulnerabilities. They provide methods and properties corresponding to the known key/value pairs permitted by the data provider. Each class maintains a fixed collection of synonyms and can translate from a synonym to the corresponding well-known key name. Checks are done for valid key/value pairs and an invalid pair throws an exception. Also, injected values are handled in a safe manner.

The following example demonstrates how the SqlConnectionStringBuilder handles an inserted extra value for the Initial Catalog setting.

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.DataSource = "(local)";
builder.IntegratedSecurity = true;
builder.InitialCatalog = "AdventureWorks;NewValue=Bad";
Console.WriteLine(builder.ConnectionString);

The output shows that the SqlConnectionStringBuilder handled it correctly by escaping the extra value in double quotation marks instead of appending it to the connection string as a new key/value pair.

data source=(local);Integrated Security=True;
initial catalog="AdventureWorks;NewValue=Bad"

Build connection strings from configuration files

If certain elements of a connection string are known beforehand, they can be stored in a configuration file and retrieved at run time to construct a complete connection string. For example, the name of the database might be known in advance, but not the name of the server. Or you might want a user to supply a name and password at run time without the ability to inject other values into the connection string.

One of the overloaded constructors for a connection string builder takes a String as an argument, which enables you to supply a partial connection string that can then be completed from user input. The partial connection string can be stored in a configuration file and retrieved at run time.

Note

The System.Configuration namespace allows programmatic access to configuration files that use the WebConfigurationManager for Web applications and the ConfigurationManager for Windows applications. For more information about working with connection strings and configuration files, see Connection Strings and Configuration Files.

Example

This example demonstrates retrieving a partial connection string from a configuration file and completing it by setting the DataSource, UserID, and Password properties of the SqlConnectionStringBuilder. The configuration file is defined as follows.

<connectionStrings>
  <clear/>
  <add name="partialConnectString"
    connectionString="Initial Catalog=Northwind;"
    providerName="Microsoft.Data.SqlClient" />
</connectionStrings>

Note

You must set a reference to the System.Configuration.dll in your project for the code to run.

private static void BuildConnectionString(string dataSource,
    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["partialConnectString"];

    if (null != settings)
    {
        // Retrieve the partial connection string.
        string connectString = settings.ConnectionString;
        Console.WriteLine("Original: {0}", connectString);

        // Create a new SqlConnectionStringBuilder based on the
        // partial connection string retrieved from the config file.
        SqlConnectionStringBuilder builder =
            new SqlConnectionStringBuilder(connectString);

        // Supply the additional values.
        builder.DataSource = dataSource;
        builder.UserID = userName;
        builder.Password = userPassword;
        Console.WriteLine("Modified: {0}", builder.ConnectionString);
    }
}

See also