Python sql escape special characters

Welcome to the world of string encoding formats!

tl;dr - The preferred method for handling quotes and escape characters when storing data in MySQL columns is to use parameterized queries and let the MySQLDatabase driver handle it. Alternatively, you can escape quotes and slashes by doubling them up prior to insertion.

Full example at bottom of link

standard SQL update

# as_json must have escape slashes and quotes doubled
query = """\
        UPDATE json_sandbox
        SET data = '{}'
        WHERE id = 1;
    """.format[as_json]

with DBConn[*client.conn_args] as c:
    c.cursor.execute[query]
    c.connection.commit[]

parameterized SQL update

# SQL Driver will do the escaping for you
query = """\
        UPDATE json_sandbox
        SET data = %s
        WHERE id = %s;
    """

with DBConn[*client.conn_args] as c:
    c.cursor.execute[query, [as_json, 1]]
    c.connection.commit[]

Invalid JSON SQL

{
  "abc": 123,
  "quotes": "ain't it great",
  "multiLine1": "hello\nworld",
  "multiLine3": "hello\r\nuniverse\r\n"
}

Valid JSON SQL

{
  "abc": 123,
  "quotes": "ain''t it great",
  "multiLine1": "hello\\nworld",
  "multiLine3": "hello\\r\\nuniverse\\r\\n"
}

Python transform:

# must escape the escape characters, so each slash is doubled
# Some MySQL Python libraries also have an escape[] or escape_string[] method.
as_json = json.dumps[payload] \
    .replace["'", "''"] \
    .replace['\\', '\\\\']

Full example

import json
import yaml

from DataAccessLayer.mysql_va import get_sql_client, DBConn

client = get_sql_client[]

def encode_and_store[payload]:
    as_json = json.dumps[payload] \
        .replace["'", "''"] \
        .replace['\\', '\\\\']

    query = """\
            UPDATE json_sandbox
            SET data = '{}'
            WHERE id = 1;
        """.format[as_json]

    with DBConn[*client.conn_args] as c:
        c.cursor.execute[query]
        c.connection.commit[]

    return

def encode_and_store_2[payload]:
    as_json = json.dumps[payload]

    query = """\
            UPDATE json_sandbox
            SET data = %s
            WHERE id = %s;
        """

    with DBConn[*client.conn_args] as c:
        c.cursor.execute[query, [as_json, 1]]
        c.connection.commit[]

    return


def retrieve_and_decode[]:
    query = """
        SELECT * FROM json_sandbox
        WHERE id = 1
    """

    with DBConn[*client.conn_args] as cnx:
        cursor = cnx.dict_cursor
        cursor.execute[query]

        rows = cursor.fetchall[]


    as_json = rows[0].get['data']

    payload = yaml.safe_load[as_json]
    return payload



if __name__ == '__main__':

    payload = {
        "abc": 123,
        "quotes": "ain't it great",
        "multiLine1": "hello\nworld",
        "multiLine2": """
            hello
            world
        """,
        "multiLine3": "hello\r\nuniverse\r\n"
    }


    encode_and_store[payload]
    output_a = retrieve_and_decode[]

    encode_and_store_2[payload]
    output_b = retrieve_and_decode[]

    print["original: {}".format[payload]]
    print["method_a: {}".format[output_a]]
    print["method_b: {}".format[output_b]]

    print['']
    print[output_a['multiLine1']]

    print['']
    print[output_b['multiLine2']]

    print['\nAll Equal?: {}'.format[payload == output_a == output_b]]

Escape Characters

To insert characters that are illegal in a string, use an escape character.

An escape character is a backslash \ followed by the character you want to insert.

An example of an illegal character is a double quote inside a string that is surrounded by double quotes:

Example

You will get an error if you use double quotes inside a string that is surrounded by double quotes:

txt = "We are the so-called "Vikings" from the north."

Try it Yourself »

To fix this problem, use the escape character \":

Example

The escape character allows you to use double quotes when you normally would not be allowed:

txt = "We are the so-called \"Vikings\" from the north."

Try it Yourself »

Other escape characters used in Python:

How do you escape special characters in Python?

How do I pass special characters in SQL query?

Does Python have escape characters?

What are the 5 escape sequences in Python?