json data is in dictionary some of values are of different data type and i want insert them in to postgres sql using python

Question

Currently : I am using string concatenation method in python to write values in psql table Issue : On concatenating a string value with other values ('') does not appear in the concatenated string so python gives an error (values are in dictionary created from a json file) Code for entering data into 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

A simple example of why string concatenation should not be used and how the parameter escaping saves you from SQL injection.

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...