json数据在字典中，一些值具有不同的数据类型，我想使用python将它们插入到postgres sql中

Question

现在 ： 我正在 python 中使用字符串连接方法在 psql 表中写入值 问题：将字符串值与其他值连接时 ('') 不会出现在连接的字符串中，因此 python 会给出错误（值位于从 json 文件创建的字典中） 将数据输入sql的代码

       for k in KEYS_NAME:
              KEYS=list(data[k][0].keys())
              Value =list(data[k][0].values())
              if(k!="BMS_Node_Param"):
                     #data[k][0]["u32SWVer"] ="\'"+str(data[k][0]["u32SWVer"])+"\'"
                     if k == 'BMS_Pack':
                            data[k][0]["u32uptime"] ="\'"+str(data[k][0]["u32uptime"])+"\'"
                     check = ",".join(KEYS)
                     val = ",".join(str(V)for V in Value)
                     conn.execute(("INSERT INTO {} ({}) VALUES ({});").format(k,check,val))

Answer 1

一个简单的例子，说明为什么不应使用字符串连接以及参数转义如何使您免遭 SQL 注入。

import psycopg2 

con = psycopg2.connect("dbname=test user=postgres host=localhost port=5432")    
cur = con.cursor()

cur.execute("create table injection_test(id integer, fld_1 varchar)")
con.commit()

cur.execute("insert into injection_test values (1, 'test'), (2, 'test2')")
con.commit()

cur.execute("select * from injection_test")
cur.fetchall()                                                                                                                                                                                                          
[(1, 'test'), (2, 'test2')]

qry_str = "select * from injection_test where id = 1 " + "; delete from injection_test" 

qry_str = "select * from injection_test where id = 1 " + "; delete from injection_test" 
cur.execute(qry_str)                                                                                                                                                                                                    
con.commit()                                                                                                                                                                                                            
cur.execute("select * from injection_test")                                                                                                                                                                             
cur.fetchall()                                                                                                                                                                                                          
[]

cur.execute("insert into injection_test values (1, 'test'), (2, 'test2')")
con.commit()
qry_str = "select * from injection_test where id = %s " 
cur.execute(qry_str, ["; delete from injection_test"])  

InvalidTextRepresentation: invalid input syntax for type integer: "; delete from injection_test"
LINE 1: select * from injection_test where id = E'; delete from inje...