python - 由于字符串中的单引号或双引号，导致SQL语法错误

sql = "INSERT IGNORE INTO users_time_zone (user_id, email, username, gcm_reg_id, time_zone) " \
                  "VALUES(%s, '%s', '%s', '%s', '%s') ON DUPLICATE KEY UPDATE gcm_reg_id ='%s', id=LAST_INSERT_ID(id) " % \
                  (user_data['user_id'], user_data['email'], user_data['username'],
                   user_data['gcm_reg_id'], user_data['time_zone'], user_data['gcm_reg_id'])
            print sql
            cursor = conn.cursor()
            cursor.execute(sql)
        conn.commit()

有时用户名就像dinda_a'yunin或dinda_a“ yunin一样，这使得sql像这样

INSERT IGNORE INTO users_time_zone (user_id, email, username, gcm_reg_id, time_zone) VALUES(4298849, 'dinda_a'[email protected]', 'dinda_ayunin', 'APA91bGRvg74', 'Asia/Jakarta') ON DUPLICATE KEY UPDATE gcm_reg_id ='APA91bGRvg74', id=LAST_INSERT_ID(id)

无论如何，这使得语法有错误来解决

最佳答案

您的代码容易受到SQL Injection的攻击...

您要么想使用prepared statement：

sql = "INSERT IGNORE INTO users_time_zone (user_id, email, username, gcm_reg_id, time_zone) " \
      "VALUES(%s, %s, %s, %s, %s) ON DUPLICATE KEY UPDATE gcm_reg_id =%s, id=LAST_INSERT_ID(id) "

data = (user_data['user_id'], user_data['email'], user_data['username'],
        user_data['gcm_reg_id'], user_data['time_zone'], user_data['gcm_reg_id'])

cursor = conn.cursor()
cursor.execute(sql, data)

或escape data manually：

data = list(conn.escape_string, data)

sql = "INSERT IGNORE INTO users_time_zone (user_id, email, username, gcm_reg_id, time_zone) " \
      "VALUES(%s, '%s', '%s', '%s', '%s') ON DUPLICATE KEY UPDATE gcm_reg_id ='%s', id=LAST_INSERT_ID(id) "\
        % data

关于python - 由于字符串中的单引号或双引号，导致SQL语法错误，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/30046868/