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

You can put a backslash character followed by a quote ( \" or \' ). This is called an escape sequence and Python will remove the backslash, and put just the quote in the string. In complicated situations, you may need to escape the \ with another \ or 2 to pass through the process twice. I recall using \ by the bushel in FRED the friendly editor from Waterloo! — David G. Pickett, Aug 18 '20 at 21:31
Never use string concatenation with user supplied strings to build an SQL statement. That makes you vulnerable to SQL injection, which can do worse things than cause an error. Use a prepared statement to avoid the danger. — Laurenz Albe, Aug 19 '20 at 02:59

score 0 · Answer 1 · answered Aug 21 '20 at 22:13

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'])

How to handle meta characters in insert_format.format?

1 Answers1