Python使用MySQL Connector更新数据时单引号报错的解决咨询

Ah, I’ve run into this exact issue before—those pesky apostrophes breaking SQL queries are a classic! The first thing you need to know: never try to manually strip or replace apostrophes (like turning O'Neil into ONeil or even O''Neil). Not only is that error-prone, but it leaves you wide open to SQL injection attacks, which is a huge security risk.

The Right Solution: Parameterized Queries #

MySQL Connector supports parameterized queries using %s as a placeholder for values (note: this isn’t the same as Python’s string formatting %s—it’s a database-specific placeholder). Here’s how to implement it:

Bad (Error-Prone & Unsafe) Code #

This is probably what you’re doing now, directly concatenating user input into your SQL string:

import mysql.connector

db = mysql.connector.connect(
    host="your_host",
    user="your_user",
    password="your_password",
    database="your_db"
)
cursor = db.cursor()

# User input with apostrophe causes syntax errors
user_input = "O'Neil"
# This will throw an error because of the unescaped apostrophe
query = f"UPDATE your_table SET column_name = '{user_input}' WHERE id = 1"
cursor.execute(query)

Good (Safe & Error-Free) Code #

Use parameterized queries instead—pass your user input as a separate argument to execute():

import mysql.connector

db = mysql.connector.connect(
    host="your_host",
    user="your_user",
    password="your_password",
    database="your_db"
)
cursor = db.cursor()

user_input = "O'Neil"
# Use %s as placeholders for values
query = "UPDATE your_table SET column_name = %s WHERE id = %s"
# Pass parameters as a tuple to execute()
cursor.execute(query, (user_input, 1))

# Don't forget to commit the changes!
db.commit()

This approach tells MySQL Connector to automatically escape any special characters (like apostrophes) in your user input, so you won’t get syntax errors. Even better, it completely eliminates the risk of SQL injection.

A Less Ideal Alternative: Escaping Strings Manually #

If for some reason you can’t use parameterized queries (though I strongly recommend sticking with them), you can use MySQL Connector’s escape_string() method to safely escape apostrophes:

from mysql.connector import escape_string

user_input = "O'Neil"
escaped_input = escape_string(user_input)
query = f"UPDATE your_table SET column_name = '{escaped_input}' WHERE id = 1"
cursor.execute(query)

Again, this is a backup option—parameterized queries are always the safer, cleaner choice.

Key Takeaway #

Always use parameterized queries when working with user input and databases. It solves your apostrophe problem and keeps your application secure.

内容的提问来源于stack exchange，提问作者Joost Luijben