8

I'm having a trouble with the ' character in a connection string. Entity Framework throws an exception saying:

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

I tried the answers suggested here and here to no avail.

I also tried constructing it with SqlConnectionStringBuilder class and got the same exception as a result.

Can anyone help me out?

Thanks in advance.

EDIT:

The connectionstring is indeed in the web.config file and it looks like this:

<add name="TestEntities" connectionString="metadata=res://*/DAL.TestModel.csdl|res://*/DAL.TestModel.ssdl|res://*/DAL.TestModel.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.\testsource;initial catalog=testdb;User Id=testuser;Password=test'password;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework&quot;" providerName="System.Data.EntityClient" />

the problem is with the Password=test'password bit.

  1. I can't put it in double quotes because it's already surrounded by them.
  2. I can't put it between &quot;-s because it's already surrounded by them too.
  3. And I can't put it in single quotes because the single quote itself is what I'm trying to escape.

Password='test&apos;password' doesn't work.

Community
  • 1
  • 1
TKharaishvili
  • 1,997
  • 1
  • 19
  • 30

2 Answers2

15

I have finally stumbled upon a correct answer. It appears, you have to surround the value with single quotes and additionally duplicate the inner apostrophe in order to escape it, like this:

Password='test''password'

This is such a dumb problem to waste this much time on. I hope it saves somebody time in the future.

Thanks to everyone for participating.

TKharaishvili
  • 1,997
  • 1
  • 19
  • 30
  • Saved me a headache. – Mardymar Apr 06 '18 at 22:01
  • Your solution has helped me out. Thanks a ton. and for future readers: It's also worth checking if the password has also saved correctly in case you're only checking at the reading part. – Steven Aug 16 '18 at 08:20
  • Thanks a ton Man .. I was getting Mad in SSIS due to this issue. Need to add a single quote at the start and end, also add a quote for each quote in between the password – Premjit Chowdhury Jan 07 '22 at 08:12
3

Assuming it is the password which contains a single quote, you should try enclosing the password in xml encoded double-quotes, something like this :

<connectionStrings>
  <add name="MyConnectionString" connectionString="Data Source=.;
    Initial Catalog=MYDB; User ID=MyUser;Password=&quot;my'password&quot;;
    providerName="System.Data.SqlClient";/>
</connectionStrings>

Assuming you are not in Xml context (as mentionned by @PanagiotisKanavos ) the ConnectionString initialization would look like :

var conn = new SqlConnection("Data Source=.;
    Initial Catalog=MYDB; User ID=MyUser;Password=\"my'password\";
    providerName="System.Data.SqlClient")
jbl
  • 15,179
  • 3
  • 34
  • 101
  • The problem isn't about quotation in XML strings. In order to contain special characters, a property value must be enclosed in single or double quotation marks itself – Panagiotis Kanavos Apr 07 '16 at 08:30