Python3 : How to escape special characters for cx_oracle continuous stream of insert statement (ORA-01756: quoted string not properly terminated)

Question

I am using python code which reads the CSV file and for every row it inserts in Oracle Database.

Is there anyway to overcome "ORA-01756: quoted string not properly terminated" error for all use cases.

Special Characters which I want to escape are Single Quote ('), Double Quotes ("), Comma (,) and Others if that can lead to error.

My logic is actually below :

    with open(files, newline='',  encoding='utf-8') as csvfile:
        rowreader = csv.reader(csvfile, delimiter=';', quotechar='|')
        next(rowreader)
        for row in rowreader:
            values = parseCSV.input(row)
            query = "INSERT INTO MYTABLE(col1,col2) values('{val1}','{val2}')".format(**values)
            cursor.execute(query)

Above doesn't works for if string to be inserted - 'my's name'

Answer 1

Yes -- use parameters/binds.

Via the cx_oracle manual on using binds:

# assuming `values` is a dict with `val1` and `val2`:
cursor.execute("INSERT INTO MYTABLE (col1, col2) values(:val1, :val2)", values)

Also note how the manual page says "never do this!!!" for how you interpolate data into your statement - your code would currently vulnerable be to SQL injection attacks too.