tornado连接mysql数据库与pymysql的简单操作

reference:

https://blog.csdn.net/runner668/article/details/80302073?ops_request_misc=%257B%2522request%255Fid%2522%253A%2522160765480619724838545315%2522%252C%2522scm%2522%253A%252220140713.130102334.pc%255Fblog.%2522%257D&request_id=160765480619724838545315&biz_id=0&utm_medium=distribute.pc_search_result.none-task-blog-2~blog~first_rank_v2~rank_v29-1-80302073.pc_v2_rank_blog_default&utm_term=%C2%A0tornado%E8%BF%9E%E6%8E%A5%E6%95%B0%E6%8D%AE%E5%BA%93mysql&spm=1018.2118.3001.4450

 本人的python是3.5,由于3.0后用的是pymysql,就不能用tornado自带的torndb来进行简单的连接操作。

Application这个类是初始化一些全局变量,按照道理说里边的self.db 也应该能够被其他类或者派生类调用的,但是

db这个属性就是不行,无奈只好创建了一个全局的db句柄,然后在HouseHandler类中根据这个db初始化一个实例。

当然要在Aplication中传入这个字典参数:dict(db=db)

 
  1.  
  2. # -*- coding: utf-8 -*-

  3. """

  4. Created on Wed Mar 21 16:49:24 2018

  5.  
  6.  
  7. @author: ming

  8. """

  9.  
  10.  
  11. # coding:utf-8

  12. import os

  13. import tornado.web

  14. import tornado.ioloop

  15. import tornado.httpserver

  16. import tornado.options

  17. from tornado.options import options, define

  18. from tornado.web import RequestHandler

  19. #import torndb

  20. import pymysql

  21. '''

  22. python 2用torndb

  23. '''

  24.  
  25.  
  26. define("port", default=8000, type=int, help="run server on the given port.")

  27. db =  pymysql.Connection(host='127.0.0.1', database='mysql', user='root', password='0000',charset='utf8') 

  28. class HouseHandler(RequestHandler):

  29.     def initialize(self, db):

  30.         self.db = db

  31.         print(1)

  32.     def get(self):

  33.         #db = self.db

  34.         cur=db.cursor()

  35.         print(type(cur))

  36.         try:

  37.              cur.execute("insert into houses(title, position, price, score, comments) values(%s, %s, %s, %s, %s)", ('独立装修小别 墅', '紧邻文津街', 280, 4, 128) )

  38.         except Exception as e:

  39.             return self.write('cuo wu')

  40.         db.commit()

  41.         print("success")

  42.         cur.close()

  43.    # db.close()

  44.             

  45.  #self.write({"error":0,"errmsg":"db ok","data":[]})

  46. #这个类把登录信息进行了绑定,保证连接的时候只实例化一次

  47. class Application(tornado.web.Application): 

  48.     def _init_(self,*args,**kwargs):

  49.         self.a =1;

  50.         

  51.         super(Application,self)._init_(*args,**kwargs)

  52.         #img_files = files.get('img')

  53.         '''try:

  54.             self.db =  pymysql.Connection(host='127.0.0.1', database='mysql', user='root', password='0000') 

  55.  
  56.         except Exception as e:

  57.             #发生错误就不往下执行,而是向前端返回出错信息

  58.          return self.write("haha")'''

  59.         print("hahaaa")

  60.               

  61.               

  62. settings = dict(

  63.           template_path=os.path.join(os.path.dirname(__file__), "templates"),

  64.           static_path=os.path.join(os.path.dirname(__file__), "statics"),

  65.           debug=True, 

  66.           )

  67. if __name__ == "__main__":

  68.     tornado.options.parse_command_line()

  69.     app = Application([

  70.         #(r"/", IndexHandler),

  71.         (r"/house", HouseHandler,dict(db=db)),

  72.     ],**settings)

  73.     http_server = tornado.httpserver.HTTPServer(app)

  74.     http_server.listen(options.port)

  75.     tornado.ioloop.IOLoop.current().start()

二:pymysql的简单操作

     (1)网上有个模拟注入攻击的例子

tornado连接mysql数据库与pymysql的简单操作

