使用 psycopg cur.execute 创建 postgres 模式 [英] Creating postgres schemas using psycopg cur.execute
问题描述
我的 python 应用程序允许用户创建他们的命名模式.我需要一种方法来保护应用程序免受 sql 注入.
My python application allows users to create schemas of their naming. I need a way to protect the application from sql injections.
要执行的SQL读取
CREATE SCHEMA schema_name AUTHORIZATION user_name;
psycopg 文档(通常)建议像这样传递参数来执行
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.
推荐答案
要传递标识符,请使用 AsIs
.但这会暴露于 SQL 注入:
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)
输出:
CREATE SCHEMA u1 AUTHORIZATION u1; select * from user_table;
这篇关于使用 psycopg cur.execute 创建 postgres 模式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!