CODEWITHSUNDEEP
pythonsecuritysqlalchemypymongosql-injection
C8H10N4O2
C8H10N4O2

Reputation: 19025

Prevent connection string pollution attacks in Python, if needed

I want to return results of a predefined query when passed a user's userid and password for the database.

The query is constant, so not the basic SQL injection scenario.

However, does allowing a user to specify username and password that will be .format()-ted into the connection string pose any vulnerability?

(I found a source suggesting it does -- mostly I'm interested in sqlalchemy.create_engine and pymongo.MongoClient but would be interested in any common data stores and python modules.)

How should I modify the below example to sanitize the inputs, if needed?

from sqlalchemy import create_engine # requires module: psycopg2
import urllib.parse
import pandas as pd
import getpass

CONSTANT_QUERY_STRING = "SELECT * FROM table1;"
DB_URI = 'postgresql://{db_user}:{db_password}@postgres.acme.com:5432/acme_db'

class DbConnector:
  def __init__(self, db_uri, db_user, db_password):
    self.uri = db_uri.format(db_user=db_user, db_password=urllib.parse.quote_plus(db_password))

  def get_data(self, query):
      engine = create_engine(self.uri)
      df = pd.read_sql_query(query, con=engine)
      engine.dispose()
      return df

if __name__ == "__main__":
    userid = input('User: ')
    password = getpass.getpass('Password for {}:'.format(userid))
    df = DbConnector(DB_URI,userid,password).get_data(CONSTANT_QUERY_STRING)
    if df:
        print("Here's your data!")
        print(df)

Upvotes: 2

Views: 1197

Answers (2)

snakecharmerb
snakecharmerb

Reputation: 55933

For SQLAlchemy, you can automatically URL-encode special characters by passing a URL instance instead of using a formatted (or otherwise composed) string. This will neutralise* the type of attacks described in Gil Cohen's answer. URL instances should be created using the URL.create constructor.

from sqlalchemy import URL

drivername = 'postgresql'
user = 'alice'
password = '[email protected]:5432/secretdb?'
host = 'good.example.com'
port = 5432
database = 'testdb'
query = ''


url = URL.create(
    drivername=drivername, username=user, password=password, host=host, port=port, database=database, query=query
)
# Show masked url
print(url)
# Show unmasked url (note escaped password)
print(url.render_as_string(hide_password=False))

Output

postgresql://alice:***@good.example.com:5432/testdb
postgresql://alice:password%40bad.example.com%3A5432%[email protected]:5432/testdb

URLs can also be created by passing a string to the make_url function. This function does not perform any URL-encoding, so it will not prevent such attacks.

* Obligatory disclaimer: The security benefits of URL.create are a side-effect of it's functionality, it isn't a dedicated security tool. Other forms of attack may exist. If you are securing high-value information, hire security professionals.

Upvotes: 0

Gil Cohen
Gil Cohen

Reputation: 846

Like any injection of any kind - it depends in the special characters an attacker is able to inject and the accepted URI format. Let's say I inject the following values:

Username: user

Password: [email protected]:5432/acme_db?

or

[email protected]:5432/acme_db#

What would be the outcome?

postgresql://user:[email protected]:5432/[email protected]:5432/acme_db

postgresql://user:[email protected]:5432/acme_db#@postgres.acme.com:5432/acme_db

Your application would connect to a different DB and the behavior might be modified completely. I don't know if these URI formats would be accepted or not, but the question is: why take the chance?

You know what the valid username and password structure is, just validate it and encode special characters that you allow (if you want to allow any character in passwords). Probably URI encoding will do the job.

Upvotes: 1

Related Questions