Creating postgres schemas using psycopg cur.execute

Question

My python application allows users to create schemas of their naming. I need a way to protect the application from sql injections.

The SQL to be executed reads

CREATE SCHEMA schema_name AUTHORIZATION user_name;

The psycopg documentation (generally) recommends passing parameters to execute like so

conn = psycopg2.connect("dbname=test user=postgres")
cur = conn.cursor()
query = 'CREATE SCHEMA IF NOT EXISTS %s AUTHORIZATION %s;'
params = ('schema_name', 'user_name')
cur.execute(query, params)

But this results in a query with single quotes, which fails:

CREATE SCHEMA 'schema_name' AUTHORIZATION 'user_name';
> fail

Is there a way to remove the quotes, or should I just settle for stripping non-alphanumeric characters from the schema name and call it a day? The later seems kind of ugly, but should still work.

Clodoaldo Neto · Accepted Answer · 2015-12-02 11:51:09Z

6

To pass identifiers use AsIs. But that exposes to SQL injection:

import psycopg2
from psycopg2.extensions import AsIs

conn = psycopg2.connect(database='cpn')
cursor = conn.cursor()
query = """CREATE SCHEMA %s AUTHORIZATION %s;"""
param = (AsIs('u1'), AsIs('u1; select * from user_table'))
print cursor.mogrify(query, param)

Output:

CREATE SCHEMA u1 AUTHORIZATION u1; select * from user_table;

answered Dec 2, 2015 at 11:51

Clodoaldo Neto

127k30 gold badges251 silver badges274 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

frozenjim · Accepted Answer · 2020-08-14 15:45:09Z

Here's a boilerplate that might help. I've used environment variables but you can use a .conf or whatever you like.

Store your connection variables in a .env file:

db_host = "localhost"
db_port = "5432"
db_database = "postgres"
db_user = "postgres"
db_password = "postgres"
db_schema = "schema2"

Load params in your app.py and assign them to variables, then use the variables where required:

import psychopg2
from dotenv import load_dotenv
import database

# Load your environment variables here:
load_dotenv()

db_host = os.environ["db_host"]
db_port = os.environ["db_port"]
db_database = os.environ["db_database"]
db_user = os.environ["db_user"]
db_password = os.environ["db_password"]
db_schema = os.environ["db_schema"]

# Build Connection:
connection = psycopg2.connect(host=db_host, 
                              port=db_port, 
                              database=db_database, 
                              user=db_user, 
                              password=db_password
                              )

# Build Query Strings:
CREATE_SCHEMA = f"CREATE SCHEMA IF NOT EXISTS {schema};"
CREATE_TABLE1 = f"CREATE TABLE IF NOT EXISTS {schema}.table1 (...);"
CREATE_TABLE2 = f"CREATE TABLE IF NOT EXISTS {schema}.table2 (...);"


# Create Schema and Tables:
with connection:
    with connection.cursor() as cursor:
        cursor.execute(CREATE_SCHEMA)
        cursor.execute(CREATE_TABLE1)
        cursor.execute(CREATE_TABLE2)

piro · Accepted Answer · 2017-07-21 11:49:25Z

0

As of psycopg2 >= 2.7, psycopg2.sql can be used to compose dynamic statements, which also guards from SQL injection.

answered Jul 21, 2017 at 11:49

piro

14k6 gold badges39 silver badges40 bronze badges

Comments

rob.loh · Accepted Answer · 2024-10-08 12:58:46Z

0

To remove the single quotes:

query = query.as_string(conn)
query = query.replace("\'", "")

conn.execute(query)

answered Oct 8, 2024 at 12:58

rob.loh

135 bronze badges

Collectives™ on Stack Overflow

Creating postgres schemas using psycopg cur.execute

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related