Python: MySQL: Handle Illegal unquoted characters in insert statement

Question

I have MySQL (5.6.10) table with two columns A: varchar(255), B: text

When I run the following:

from mysql import connector as sqlserver

Database = sqlserver.connect(host=host, user=user, passwd=passwd, db=db)
Database.autocommit = True
Cursor = Database.cursor()

Cursor.execute('INSERT INTO Table (A, B) VALUES (%s, %s)',
               tuple(['This_is_a_test', '\n\nThis is a test\n\n']))

Raises the exception:

Illegal unquoted character ((CTRL-CHAR, code 10)): has to be escaped using backslash

If I do instead:

import json

Cursor.execute('INSERT INTO Table (A, B) VALUES (%s, %s)',
               tuple(['This_is_a_test', json.dumps('\n\nThis is a test\n\n')]))

Then it is successful, however the value in the table is:

'"\n\nThis is a test\n\n"'

Instead of:

'

This is a test

'

I can enter in the value manually to have the correct format, but how should you pass in the string as a variable and have it keep the correct formatting?

Does this answer your question? [How to write string literals in python without having to escape them?](https://stackoverflow.com/questions/4703516/how-to-write-string-literals-in-python-without-having-to-escape-them) — Karl Knechtel, Aug 08 '22 at 04:15

score 0 · Answer 1 · answered Oct 15 '21 at 00:35

I found a solution here: https://stackoverflow.com/a/57828614/11307485

As stated, add "fr" before the string:

from mysql import connector as sqlserver

Database = sqlserver.connect(host=host, user=user, passwd=passwd, db=db)
Database.autocommit = True
Cursor = Database.cursor()

Cursor.execute('INSERT INTO Table (A, B) VALUES (%s, %s)',
               tuple(['This_is_a_test', fr'\n\nThis is a test\n\n']))

It does not keep the native formatting, but the double quotes from json.dumps() are not present which I can easily work around.

The `r` is relevant here; the `f` is not helpful, and potentially a security risk. — Karl Knechtel, Aug 08 '22 at 04:16

Python: MySQL: Handle Illegal unquoted characters in insert statement

1 Answers1