5

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?

2 Answers 2

10

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 and users.

Answering Your Questions

Can someone suggest a solution for the same?

You might be able to escape the } with a backslash \, i.e. \}, but this depends on where the string is located.

I would refrain from using the } in your password.

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

See my above listing for possible issues.

Reference

This answer was taken from my Blog Post over on topanswers titled Passwords for Databases.

6

Now, am facing issue if I give a password like }qwerty1234. i.e. a password that starts with } will give invalid connection string error.

You can use the ODBC escape characters and sequence to do this: {}

If the password is }qwerty1234 then you'll need to escape it, although I've found that this depends on the application.

I wrote a little app using the .net ODBC connection string builder and this is what it gave:

Driver={ODBC Driver 17 for SQL Server};Server=.;UID=odbclogin;PWD=}qwerty1234

Which didn't escape it, however when using other escape needing characters, it does escape the right curly brace with another right curly brace.

Driver={ODBC Driver 17 for SQL Server};Server=.;UID=odbclogin;PWD={}}qwerty1234!@#$;}

I would offer that if the application isn't working with the values as you've put them in, then it isn't properly handling the escape sequences or going through something that will and you'll need to manually escape with either {} or }.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.