python-3.xsql-serverpyodbc

Pyodbc connection string correctly escaping password with special characters

I have connection string with password with special characters

";" , " { ", " ' ", " . "

sample password -> "fdfs{;S'fd,_dj.fsgdfgdf"

how to escape such password to avoid authentication failure

I need this so I connect to database using Pyodbc by calling

pyodbc.connect(connection_str)

Using pyodbc with mssqlodbc driver

Solution

  • The escaping rules for ODBC connections to SQL Server a really quite straightforward:

    If the raw password contains ; or it begins with { then it must be enclosed in curly brackets and any } characters in the raw password must be doubled.

    import pyodbc

def odbc_escape(value):
    if ";" in value or value.startswith("{"):
        return "{" + value.replace("}", "}}") + "}"
    else:
        return value

raw_password = "fdfs{;S'fd,_dj.fsgd}fgdf"
escaped_password = odbc_escape(raw_password)
print(escaped_password)
# {fdfs{;S'fd,_dj.fsgd}}fgdf}

connection_str = (
    "DSN=mssql_199;"
    "UID=gord;"
    f"PWD={escaped_password};"
)
print(connection_str)
# DSN=mssql_199;UID=gord;PWD={fdfs{;S'fd,_dj.fsgd}}fgdf};

cnxn = pyodbc.connect(connection_str)
print("Connected.")

    More details at

    https://github.com/mkleehammer/pyodbc/wiki/Connecting-to-databases