SQL 语句包含“”时出现问题或'

Question

我使用 python 将 HTML 存储在 SQLite3 数据库中。当我将一些 HTML 插入到 SQL 表中时，当我有“”字符时，我会遇到错误/问题。

我的问题的以下示例创建了不正确的 SQL 语句：

INSERT INTO mytable(id, html, other) VALUE(1, " <img src="images/1.png" alt=""/> ", "some other info")
# it should be something like
INSERT INTO mytable(id, html, other) VALUE(1, " <img src=\"images/1.png\" alt=\"\"/> ", "some other info")

如何获得包含“字符的 SQL 语句？

import sqlite3

HTML       = """ <img src="images/1.png" alt=""/> """ # NOTE the " characters in it
insert_qry = """ INSERT INTO mytable(id, html, other) VALUE(%s, "%s", "%s")"""

conn = sqlite3.connect( GlobalVars.db_path )
cur  = conn.cursor()
res  = cur.execute( insert_qry % (1, HTML, "some other info") )


# THESE FUNCTIONS DONT SOLVE MY PROBLEM:
def format_for_DB( src_code ):
    """ Post: """

    src_code = src_code.replace( '"', '\"' )
    src_code = src_code.replace( "'", "\'" )
    return src_code


def format_for_display( src_code ):
    """ Post: """

    src_code = src_code.replace( '\"', '"' )
    src_code = src_code.replace( "\'", "'" )
    return src_code

def format_for_DB( src_code ):
    """ Post: """

    src_code = src_code.replace( '"', '""' )
    src_code = src_code.replace( "'", "''" )
    return src_code


def format_for_display( src_code ):
    """ Post: """

    src_code = src_code.replace( '""', '"' )
    src_code = src_code.replace( "''", "'" )
    return src_code

Answer 1

使用参数化查询：

query =  """ INSERT INTO mytable(id, html, other) VALUES(?, ?, ?) """

# ...

cur.execute(query, (1, HTML, "some other info"))