Contents
  1. 1. 问题分析

问题分析

使用sqlalchemy的过程中,遇到写数据库的时候报语法错误,相关代码如下:

# 定义requests对象,存储HTTP请求的所有信息:
class Requests(Base):
    # 表的名字:
    __tablename__ = 'requests'
    # 表的结构:
    id = Column(Integer(), primary_key=True)
    # scheme = Column(String(20))
    method = Column(String(200))
    url = Column(String(2000))
    headers = Column(Text(20000))
    # cookies = Column(Text(20000))
    content = Column(Text(20000))
    # query = Column(Text(20000))
    timestamp_start = Column(String(2000))
    urlencoded_form=Column(Text(20000))
    multipart_form=Column(Text(20000))
    sqlmap=Column(String(20))
    xss=Column(String(20))

# 将请求信息写入数据库
def save_request(flow):
    newrequest = Requests(
            # scheme=flow.request.scheme,
            method=flow.request.method,
            url=flow.request.url,
            headers=str(flow.request.headers.__dict__),
            # headers={'fields': [['Host', 'zone.wooyun.org'], ['Proxy-Connection', 'keep-alive'], ['Cache-Control', 'max-age=0'], ['Accept', 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8'], ['Upgrade-Insecure-Requests', '1'], ['User-Agent', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2564.82 Safari/537.36'], ['Referer', 'http://zone.wooyun.org/content/24881'], ['Accept-Encoding', 'gzip, deflate, sdch'], ['Accept-Language', 'zh-CN,zh;q=0.8,en;q=0.6,zh-TW;q=0.4,fr;q=0.2'], ['Cookie', '__cfduid=dc40888efca4ab0ff91186dc39428d9451452910746; wy_uid=5216UsFxwcFbseAhJt2wKwW3D3TWpih1g71JAjeIlpCT; wy_pwd=8c96%2BGWnQmu1BRR1RePbtZrkAZ6bY8HvlPtgcggYQmaideN8%2F08EAzHsV1XKQ72ZwX7q%2BxN4FJgnfUHorw; wy_token=192c2fd47fa5d03123ced3320c7e580c; PHPSESSID=e0e6vqec509trlqu7tv1lfeu70; bdshare_firstime=1453095346906; login_type=d8d1the3gNwSvY8dSI2oIhzdLszQ8nsd2ne%2Fl6JY; __ads_session=xk9roUMxqgiiIx0CggA=; Hm_lvt_c12f88b5c1cd041a732dea597a5ec94c=1453453560; Hm_lpvt_c12f88b5c1cd041a732dea597a5ec94c=1453876852; Hm_lvt_3faf3a47435cc512f3b86dc12af100d0=1453682212; Hm_lpvt_3faf3a47435cc512f3b86dc12af100d0=1453882158'], ['AlexaToolbar-ALX_NS_PH', 'AlexaToolbar/alxg-3.3']]},
            # cookies=flow.request.cookies,
            content=flow.request.content,
            # query=flow.request.query,
            timestamp_start=flow.request.timestamp_start,
            # id=get_md5_value(flow.request.url+flow.request.content+str(flow.request.timestamp_start))
            urlencoded_form=flow.request.urlencoded_form,
            multipart_form=flow.request.multipart_form,
    )
    session = DBSession()
    session.add(newrequest)
    session.commit()
    session.close()

当写入的数据类型为字典时,报语法错误。

ProgrammingError: (_mysql_exceptions.ProgrammingError) (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 \'\'fields\': "((\'Host\',\'dongweiming.github.io\'),(\'Proxy-Connection\',\'keep-alive\'),(\' at line 1') [SQL: u'INSERT INTO requests (method, url, headers, content, timestamp_start, urlencoded_form, multipart_form, sqlmap, xss) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)'] [parameters: ('GET', 'http://dongweiming.github.io/blog/archives/how-to-use-celery/', {'fields': [['Host', 'dongweiming.github.io'], ['Proxy-Connection', 'keep-alive'], ['Cache-Control', 'max-age=0'], ['Accept', 'text/html,application/xhtml+xml,application/xml;q=0.9,image/webp,*/*;q=0.8'], ['Upgrade-Insecure-Requests', '1'], ['User-Agent', 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_11_2) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/48.0.2564.82 Safari/537.36'], ['Referer', 'https://www.google.com/'], ['Accept-Encoding', 'gzip, deflate, sdch'], ['Accept-Language', 'zh-CN,zh;q=0.8,en;q=0.6,zh-TW;q=0.4,fr;q=0.2'], ['Cookie', '__utma=221486477.1712369208.1453863719.1453863719.1453972410.2; __utmc=221486477; __utmz=221486477.1453972410.2.2.utmcsr=google|utmccn=(organic)|utmcmd=organic|utmctr=(not%20provided)'], ['AlexaToolbar-ALX_NS_PH', 'AlexaToolbar/alxg-3.3'], ['If-Modified-Since', 'Tue, 26 Jan 2016 10:36:35 GMT']]}, '', 1454034516.300893, None, None, None, None)]

一开始看到语法错误,感觉有SQL注入啊。可是sqlalchemy应该是用预编译的方式,报错信息也显示了,确实是采用了预编译的方式。

u'INSERT INTO requests (method, url, headers, content, timestamp_start, urlencoded_form, multipart_form, sqlmap, xss) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)

使用如下python代码可以记录sqlalchemy执行的sql语句。

# logging.basicConfig(filename='db.log')
# logging.getLogger('sqlalchemy.engine').setLevel(logging.INFO)

经过调试分析发现,如果传入类型为字典,就会导致

ProgrammingError: (_mysql_exceptions.ProgrammingError) (1064, 'You have an error in your SQL syntax; 

错误。

sqlalchemy的bug?是否有可能造成sql注入?

Contents
  1. 1. 问题分析