Creating postgres schemas using psycopg cur.execut

问题:

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.

回答1:

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;

回答2:

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