Блог

У меня проблема, когда у меня есть список словарей, содержащий, например, следующие данные:

 columns = [{
    'name': 'column1',
    'type': 'varchar'
},
{
    'name': 'column2',
    'type': 'decimal'
},
.
.
.
]
  

Из этого списка мне нужно динамически создать инструкцию CREATE TABLE на основе каждого словаря в списке, который содержит имя столбца и тип, и выполнить его в базе данных PostgreSQL с использованием адаптера psycopg2.

Мне удалось сделать это с:

 columns = "("   ",n".join(["{} {}".format(col['name'], col['type']) for col in columns])   ")"
cursor.execute("CREATE TABLE some_table_namen {}".format(columns))
  

Но это решение уязвимо для внедрения SQL. Я пытался сделать то же самое с модулем sql из psycopg2, но безуспешно. Всегда получаю синтаксическую ошибку, потому что она заключает тип в кавычки.

Есть ли какой-то способ, которым это можно сделать безопасно?

Вы можете использовать AsIs для добавления типов столбцов без кавычек:

 import psycopg2
from psycopg2.extensions import AsIs
import psycopg2.sql as sql

conn = psycopg2.connect("dbname=mf port=5959 host=localhost user=mf_usr")

columns = [{
    'name': "column1",
    'type': "varchar"
},
{
    'name': "column2",
    'type': "decimal"
}]

# create a dict, so we can use dict placeholders in the CREATE TABLE query.
column_dict = {c['name']: AsIs(c['type']) for c in columns}

createSQL = sql.SQL("CREATE TABLE some_table_namen ({columns})").format(
    columns = sql.SQL(',').join(
        sql.SQL(' ').join([sql.Identifier(col), sql.Placeholder(col)]) for col in column_dict)
)

print(createSQL.as_string(conn))
cur = conn.cursor()
cur.execute(createSQL, column_dict)
cur.execute("insert into some_table_name (column1) VALUES ('foo')")
cur.execute("select * FROM some_table_name")
print('Result: ', cur.fetchall())
  

Вывод:

 CREATE TABLE some_table_name
 ("column1" %(column1)s,"column2" %(column2)s)
Result:  [('foo', None)]
  

Примечание:
psycopg2.sql безопасно для SQL-инъекции, AsIs вероятно, нет. Тестирование с использованием 'type': "varchar; DROP TABLE foo" привело к синтаксической ошибке Postgres:

 b'CREATE TABLE some_table_namen ("column1" varchar; DROP TABLE foo,"column2" decimal)'
Traceback (most recent call last):
  File "pct.py", line 28, in <module>
    cur.execute(createSQL, column_dict)
psycopg2.errors.SyntaxError: syntax error at or near ";"
LINE 2:  ("column1" varchar; DROP TABLE foo,"column2" decimal)
  

Расширяя мой комментарий, полный пример:

 import psycopg2 
from psycopg2 import sql

columns = [{
    'name': 'column1',
    'type': 'varchar'
},
{
    'name': 'column2',
    'type': 'decimal'
}]

con = psycopg2.connect("dbname=test host=localhost user=aklaver") 
cur = con.cursor()

col_list = sql.SQL(',').join( [sql.Identifier(col["name"])   sql.SQL(' ')   sql.SQL(col["type"]) for col in columns])
create_sql = sql.SQL("CREATE TABLE tablename ({})").format(col_list)

print(create_sql.as_string(con))                                                                                                                                          
CREATE TABLE tablename ("column1" varchar,"column2" decimal)

cur.execute(create_sql)
con.commit()


test(5432)=> d tablename 
                   Table "public.tablename"
 Column  |       Type        | Collation | Nullable | Default 
--------- ------------------- ----------- ---------- ---------
 column1 | character varying |           |          | 
 column2 | numeric           | 

  

Выполните итерацию по списку столбцов dicts и назначьте имена столбцов в качестве SQL идентификаторов, а типы столбцов — непосредственно SQL в sql.SQL конструкции. Используйте это как параметр для CREATE TABLE SQL .

Предостережение: sql.SQL() не выполняет экранирование, поэтому эти значения должны быть проверены перед их использованием.