Clash Royale CLAN TAG#URR8PPP

Exception from SqlConnectionBuilder

A connection string is passed to a .net app from Delphi and this code throws an exception:

string conString = "Provider=SQLOLEDB.1;User ID=tstuser;password=sa123&<>"';Initial Catalog=MyDatabase;Data Source=localhost;OLE DB Services=-4";

var builder = new SqlConnectionStringBuilder

ConnectionString = conString
;


When I split connection string and assign all properties - all good with builder:

Dictionary<string, string> conProperties = new Dictionary<string, string>(StringComparer.InvariantCultureIgnoreCase);

connectionString.Split(';')
.Select(t => t.Split(new char '=' , 2))
.ToDictionary(t => t[0].Trim(), t => t[1].Trim(), StringComparer.InvariantCultureIgnoreCase)
.ToList()
.ForEach(t => conProperties.Add(t.Key, t.Value));

var builder = new SqlConnectionStringBuilder()

UserID = conProperties["User ID"],
Password = conProperties["Password"],
InitialCatalog = conProperties["Initial Catalog"],
DataSource = conProperties["Data Source"]
;


Is there a bug in SqlConnectionStringBuilder, or am I missing something?

SqlConnectionStringBuilder

1 Answer
1

Your first example does indeed yield an exception that indicates the problem:

Format of the initialization string does not conform to specification starting at index 36.

Format of the initialization string does not conform to specification starting at index 36.

The substring index corresponds to the password field. Some code demonstrates the issue here:

SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
builder.Password = "sa123&<>\"'";
Console.WriteLine(builder.ToString());


This yields: Password="sa123&<>""'" -- which suggests the problem is the connection string itself rather than the SqlConnectionStringBuilder; the " character in your input string needs to be doubled, and the whole password wrapped in quotes.

Password="sa123&<>""'"

You don't hit this when setting the password via the property because the builder handles properly escaping the supplied string for you.

EDIT: The reason for having to do this is explained by the connection string syntax. The " you've used in the question escapes the " character for the compiler, so it doesn't treat the " as terminating the string literal. However, the actual problem is the ' that follows immediately afterwards; the syntax of the connection string has different rules around how quote characters are handled:

"

"

"

'

The basic format of a connection string includes a series of
keyword/value pairs separated by semicolons. The equal sign (=)
connects each keyword and its value. To include values that contain a
semicolon, single-quote character, or double-quote character, the
value must be enclosed in double quotation marks. If the value
contains both a semicolon and a double-quote character, the value can
be enclosed in single quotation marks. The single quotation mark is
also useful if the value starts with a double-quote character.
Conversely, the double quotation mark can be used if the value starts
with a single quotation mark. If the value contains both single-quote
and double-quote characters, the quotation mark character used to
enclose the value must be doubled every time it occurs within the
value.

To include preceding or trailing spaces in the string value, the value
must be enclosed in either single quotation marks or double quotation
marks. Any leading or trailing spaces around integer, Boolean, or
enumerated values are ignored, even if enclosed in quotation marks.
However, spaces within a string literal keyword or value are
preserved. Single or double quotation marks may be used within a
connection string without using delimiters (for example, Data Source=
my'Server or Data Source= my"Server), unless a quotation mark
character is the first or last character in the value.

(Source: SqlConnection.ConnectionString Remarks)

Since your connection string ends with a ', it is subject to the wrapping rule in the last sentence.

'

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.