在这个实例中不是创建一个全局的连接,而是在post方法中创建一个连接,这种做法不提倡。

 

一、搭建环境

1、服务端的tornado主程序app.py如下:

 
  1. #!/usr/bin/env python3

  2. # -*- coding: utf-8 -*-

  3.  
  4. import tornado.ioloop

  5. import tornado.web

  6. import pymysql

  7.  
  8. class LoginHandler(tornado.web.RequestHandler):

  9. def get(self):

  10. self.render('login.html')

  11.  
  12. def post(self, *args, **kwargs):

  13. username = self.get_argument('username',None)

  14. pwd = self.get_argument('pwd', None)

  15.  
  16. # 创建数据库连接

  17. conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='123456', db='shop')

  18. cursor = conn.cursor()

  19.  
  20. # %s 要加上'' 否则会出现KeyboardInterrupt的错误

  21. temp = "select name from userinfo where name='%s' and password='%s'" % (username, pwd)

  22. effect_row = cursor.execute(temp)

  23. result = cursor.fetchone()

  24. conn.commit()

  25. cursor.close()

  26. conn.close()

  27.  
  28. if result:

  29. self.write('登录成功!')

  30. else:

  31. self.write('登录失败!')

  32.  
  33.  
  34. settings = {

  35. 'template_path':'template',

  36. }

  37.  
  38.  
  39. application = tornado.web.Application([

  40. (r"/login", LoginHandler),

  41. ],**settings)

  42.  
  43. if __name__ == "__main__":

  44. application.listen(8000)

  45. tornado.ioloop.IOLoop.instance().start()

2、在template文件夹下,放入login.html文件:

 
  1. <!DOCTYPE html>

  2. <html lang="en">

  3. <head>

  4. <meta charset="UTF-8">

  5. <title>Title</title>

  6. </head>

  7. <body>

  8. <form method="post" action="/login">

  9. <input type="text" name="username" placeholder="用户名"/>

  10. <input type="text" name="pwd" placeholder="密码"/>

  11. <input type="submit" value="提交" />

  12. </form>

  13. </body>

  14. </html>

3、在shop数据库中建立userinfo数据表,并填入数据:

tornado连接mysql数据库与pymysql的简单操作

随便添加两条就好,明文就明文吧:

tornado连接mysql数据库与pymysql的简单操作

二、模拟登录

1、正常登录

tornado连接mysql数据库与pymysql的简单操作
tornado连接mysql数据库与pymysql的简单操作
tornado连接mysql数据库与pymysql的简单操作
tornado连接mysql数据库与pymysql的简单操作
以上都是“好用户”的正常登录,我们看一下“坏家伙”怎么做。

2、非法登录

密码不对也能登录:

tornado连接mysql数据库与pymysql的简单操作
tornado连接mysql数据库与pymysql的简单操作

看一下服务端执行的SQL语句,就不难理解了,密码部分被注释掉了:

select name from userinfo where name='dyan' -- n' and password='000'

账户密码都不对照样登录成功:

tornado连接mysql数据库与pymysql的简单操作
tornado连接mysql数据库与pymysql的简单操作

看执行的SQL语句:

select name from userinfo where name='badguy' or 1=1 -- y' and password='000'

三、使用cursor.execute方式防止注入

使用字符串拼接的方式会导致SQL注入。在cursor.execute方法中对'导致注入的符号做了转义。

将app.py中下面两行代码改为:

 
  1. # 导致SQL注入

  2. temp = "select name from userinfo where name='%s' and password='%s'" % (username, pwd)

  3. effect_row = cursor.execute(temp)

  4.  
 
  1. # 防止SQL注入

  2. effect_row = cursor.execute("select name from userinfo where name='%s' and password='%s'",(username, pwd,))

再次尝试注入:

tornado连接mysql数据库与pymysql的简单操作
tornado连接mysql数据库与pymysql的简单操作

错误原因,巴拉巴拉就是语法不对:

