以安全的方式从字典数据创建表 [英] Create table from dictionary data in a safe way

问题描述

我有一个字典列表的问题，例如以下数据:

I have a problem where i have a list of dictionaries with for example the following data:

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

从该列表中，我需要根据列表中包含列名称和类型的每个字典动态创建一个 CREATE TABLE 语句，并使用 psycopg2 适配器在 PostgreSQL 数据库上执行它.

From that list i need to dynamically create a CREATE TABLE statement based on each dictionary in the list which contains the name of the column and the type and execute it on a PostgreSQL database using the psycopg2 adapter.

我设法做到了:

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

但是这个解决方案容易受到 SQL 注入的影响.我试图用来自 psycopg2 的 sql 模块做完全相同的事情，但没有运气.总是出现语法错误，因为它将类型用引号括起来.

But this solution is vulnerable to SQL injection. I tried to do the exact same thing with the sql module from psycopg2 but without luck. Always getting syntax error, because it wraps the type in quotes.

有什么办法可以安全地做到这一点吗?

Is there some way this can be done safely?

推荐答案

展开我的评论，一个完整的例子:

Expanding on my comment, a complete example:

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.

Iterate over the column list of dicts and assign the column names as SQL identifiers and the column types as straight SQL into sql.SQL construct. Use this as parameter to CREATE TABLE SQL.

警告:sql.SQL() 不进行转义，因此在使用这些值之前必须对其进行验证.

Caveat: sql.SQL() does not do escaping, so those values would have to be validated before they where used.

这篇关于以安全的方式从字典数据创建表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！