Escape ;(semicolon) in odbc connection string in app.config file

Question

I have created a windows form with certain fields. I am trying to interact with oracle database through ODBC DSN connections.

I have an issue in the below connection string in app.config .

In the connection string password contains semicolon(here abc;45). I am getting an error:

"Format of the initialization string does not conform to specification starting at index 35"

while i am trying to access this connection string using

OdbcConnection connection = new OdbcConnection(connection_string);

in C# code.

Below is my connection string.

<add name="ConnectionString_T1" connectionString="DSN=CLA_T5;Uid=abc;Pwd=abc;45" providerName="System.Data.Odbc" />

 OdbcConnection connection = new OdbcConnection(connection_string);

PS: I tried putting this password in double quotes/single quote/" . But no use. Still facing this error. I have tried putting all the escape sequences like ",double quote, single quote,/" etc..

Answer 1

Rikitikitik is partly correct about escaping []{(),;?*=!@ characters in an ODBC Connection String by surrounding the value with {} , but misses a subtle but extremely important ODBC Connection String escape rule that is NOT documented by Microsoft about escaping }.

PASS CASE: connectionString="DSN=CLA_T5;Uid=abc;Pwd={abc;45}"

Works correctly to escape the ;, but will fail when the password is

FAIL CASE: connectionString="DSN=CLA_T5;Uid=abc;Pwd={abc;}45}"

Because the first } character is interpreted as the closing brace of the escape pair, rather than the second (correct) }.

To correct for this, you have to manually escape the } with a second }

PASS CASE: connectionString="DSN=CLA_T5;Uid=abc;Pwd={abc;}}45}", which will be read as abc;}45} by the ODBC engine.

This appears to be a totally undocumented, despite several MSDN sources outlining the enclosing {} escaping Rikitikitik mentions.

Documentation failing to mention the interior } escape method:

• https://msdn.microsoft.com/en-us/library/system.data.odbc.odbcconnection.connectionstring(v=vs.110).aspx
• https://msdn.microsoft.com/en-us/library/ms130822.aspx

However, the interior } escape method is clearly observable with a quick .net test harness:

OdbcConnectionStringBuilder builder = new OdbcConnectionStringBuilder
builder.Driver = "{SomeDriver}"
builder.Add("UID", "user");
builder.Add("PWD", "abc;}45"); 

MessageBox.Show(builder.ConnectionString) // observe PWD's escaped value of "{abc;}}45}"

try
{
    using (OdbcConnection conn = new OdbcConnection(builder.ConnectionString))
    {
        //           
        MessageBox.Show("SUCCEEDED");
    }
}
catch (Exception ex)
{
    MessageBox.Show($"{ResourceManager.GetString("FAILED: ")} {ex.Message}");
}

where SomeDriver has a user user with password abc;}45

Answer 2

I ran into a similar problem with an entity framework connection string. While I was not using an OdbcConnection I thought I would add what worked for me to help anyone with my issue that was similar. We had a password created with a semicolon in it. And it broke our entity framework connection string. The error was:

'Keyword not supported: '[rest of password after semicolon];multipleactiveresultsets'.'

I resolved the issue by placing single quotes (') around the username and password fields. The result was a connection string that contained the following:

initial catalog=myDB;User     
Id='MyUser';Password='abc;123';multipleactiveresultsets=True;

Answer 3

Apparently, ODBC is special when it comes to escaping connection string values with semicolons, equal signs, etc. According to this, you should enclose values with special characters in curly braces {}

connectionString="DSN=CLA_T5;Uid=abc;Pwd={abc;45}"

Answer 4

Try this connection string...

<add name="ConnectionString_T1" connectionString="DSN=CLA_T5;Uid='abc';Pwd='abc;45';" providerName="System.Data.Odbc" />

I am using same type of tricky password and solve this issue in this way....