Password character issues for } and ' in SQL connection string

I am using ODBC Driver 17 to connect to MSSQL server.
To avoid SQL injection possibilities, have enclosed password in {}.
Now, am facing issue if I give a password like }qwerty1234.
i.e. a password that starts with } will give invalid connection string error.
Can someone suggest a solution for the same?

Also, is there any known issue for using ' (single quotes) in password?

Intro

There was a situation over on The Heap™ – Consultancy ©® where McNets was having an issue with a connection string.

I blast off a quick:

I hope your connection strings password doesn't contain either of these: _ % @ ?

...and McNets was quick to reply with:

there is an @ in the password

As it turned out the @ (at sign) was breaking the connection string in McNets application.
Observations

I had previously encountered an issue with the % (percentage) sign in a SQL Server silent installation file. The command I was running was similar to the following:

E:\setup.exe /ACTION=INSTALL /CONFIGURATIONFILE=%~dpn0.ini /IACCEPTSQLSERVERLICENSETERMS /SAPWD="MyPassword%WillFail"

Because the SA password contained a % sign, the installation routine would not display the password in the corresponding dialog. The %WillFail was treated as an additional "DOS" parameter.
Generating Passwords for Database Logins & Systems

Over the years I have generated a lot of passwords and some have failed, while others have worked. It depended on:

• where the password was being used

• which RDBMS system I was accessing

This can be:

• Oracle Connection Strings

• SQL Server Installations

• ODBC Connection Strings

• DOS Commands

• Other Connection Strings

• C#

• Visual Basic

• MySQL

• PostgreSQL

Universal Password Generator

In order to improve the procedure of generating passwords I started generating long passwords (15+ characters length) with the password generator supplied in KeePass.

My password generator excludes most of the following characters:

= equals sign
" double quote
' single quote
& ampersand
% percentage
^ caret (accent circumflex)
* asterisk
? question mark
¨ diaeresis (umlaut)
´ forward tick (accent grave)
back tick (accent acute)
/ forward slash
\ back slash
@ at sign (commercial at)
[ left square bracket
] right square bracket
{ left curly bracket
} right curly bracket
( left round bracket
) right round bracket
; semicolon
_ underscore
# hash


Reasons for Breaking

Some of the reasons for excluding certain characters are only because of certain RDBMS whereas other characters break silent installation files. I will try and list some of the obvious reasons and some other maybe not so obvious reasons why some characters fail when used in passwords.
= equal

• In an ODBC connection string the equals sign is used to separate parameter from value.

• In SQL Server silent installations an equal sign will break the password.

" double quote

• Breaks the password for parameter=value settings in ODBC connection strings when the parameter starts with a double quote.

• Breaks connection strings in various programming languages depending on the language used.

' single quote (similar to double quote)

• Breaks the password for parameter=value settings in ODBC connection strings when the parameter starts with a single quote.

• Breaks connection strings in various programming languages depending on the language used.

% percentage

-
Fails in SQL Server silent installations when used in a conjunction with a BAT/CMD file that runs the setup.exe which supplies the password for a login.
E.g contents of a SETUP.BAT file:

E:\setup.exe /ACTION=INSTALL /CONFIGURATIONFILE=%~dpn0.ini /IACCEPTSQLSERVERLICENSETERMS /SAPWD="MyPassword%WillFail"

@ at sign (commercial at)



• Breaks Oracle connection strings when using the notation: user/password@oracle_sid. The @ in the password is interpreted as the terminator between password and oracle_sid.

• Breaks some connection strings as observed by McNets.

& ampersand

• Used to break ODBC connection strings.

? question mark

• If I recall correctly, this used to break very old ODBC connections strings (or was it OLE DB), because the question mark was "asking for a parameter".

_ underscore

• I'm not 100% sure why the underscore eventually made it into my list of undesired characters for database passwords, but I think it was to do with posting an HTTP request with an underscore in the password for a certain web application. (shout out to Andriy M. for asking in the comments)

# hash

• Apparently according to a comment, the hash can be used as a password in MySQL, but when it is stored in a configuration file, then anything after the hash is interpreted as a comment. (shout out to Scott N for mentioning this in the comments)

} right curly bracket`

• Will break the connection string in SQL Server ODBC connections as mentioned by OP in this question I am answering.

Other Characters

The other characters in my collection have made it there due to observations during my 26+ years in information technology and as a database administrator. They are probably not conclusive, but might help others when encountering errors trying to connect to a database. They might also help prevent issues when generating passwords and/or connection strings for various RDBMS

StackExchange Database Administrators Q#324927, answer score: 10