python - How do I make input text from textboxes store in databse whilst preventing security flaws

Question

I have a website in Flask, which is basically a Twitter clone, where I have a textarea element for recording tweets, and I'm using MySQL to post the tweet to the database.

Now the problem is, when someone tweets something that contains symbols, the flask-mysql connector can't convert the symbols into text and it breaks the program. For example, when I tweet:

# When I tweet this
The '%^&!@#:';.,}{][ characters break everything

using the following code in main.py:

# This code handles the tweet
conn = mysql.connect()
cursor = conn.cursor()

try: 
    cursor.execute(f"INSERT INTO tweets (username, tweet_content) values ('%s', %s)", (session['username'], content))
    conn.commit()
    success = True
except Exception as e:
    print("!!!!   error : ", e)
finally: 
    conn.close()

the error reads:

!!!!   error :  (1064, "You have an error in your 
SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use 
near 'mohit2004'', 'The\r\n!@$@#%@%&^*%^!#%@!^%' at line 1")

I know this is happening because the symbols can't be converted into text strings, and when the tweet_text is passed, the ' symbols mix and break the code.

Is there a way to convert all these symbols to something that can be stored into the database like ' to &single_quote, " to &double_quote

Something like that, and then convert it back to normal text, when a user views it.

I can't find any relevant search term for a service like this on the internet, and I know that this question will be highly devoted, but if you know any service like, or if they are built into Flask, then let me know.

Edit highlight: the html.escape function does not work in this case.

question from:https://stackoverflow.com/questions/65939116/how-do-i-make-input-text-from-textboxes-store-in-databse-whilst-preventing-secur

Answer 1

You can use the built-in module html with its functions:

• escape to convert characters to HTML entities.
• unescape to convert HTML entities to characters.

Here is your example:

>>> import html
>>>
>>> text = "The ' character breaks everything"
>>> html_text = html.escape(text)
>>> html_text
The ' character breaks everything
>>>
>>> html.unescape(html_text)
The ' character breaks everything