Writing a connection string when password contains special characters

Question

I'm using SQLalchemy for a Python project, and I want to have a tidy connection string to access my database. So for example:

engine = create_engine('postgresql://user:pass@host/database')

The problem is my password contains a sequence of special characters that get interpreted as delimiters when I try to connect.

I realize that I could just use engine.URL.create() and then pass my credentials like this:

import sqlalchemy as sa

connection_url = sa.engine.URL.create(
    drivername="postgresql",
    username="user",
    password="p@ss",
    host="host",
    database="database",
)
print(connection_url)
# postgresql://user:p%40ss@host/database

But I'd much rather use a connection string if this is possible.

So to be clear, is it possible to encode my connection string, or the password part of the connection string - so that it can be properly parsed?

Porterhouse

Can you give an example of this special char that cannot be escaped by a backslash? – Lucarne

missing "=" after "X6~k9?q" in connection info – Porterhouse

that happens whether I escape it or not – Porterhouse

@Porterhouse were you able to resolve this ? Thanks – Swetlana

"I realize that I could just use engine.URL.create() and then pass my credentials" - Future readers should not gloss over the fact that this really is the preferred method of creating a connection URL. – Phil

Answer 1

You need to URL-encode the password portion of the connect string:

from urllib.parse import quote_plus
from sqlalchemy.engine import create_engine
engine = create_engine("postgres://user:%s@host/database" % quote_plus("p@ss"))

If you look at the implementation of the class used in SQLAlchemy to represent database connection URLs (in sqlalchemy/engine/url.py), you can see that they use the same method to escape passwords when converting the URL instances into strings.

Threesquare

The part about using unquote_plus was apparently a bug and changed in v0.9.0 (ref) - Dec 2013. Interestingly, they still quote with quote_plus but unquote with unquote so there may still be some bug lingering in there (src). – Terranceterrane

quote_plus will cause issues if there are spaces in the password (it will encode a space as + which is a valid character. it should be encoded as %20). better use quote – Saltus

Tank you! This literally saved me inordinate amounts of time editing passwords on all our environments. – Puccini

Answer 2

In Python 3.x, you need to import urllib.parse.quote:

The urllib module has been split into parts and renamed in Python 3 to urllib.request, urllib.parse, and urllib.error.

When you are trying to connect database MySQL with password which contains sequence of special characters and your python version is Python3

user_name is your userid for database
database is your database name
your_password password with special characters

 from urllib.parse import quote  
 from sqlalchemy.engine import create_engine
 engine = create_engine('mysql+mysqlconnector://user_name:%s@localhost:3306/database' % quote('your_password'))

Rubin

does not work if you have an @ in the password. – Swetlana

Tanu, the quoting will change "@" to '%40'. – Juggler

Answer 3

METHOD 1:

The password contains "@", you can escape the "@" character using "%40" instead.

BEFORE:

# mssql+pymssql://username:password@databaseserver/database

mssql+pymssql://admin:[email protected]/dbtest

AFTER:

mssql+pymssql://admin:admin%[email protected]/dbtest

METHOD 2:

Encode the password using urllib.parse.quote_plus.

DATABASE_PASSWORD = "admin@123"

# to elimate the error, if the password contains special characters like '@' 
DATABASE_PASSWORD_UPDATED = urllib.parse.quote_plus(DATABASE_PASSWORD)

Here is the complete code snippet :

import os, sys, click, urllib
from flask import Flask, jsonify, request
from flask_sqlalchemy import SQLAlchemy
from sqlalchemy import text

# Make sure to replace below data with your DB values
DATABASE_HOST = "10.10.10.110"
DATABASE_NAME = "dbtest"
DATABASE_USERNAME = "admin" 
DATABASE_PASSWORD = "admin@123"

app = Flask(__name__)

# to elimate the error, if the password contains special characters like '@'
# replace the DATABASE_PASSWORD with DATABASE_PASSWORD_UPDATED. 

DATABASE_PASSWORD_UPDATED = urllib.parse.quote_plus(DATABASE_PASSWORD)
app.config['SQLALCHEMY_DATABASE_URI'] = 'mssql+pymssql://'+DATABASE_USERNAME+':'+DATABASE_PASSWORD_UPDATED+'@'+DATABASE_HOST+'/'+DATABASE_NAME
app.config['SQLALCHEMY_ECHO'] = True


db = SQLAlchemy(app)

if __name__ == "__main__":
    app.run(host="0.0.0.0", port=5000, debug=True)

Espinal