MySQL更新含引号的MEDIUM TEXT字段报错，求编码解决方案

问题场景 #

执行更新语句修改MEDIUMTEXT类型的content字段时，待上传文本包含单引号、双引号等特殊字符，导致SQL语句语法错误。原执行语句如下：

UPDATE blocks SET content = "${file}" WHERE key = "${name}"

待上传的文件内容示例：

'Date: <input type="date">';

可行解决方案 #

1. 使用参数化查询（预处理语句）—— 推荐 #

这是最安全可靠的方案，既能避免特殊字符导致的语法错误，还能防止SQL注入。几乎所有编程语言的MySQL客户端都支持参数化查询，示例如下：

Python（pymysql）示例：

import pymysql

# 建立数据库连接
conn = pymysql.connect(host='你的主机地址', user='用户名', password='密码', database='数据库名')
cursor = conn.cursor()

# 定义带占位符的SQL语句
update_sql = "UPDATE blocks SET content = %s WHERE key = %s"
# 待更新的内容和条件值
file_content = "'Date: <input type=\"date\">';"
target_key = "目标key值"

# 执行参数化查询
cursor.execute(update_sql, (file_content, target_key))
conn.commit()

# 关闭连接
cursor.close()
conn.close()

Java（JDBC）示例：

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;

public class UpdateExample {
    public static void main(String[] args) {
        String url = "jdbc:mysql://主机地址:端口/数据库名";
        String user = "用户名";
        String password = "密码";
        String fileContent = "'Date: <input type=\"date\">';";
        String targetKey = "目标key值";

        try (Connection conn = DriverManager.getConnection(url, user, password)) {
            String sql = "UPDATE blocks SET content = ? WHERE key = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, fileContent);
            pstmt.setString(2, targetKey);
            pstmt.executeUpdate();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

2. 手动转义特殊字符（不推荐） #

如果无法使用参数化查询，可以手动转义字符串中的特殊字符。MySQL中可以使用内置的QUOTE()函数，或者利用编程语言提供的转义方法：

方法一：使用MySQL的QUOTE()函数

UPDATE blocks SET content = QUOTE('待转义的内容') WHERE key = QUOTE('目标key值')

QUOTE()函数会自动给字符串添加单引号，并转义内部的单引号、反斜杠等特殊字符。

方法二：编程语言中手动转义

以Python为例，使用pymysql的escape_string()方法：

import pymysql

conn = pymysql.connect(host='你的主机地址', user='用户名', password='密码', database='数据库名')
file_content = "'Date: <input type=\"date\">';"
target_key = "目标key值"

# 转义特殊字符
escaped_content = conn.escape_string(file_content)
escaped_key = conn.escape_string(target_key)

# 拼接执行SQL
sql = f"UPDATE blocks SET content = '{escaped_content}' WHERE key = '{escaped_key}'"
cursor = conn.cursor()
cursor.execute(sql)
conn.commit()

cursor.close()
conn.close()

错误原因说明 #

直接将含特殊字符的变量拼接进SQL语句时，单引号、双引号会破坏SQL的语法结构（比如提前闭合字符串），导致数据库解析语句时出现语法错误。

内容的提问来源于stack exchange，提问作者Andrew Mongo