ymysql.err.ProgrammingError: (1064, "You have an error in your SQL syntax;

看看内部执行的语句,主要是对'符号做了转义防止注入:

select name from userinfo where name=''dyan\' -- n'' and password=''123''

 

二、使用操作

1. 执行SQL

 
  1. #!/usr/bin/env python

  2. # _*_ coding:utf-8 _*_

  3. __author__ = 'junxi'

  4.  
  5. import pymysql

  6.  
  7. # 创建连接

  8. conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

  9.  
  10. # 创建游标, 查询数据默认为元组类型

  11. cursor = conn.cursor()

  12.  
  13.  
  14. # 执行SQL,并返回收影响行数

  15. row1 = cursor.execute("update users set password = '123'")

  16. print(row1)

  17. # 执行SQL,并返回受影响行数

  18. row2 = cursor.execute("update users set password = '456' where id > %s", (1,))

  19. print(row2)

  20. # 执行SQL,并返回受影响行数(使用pymysql的参数化语句防止SQL注入)

  21. row3 = cursor.executemany("insert into users(username, password, email)values(%s, %s, %s)", [("ceshi3", '333', 'ceshi3@11.com'), ("ceshi4", '444', 'ceshi4@qq.com')])

  22. print(row3)

  23.  
  24. # 提交,不然无法保存新建或者修改的数据

  25. conn.commit()

  26. # 关闭游标

  27. cursor.close()

  28. # 关闭连接

  29. conn.close()

  30.  

提示:存在中文的时候,连接需要添加charset='utf8',否则中文显示乱码。

2、获取查询数据

 
  1. #!/usr/bin/env python

  2. # _*_ coding:utf-8 _*_

  3. __author__ = 'junxi'

  4.  
  5. import pymysql

  6.  
  7. # 创建连接

  8. conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

  9.  
  10. # 创建游标, 查询数据默认为元组类型

  11. cursor = conn.cursor()

  12. cursor.execute("select * from users")

  13.  
  14. # 获取第一行数据

  15. row_1 = cursor.fetchone()

  16. print(row_1)

  17. # 获取前n行数据

  18. row_n = cursor.fetchmany(3)

  19. print(row_n)

  20. # 获取所有数据

  21. row_3 = cursor.fetchall()

  22. print(row_3)

  23.  
  24.  
  25. # 提交,不然无法保存新建或者修改的数据

  26. conn.commit()

  27. # 关闭游标

  28. cursor.close()

  29. # 关闭连接

  30. conn.close()

  31.  

3、获取新创建数据自增ID
可以获取到最新自增的ID,也就是最后插入的一条数据ID

 
  1. #!/usr/bin/env python

  2. # _*_ coding:utf-8 _*_

  3. __author__ = 'junxi'

  4.  
  5. import pymysql

  6.  
  7. # 创建连接

  8. conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

  9.  
  10. # 创建游标, 查询数据默认为元组类型

  11. cursor = conn.cursor()

  12.  
  13. cursor.executemany("insert into users(username, password, email)values(%s, %s, %s)", [("ceshi3", '333', 'ceshi3@11.com'), ("ceshi4", '444', 'ceshi4@qq.com')])

  14. new_id = cursor.lastrowid

  15. print(new_id)

  16.  
  17.  
  18. # 提交,不然无法保存新建或者修改的数据

  19. conn.commit()

  20. # 关闭游标

  21. cursor.close()

  22. # 关闭连接

  23. conn.close()

  24.  

4、移动游标
操作都是靠游标,那对游标的控制也是必须的

 
  1. 注:在fetch数据时按照顺序进行,可以使用cursor.scroll(num,mode)来移动游标位置,如:

  2.  
  3. cursor.scroll(1,mode='relative') # 相对当前位置移动

  4. cursor.scroll(2,mode='absolute') # 相对绝对位置移动

5、fetch数据类型
关于默认获取的数据是元组类型,如果想要或者字典类型的数据,即:

 
  1. import pymysql

  2.  
  3. # 创建连接

  4. conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

  5.  
  6. # 游标设置为字典类型

  7. cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

  8. # 左连接查询

  9. r = cursor.execute("select * from users as u left join articles as a on u.id = a.user_id where a.user_id = 2")

  10. result = cursor.fetchall()

  11. print(result)

  12.  
  13. # 查询一个表的所有字段名

  14. c = cursor.execute("SHOW FULL COLUMNS FROM users FROM blog")

  15. cc = cursor.fetchall()

  16.  
  17.  
  18. # 提交,不然无法保存新建或者修改的数据

  19. conn.commit()

  20. # 关闭游标

  21. cursor.close()

  22. # 关闭连接

  23. conn.close()

  24.  

查看运行结果:

[{'user_id': 2, 'id': 2, 'password': '456', 'email': 'xinlei2017@test.com', 'a.id': 2, 'content': '成名之路', 'title': '星光大道', 'username': 'tangtang'}]

6、调用存储过程

a、调用无参存储过程

 
  1. #! /usr/bin/env python

  2. # -*- coding:utf-8 -*-

  3.  
  4. import pymysql

  5.  
  6. conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

  7. #游标设置为字典类型

  8. cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

  9. #无参数存储过程

  10. cursor.callproc('p2') #等价于cursor.execute("call p2()")

  11.  
  12. row_1 = cursor.fetchone()

  13. print row_1

  14.  
  15.  
  16. conn.commit()

  17. cursor.close()

  18. conn.close()

  19.  

b、调用有参存储过程

 
  1. #! /usr/bin/env python

  2. # -*- coding:utf-8 -*-

  3.  
  4. import pymysql

  5.  
  6. conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

  7. cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

  8.  
  9. cursor.callproc('p1', args=(1, 22, 3, 4))

  10. #获取执行完存储的参数,参数@开头

  11. cursor.execute("select @p1,@_p1_1,@_p1_2,@_p1_3")

  12. # {u'@_p1_1': 22, u'@p1': None, u'@_p1_2': 103, u'@_p1_3': 24}

  13. row_1 = cursor.fetchone()

  14. print row_1

  15.  
  16.  
  17. conn.commit()

  18. cursor.close()

  19. conn.close()

  20.  

三、关于pymysql防注入

1、字符串拼接查询,造成注入

正常查询语句:

 
  1. #! /usr/bin/env python

  2. # -*- coding:utf-8 -*-

  3.  
  4. import pymysql

  5.  
  6. conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

  7. cursor = conn.cursor()

  8. username = "ceshi1"

  9. password = "ceshi1passwd"

  10. # 正常构造语句的情况

  11. sql = "select username, password from users where user='%s' and pass='%s'" % (username, password)

  12. # sql = select username, password from users where user='ceshi1' and pass='ceshi1passwd'

  13. row_count = cursor.execute(sql)

  14. row_1 = cursor.fetchone()

  15. print row_count, row_1

  16.  
  17. conn.commit()

  18. cursor.close()

  19. conn.close()

  20.  

构造注入语句:

 
  1. #! /usr/bin/env python

  2. # -*- coding:utf-8 -*-

  3.  
  4. import pymysql

  5.  
  6. conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

  7. cursor = conn.cursor()

  8.  
  9. username = "u1' or '1'-- "

  10. password = "u1pass"

  11. sql="select username, password from users where username='%s' and password='%s'" % (username, password)

  12.  
  13. # 拼接语句被构造成下面这样,永真条件,此时就注入成功了。因此要避免这种情况需使用pymysql提供的参数化查询。

  14. # select user,pass from tb7 where user='u1' or '1'-- ' and pass='u1pass'

  15.  
  16. row_count = cursor.execute(sql)

  17. row_1 = cursor.fetchone()

  18. print row_count,row_1

  19.  
  20.  
  21. conn.commit()

  22. cursor.close()

  23. conn.close()

  24.  

2、避免注入,使用pymysql提供的参数化语句
正常参数化查询

 
  1. #! /usr/bin/env python

  2. # -*- coding:utf-8 -*-

  3.  
  4.  
  5. import pymysql

  6.  
  7. conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

  8. cursor = conn.cursor()

  9. username="u1"

  10. password="u1pass"

  11. #执行参数化查询

  12. row_count=cursor.execute("select username,password from tb7 where username=%s and password=%s",(username,password))

  13. row_1 = cursor.fetchone()

  14. print row_count,row_1

  15.  
  16. conn.commit()

  17. cursor.close()

  18. conn.close()

  19.  

构造注入,参数化查询注入失败。

 
  1. #! /usr/bin/env python

  2. # -*- coding:utf-8 -*-

  3.  
  4. import pymysql

  5.  
  6. conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

  7. cursor = conn.cursor()

  8.  
  9. username="u1' or '1'-- "

  10. password="u1pass"

  11. #执行参数化查询

  12. row_count=cursor.execute("select username,password from users where username=%s and password=%s",(username,password))

  13. #内部执行参数化生成的SQL语句,对特殊字符进行了加\转义,避免注入语句生成。

  14. # sql=cursor.mogrify("select username,password from users where username=%s and password=%s",(username,password))

  15. # print sql

  16. #select username,password from users where username='u1\' or \'1\'-- ' and password='u1pass'被转义的语句。

  17.  
  18. row_1 = cursor.fetchone()

  19. print row_count,row_1

  20.  
  21. conn.commit()

  22. cursor.close()

  23. conn.close()

  24.  

结论:excute执行SQL语句的时候,必须使用参数化的方式,否则必然产生SQL注入漏洞。

3、使用存mysql储过程动态执行SQL防注入

 
  1. 使用MYSQL存储过程自动提供防注入,动态传入SQL到存储过程执行语句。

  2. delimiter \\

  3. DROP PROCEDURE IF EXISTS proc_sql \\

  4. CREATE PROCEDURE proc_sql (

  5. in nid1 INT,

  6. in nid2 INT,

  7. in callsql VARCHAR(255)

  8. )

  9. BEGIN

  10. set @nid1 = nid1;

  11. set @nid2 = nid2;

  12. set @callsql = callsql;

  13. PREPARE myprod FROM @callsql;

  14. -- PREPARE prod FROM 'select * from users where nid>? and nid<?'; 传入的值为字符串,?为占位符

  15. -- 用@p1,和@p2填充占位符

  16. EXECUTE myprod USING @nid1,@nid2;

  17. DEALLOCATE prepare myprod;

  18.  
  19. END\\

  20. delimiter ;

  21. set @nid1=12;

  22. set @nid2=15;

  23. set @callsql = 'select * from users where nid>? and nid<?';

  24. CALL proc_sql(@nid1,@nid2,@callsql)

  25.  

pymsql中调用

 
  1. #! /usr/bin/env python

  2. # -*- coding:utf-8 -*-

  3.  
  4. import pymysql

  5.  
  6. conn = pymysql.connect(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8')

  7. cursor = conn.cursor()

  8. sql1="select * from users where nid>? and nid<?"

  9. cursor.callproc('proc_sql', args=(11, 15, sql1))

  10.  
  11. rows = cursor.fetchall()

  12. print rows

  13. conn.commit()

  14. cursor.close()

  15. conn.close()

  16.  

四、使用with简化连接过程

 
  1. # 使用with简化连接过程,每次都连接关闭很麻烦,使用上下文管理,简化连接过程

  2. import pymysql

  3. import contextlib

  4.  
  5.  
  6. # 定义上下文管理器,连接后自动关闭连接

  7. @contextlib.contextmanager

  8. def mysql(host='127.0.0.1', port=3306, user='blog', passwd='123456', db='blog', charset='utf8'):

  9. conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset=charset)

  10. cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

  11. try:

  12. yield cursor

  13. finally:

  14. conn.commit()

  15. cursor.close()

  16. conn.close()

  17.  
  18. # 执行sql

  19. with mysql() as cursor:

  20. # 左连接查询

  21. r = cursor.execute("select * from users as u left join articles as a on u.id = a.user_id where a.user_id = 2")

  22. result = cursor.fetchall()

  23. print(result)

  24.  

查看运行结果:

[{'title': '星光大道', 'username': 'tangtang', 'user_id': 2, 'email': 'xinlei3166@126.com', 'a.id': 2, 'content': '成名之路', 'password': '456', 'id': 2}]
上一篇:Python学习之关于异常


下一篇:Oracle RAC 第二节点打补丁报错 oui-patch.xml (Permission denied)