How to handle meta characters in insert_format.format?

Question

I am receiving the values of text fields from the front end and performing sql operation on the values. I am using "insert_format.format" to format the fields. But this does not work when meta characters are sent from the UI.

Below is my python code:

insert_string_template = ''' INSERT INTO  SELF_SERVICE_ALARMS.metric_definition  
                                             (metric_id, 
                                             login, 
                                             metric_name, 
                                             schema_name, 
                                             metric_object,
                                             metric_column_name
                                             ) 
                                             VALUES'''
        insert_format = "(nextval ('metric_id_seq'),'" + getpass.getuser() + "','{}','{}','{}','{}')"

This does not work when someone inputs a single quote. For example :

if the user inputs " biswajit's " rule in metric_name it expands to

INSERT INTO  SELF_SERVICE_ALARMS.metric_definition  
                                             (metric_id, 
                                             login, 
                                             metric_name, 
                                             schema_name, 
                                             metric_object,
                                             metric_column_name
                                             ) 
                                             VALUES


nextval ('metric_id_seq') , 'xxx', 'biswajit's rule', 'yyy' , 'zzz' ,'aaa') ```

The query fails because of the single quote in 'biswajit's rule'.

Is there a way to fix this issue in insert_format ??



 

Answer 1

Python replace function worked here. I used dt['metric_name'].replace("'","''") to format the data which replaces ' with '' which fix this problem. we can use E (') also the same way.

Eg:

insert_string = insert_string + insert_format.format(dt['metric_id'],dt['metric_id'] dt['metric_name'].replace("'","''"),dt['metric_object'],dt['metric_column_name'])