Inserting HTML into mysql Database showing Type Error

Question

I am developing a Webapp using Flask. At some point, I have to insert a certain HTML script into a MySQL database:

<h3>Welcome!</h3>
<p>Some text</p>

When I insert it into the database (when it is returned by flask's 'render_template' function):

\n\n<h3>Welcome!</h3>\n\n\n\n<p>Some text</p>

I get the following error:

TypeError: ProgrammingError(1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '\\\\\\\\n\\\\n<h3>Welcome!</h3>\\\\n\\\\n\\\\n\\\\n<p>Some text' at line 1") is not JSON serializable

I first don't understand what 'JSON serializable' means, and I want to know what I am doing wrong. I have already tried taking off the linebreaks (\n) but it still shows the same error. Why? I am thankful for any answer you can provide.

score 0 · Accepted Answer · answered Dec 27 '17 at 13:58

A solutions that are commonly used when writing HTML to databases to:

1) Simply convert the database field type to blob so it will accept binary data and then encode HTML to binary (example below). 2) Leave the database field as a text field, but base64 encode the data so that the database will not complain about illegal characters.

# Example for case 1.
# Note that you need to make sure the database field is a blob:
html = '<h3>Welcome!</h3>\n<p>Some text</p>'
bin = html.encode()
dbhandle.execute('INSERT INTO script (various fields, binhtml) VALUES (..., bin)')
# When you read back the data, remember to decode.
dbhandle.execute('SELECT binhtml FROM script WHERE...')
resultset = dbhandle.fetchall()
htmlresult = resultset.decode()

# Example for case 2.
# Database field can be a text/varchar type because base64 ensures it will work.
import base64
html = '<h3>Welcome!</h3>\n<p>Some text</p>'
# Convert HTML into base64 encoded *text* so it can be stored in text field.
encoded =  base64.b64decode(html.encode()).decode()
# Do the database INSERT.
...
# Retrieve the stored text from the database and convert back to HTML
dbhandle.execute('SELECT encodedhtml FROM script WHERE...')
resultset = dbhandle.fetchall()
htmlresult = base64.b64decode(resultset).decode()

Inserting HTML into mysql Database showing Type Error

1 Answers1