黄兵的个人博客

MySQLdb 单引号导致执行SQL出现错误

最近在使用MySQLdb执行SQL语句的时候出现了如下错误：

[42000][1064] You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'an','亚洲','AS','中国','CN','CNY','','CNC Group CHINA169 Shandong Province N' at line 3

出现问题的原因：

是由于插入SQL中的一些字符串出现了单引号（'），导致SQL被截断，出现了问题。

下面是插入的数据：

INSERT INTO ip_query_result 
    (ip, ip_as, asname, city, continent, continent_code, country, country_code, currency, district, isp, lat, lon, mobile, org, proxy, region, region_name, reverse, time_zone_id, area_zip)
VALUES
    ('221.0.34.231','AS4837 CHINA UNICOM China169 Backbone','CHINA169-Backbone','Tai'an','亚洲','AS','中国','CN','CNY','','CNC Group CHINA169 Shandong Province Network','36.1853','117.12','0','','0','SD','山东省','','1','');

这里出现问题的地方在tai'an，这里本来应该是一个结果，结果由于中间存在单引号（'），导致被截断，相当于两个结果，所以才出现了上述问题。

解决方案：

再插入SQL语句的时候使用MySQLdb.escape_string()进行转义，具体示例代码如下：

with closing(mysql_conn) as conn_mysql:
    with closing(conn_mysql.cursor()) as cur:
        # 插入SQL
        ip_info_sql = "INSERT INTO ip_query_result (ip, ip_as, asname, city, continent, continent_code, country, country_code, currency, district, isp, lat, lon, mobile, org, proxy, region, region_name, reverse, time_zone_id, area_zip)" \
                      " VALUES ('{ip}','{ip_as}','{asname}','{city}','{continent}','{continent_code}','{country}','{country_code}','{currency}','{district}','{isp}','{lat}','{lon}','{mobile}','{org}','{proxy}','{region}','{region_name}','{reverse}','{time_zone_id}','{area_zip}');".format(
            ip=ip, ip_as=get_as, asname=MySQLdb.escape_string(get_asname), city=MySQLdb.escape_string(get_city), continent=get_continent,
            continent_code=get_continentCode, country=get_country, country_code=get_countryCode,
            currency=get_currency, district=get_district, isp=MySQLdb.escape_string(get_isp),
            lat=get_lat, lon=get_lon, mobile=get_mobile, org=MySQLdb.escape_string(get_org), proxy=get_proxy, region=get_region,
            region_name=MySQLdb.escape_string(get_regionName), reverse=get_reverse, time_zone_id=get_time_zone_id, area_zip=get_zip)
        try:
            # 执行SQL语句，此处需要转义，否则有一些字符无法存储
            cur.execute(ip_info_sql)
            # 提交到数据库执行
            conn_mysql.commit()
        except (MySQLdb.Error, MySQLdb.Warning) as e:
            logging.error(' 执行保存IP查询结果SQL命令的时候出现错误，具体错误内容： {error_message}'.format(error_message=e))
            # 发生错误时回滚
            conn_mysql.rollback()

下面给一个MySQLdb.escape_string()转义的案例：

>>> import MySQLdb
>>> example = r"""I don't like "special" chars ¯\_(ツ)_/¯"""
>>> example
'I don\'t like "special" chars \xc2\xaf\\_(\xe3\x83\x84)_/\xc2\xaf'
>>> MySQLdb.escape_string(example)
'I don\\\'t like \\"special\\" chars \xc2\xaf\\\\_(\xe3\x83\x84)_/\xc2\xaf'

使用MySQLdb.escape_string任然存在一些问题，如果插入的字段非常多，你也不清楚那些是需要转义的，为以后的事故埋下了隐患。

使用下面的写法，经过项目测试，可以很好的杜绝上面问题，代码示例：

with closing(mysql_conn) as conn_mysql:
    with closing(conn_mysql.cursor()) as cur:
        # 插入SQL
        ip_info_sql = "INSERT INTO ip_query_result (ip, ip_as, asname, city, continent, continent_code, country, country_code, currency, district, isp, lat, lon, mobile, org, proxy, region, region_name, reverse, time_zone_id, area_zip)" \
                      " VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s);"
        try:
            # 执行SQL语句，此处需要转义，否则有一些字符无法存储
            cur.execute(ip_info_sql, (
                ip, get_as, get_asname, get_city, get_continent, get_continentCode, get_country,
                get_countryCode,
                get_currency, get_district, get_isp, get_lat, get_lon, get_mobile, get_org, get_proxy,
                get_region,
                get_regionName, get_reverse, get_time_zone_id, get_zip))
            # 提交到数据库执行
            conn_mysql.commit()
        except (MySQLdb.Error, MySQLdb.Warning) as e:
            logging.error(' 执行保存IP查询结果SQL命令的时候出现错误，具体错误内容： {error_message}'.format(error_message=e))
            # 发生错误时回滚
            conn_mysql.rollback()

也就是这种写法：

sql = "INSERT INTO TABLE_A (COL_A,COL_B) VALUES (%s, %s)"
cursor.execute(sql, (val1, val2))

参考资料：

1、Escape string Python for MySQL

1、Mysqldb的sql语句转义问题

黄兵个人博客原创。

转载请注明出处：黄兵个人博客 - MySQLdb 单引号导致执行SQL出现错误