摘要:簡(jiǎn)述是中操作的模塊,其使用方法和幾乎相同。但目前支持而后者不支持版本。因此要避免這種情況需使用提供的參數(shù)化查詢。使用存儲(chǔ)過(guò)程動(dòng)態(tài)執(zhí)行防注入使用存儲(chǔ)過(guò)程自動(dòng)提供防注入,動(dòng)態(tài)傳入到存儲(chǔ)過(guò)程執(zhí)行語(yǔ)句。
簡(jiǎn)述
pymsql是Python中操作MySQL的模塊,其使用方法和MySQLdb幾乎相同。但目前pymysql支持python3.x而后者不支持3.x版本。一、安裝
本文測(cè)試python版本:3.5.2。mysql版本:5.7.18
pip install pymysql二、使用操作 1. 執(zhí)行SQL
#!/usr/bin/env python # _*_ coding:utf-8 _*_ __author__ = "junxi" import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8") # 創(chuàng)建游標(biāo), 查詢數(shù)據(jù)默認(rèn)為元組類型 cursor = conn.cursor() # 執(zhí)行SQL,并返回收影響行數(shù) row1 = cursor.execute("update users set password = "123"") print(row1) # 執(zhí)行SQL,并返回受影響行數(shù) row2 = cursor.execute("update users set password = "456" where id > %s", (1,)) print(row2) # 執(zhí)行SQL,并返回受影響行數(shù)(使用pymysql的參數(shù)化語(yǔ)句防止SQL注入) row3 = cursor.executemany("insert into users(username, password, email)values(%s, %s, %s)", [("ceshi3", "333", "ceshi3@11.com"), ("ceshi4", "444", "ceshi4@qq.com")]) print(row3) # 提交,不然無(wú)法保存新建或者修改的數(shù)據(jù) conn.commit() # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close()
提示:存在中文的時(shí)候,連接需要添加charset="utf8",否則中文顯示亂碼。
2、獲取查詢數(shù)據(jù)#!/usr/bin/env python # _*_ coding:utf-8 _*_ __author__ = "junxi" import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8") # 創(chuàng)建游標(biāo), 查詢數(shù)據(jù)默認(rèn)為元組類型 cursor = conn.cursor() cursor.execute("select * from users") # 獲取第一行數(shù)據(jù) row_1 = cursor.fetchone() print(row_1) # 獲取前n行數(shù)據(jù) row_n = cursor.fetchmany(3) print(row_n) # 獲取所有數(shù)據(jù) row_3 = cursor.fetchall() print(row_3) # 提交,不然無(wú)法保存新建或者修改的數(shù)據(jù) conn.commit() # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close()
3、獲取新創(chuàng)建數(shù)據(jù)自增ID
可以獲取到最新自增的ID,也就是最后插入的一條數(shù)據(jù)ID
#!/usr/bin/env python # _*_ coding:utf-8 _*_ __author__ = "junxi" import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8") # 創(chuàng)建游標(biāo), 查詢數(shù)據(jù)默認(rèn)為元組類型 cursor = conn.cursor() cursor.executemany("insert into users(username, password, email)values(%s, %s, %s)", [("ceshi3", "333", "ceshi3@11.com"), ("ceshi4", "444", "ceshi4@qq.com")]) new_id = cursor.lastrowid print(new_id) # 提交,不然無(wú)法保存新建或者修改的數(shù)據(jù) conn.commit() # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close()4、移動(dòng)游標(biāo)
操作都是靠游標(biāo),那對(duì)游標(biāo)的控制也是必須的
注:在fetch數(shù)據(jù)時(shí)按照順序進(jìn)行,可以使用cursor.scroll(num,mode)來(lái)移動(dòng)游標(biāo)位置,如: cursor.scroll(1,mode="relative") # 相對(duì)當(dāng)前位置移動(dòng) cursor.scroll(2,mode="absolute") # 相對(duì)絕對(duì)位置移動(dòng)5、fetch數(shù)據(jù)類型
關(guān)于默認(rèn)獲取的數(shù)據(jù)是元組類型,如果想要或者字典類型的數(shù)據(jù),即:
import pymysql # 創(chuàng)建連接 conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8") # 游標(biāo)設(shè)置為字典類型 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) # 左連接查詢 r = cursor.execute("select * from users as u left join articles as a on u.id = a.user_id where a.user_id = 2") result = cursor.fetchall() print(result) # 查詢一個(gè)表的所有字段名 c = cursor.execute("SHOW FULL COLUMNS FROM users FROM blog") cc = cursor.fetchall() # 提交,不然無(wú)法保存新建或者修改的數(shù)據(jù) conn.commit() # 關(guān)閉游標(biāo) cursor.close() # 關(guān)閉連接 conn.close()
查看運(yùn)行結(jié)果:
[{"user_id": 2, "id": 2, "password": "456", "email": "xinlei2017@test.com", "a.id": 2, "content": "成名之路", "title": "星光大道", "username": "tangtang"}]
6、調(diào)用存儲(chǔ)過(guò)程
a、調(diào)用無(wú)參存儲(chǔ)過(guò)程
#! /usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8") #游標(biāo)設(shè)置為字典類型 cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) #無(wú)參數(shù)存儲(chǔ)過(guò)程 cursor.callproc("p2") #等價(jià)于cursor.execute("call p2()") row_1 = cursor.fetchone() print row_1 conn.commit() cursor.close() conn.close()
b、調(diào)用有參存儲(chǔ)過(guò)程
#! /usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8") cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) cursor.callproc("p1", args=(1, 22, 3, 4)) #獲取執(zhí)行完存儲(chǔ)的參數(shù),參數(shù)@開(kāi)頭 cursor.execute("select @p1,@_p1_1,@_p1_2,@_p1_3") # {u"@_p1_1": 22, u"@p1": None, u"@_p1_2": 103, u"@_p1_3": 24} row_1 = cursor.fetchone() print row_1 conn.commit() cursor.close() conn.close()三、關(guān)于pymysql防注入 1、字符串拼接查詢,造成注入
正常查詢語(yǔ)句:
#! /usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8") cursor = conn.cursor() username = "ceshi1" password = "ceshi1passwd" # 正常構(gòu)造語(yǔ)句的情況 sql = "select username, password from users where user="%s" and pass="%s"" % (username, password) # sql = select username, password from users where user="ceshi1" and pass="ceshi1passwd" row_count = cursor.execute(sql) row_1 = cursor.fetchone() print row_count, row_1 conn.commit() cursor.close() conn.close()
構(gòu)造注入語(yǔ)句:
#! /usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8") cursor = conn.cursor() username = "u1" or "1"-- " password = "u1pass" sql="select username, password from users where username="%s" and password="%s"" % (username, password) # 拼接語(yǔ)句被構(gòu)造成下面這樣,永真條件,此時(shí)就注入成功了。因此要避免這種情況需使用pymysql提供的參數(shù)化查詢。 # select user,pass from tb7 where user="u1" or "1"-- " and pass="u1pass" row_count = cursor.execute(sql) row_1 = cursor.fetchone() print row_count,row_1 conn.commit() cursor.close() conn.close()2、避免注入,使用pymysql提供的參數(shù)化語(yǔ)句
正常參數(shù)化查詢
#! /usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8") cursor = conn.cursor() username="u1" password="u1pass" #執(zhí)行參數(shù)化查詢 row_count=cursor.execute("select username,password from tb7 where username=%s and password=%s",(username,password)) row_1 = cursor.fetchone() print row_count,row_1 conn.commit() cursor.close() conn.close()
構(gòu)造注入,參數(shù)化查詢注入失敗。
#! /usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8") cursor = conn.cursor() username="u1" or "1"-- " password="u1pass" #執(zhí)行參數(shù)化查詢 row_count=cursor.execute("select username,password from users where username=%s and password=%s",(username,password)) #內(nèi)部執(zhí)行參數(shù)化生成的SQL語(yǔ)句,對(duì)特殊字符進(jìn)行了加轉(zhuǎn)義,避免注入語(yǔ)句生成。 # sql=cursor.mogrify("select username,password from users where username=%s and password=%s",(username,password)) # print sql #select username,password from users where username="u1" or "1"-- " and password="u1pass"被轉(zhuǎn)義的語(yǔ)句。 row_1 = cursor.fetchone() print row_count,row_1 conn.commit() cursor.close() conn.close()
結(jié)論:excute執(zhí)行SQL語(yǔ)句的時(shí)候,必須使用參數(shù)化的方式,否則必然產(chǎn)生SQL注入漏洞。
3、使用存mysql儲(chǔ)過(guò)程動(dòng)態(tài)執(zhí)行SQL防注入使用MYSQL存儲(chǔ)過(guò)程自動(dòng)提供防注入,動(dòng)態(tài)傳入SQL到存儲(chǔ)過(guò)程執(zhí)行語(yǔ)句。 delimiter DROP PROCEDURE IF EXISTS proc_sql CREATE PROCEDURE proc_sql ( in nid1 INT, in nid2 INT, in callsql VARCHAR(255) ) BEGIN set @nid1 = nid1; set @nid2 = nid2; set @callsql = callsql; PREPARE myprod FROM @callsql; -- PREPARE prod FROM "select * from users where nid>? and nid"; 傳入的值為字符串,?為占位符 -- 用@p1,和@p2填充占位符 EXECUTE myprod USING @nid1,@nid2; DEALLOCATE prepare myprod; END delimiter ; set @nid1=12; set @nid2=15; set @callsql = "select * from users where nid>? and nid"; CALL proc_sql(@nid1,@nid2,@callsql)
pymsql中調(diào)用
#! /usr/bin/env python # -*- coding:utf-8 -*- import pymysql conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8") cursor = conn.cursor() sql1="select * from users where nid>? and nid" cursor.callproc("proc_sql", args=(11, 15, sql1)) rows = cursor.fetchall() print rows conn.commit() cursor.close() conn.close()四、使用with簡(jiǎn)化連接過(guò)程
# 使用with簡(jiǎn)化連接過(guò)程,每次都連接關(guān)閉很麻煩,使用上下文管理,簡(jiǎn)化連接過(guò)程 import pymysql import contextlib # 定義上下文管理器,連接后自動(dòng)關(guān)閉連接 @contextlib.contextmanager def mysql(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8"): conn = pymysql.connect(host=host, port=port, user=user, passwd=passwd, db=db, charset=charset) cursor = conn.cursor(cursor=pymysql.cursors.DictCursor) try: yield cursor finally: conn.commit() cursor.close() conn.close() # 執(zhí)行sql with mysql() as cursor: # 左連接查詢 r = cursor.execute("select * from users as u left join articles as a on u.id = a.user_id where a.user_id = 2") result = cursor.fetchall() print(result)
查看運(yùn)行結(jié)果:
[{"title": "星光大道", "username": "tangtang", "user_id": 2, "email": "xinlei3166@126.com", "a.id": 2, "content": "成名之路", "password": "456", "id": 2}]
文章版權(quán)歸作者所有,未經(jīng)允許請(qǐng)勿轉(zhuǎn)載,若此文章存在違規(guī)行為,您可以聯(lián)系管理員刪除。
轉(zhuǎn)載請(qǐng)注明本文地址:http://specialneedsforspecialkids.com/yun/41636.html
摘要:一積累中如何快速查看包中的源碼最常用的大開(kāi)發(fā)快捷鍵技巧將對(duì)象保存到文件中從文件中讀取對(duì)象中的用法的配置詳解和代碼的格式詳解格式化內(nèi)容設(shè)置生成詳解注釋規(guī)范中設(shè)置內(nèi)存調(diào)試的小知識(shí)單步執(zhí)行命令的區(qū)別的動(dòng)態(tài)代理機(jī)制詳解內(nèi)容有瑕疵,樓指正泛型繼承的幾 一、積累 1.JAVA Eclipse中如何快速查看jar包中 的class源碼 最常用的15大Eclipse開(kāi)發(fā)快捷鍵技巧 Java將對(duì)象保存到...
摘要:一積累中如何快速查看包中的源碼最常用的大開(kāi)發(fā)快捷鍵技巧將對(duì)象保存到文件中從文件中讀取對(duì)象中的用法的配置詳解和代碼的格式詳解格式化內(nèi)容設(shè)置生成詳解注釋規(guī)范中設(shè)置內(nèi)存調(diào)試的小知識(shí)單步執(zhí)行命令的區(qū)別的動(dòng)態(tài)代理機(jī)制詳解內(nèi)容有瑕疵,樓指正泛型繼承的幾 一、積累 1.JAVA Eclipse中如何快速查看jar包中 的class源碼 最常用的15大Eclipse開(kāi)發(fā)快捷鍵技巧 Java將對(duì)象保存到...
閱讀 2054·2021-10-08 10:04
閱讀 3079·2021-09-22 10:02
閱讀 2226·2019-08-30 15:56
閱讀 825·2019-08-30 15:54
閱讀 921·2019-08-30 15:54
閱讀 1276·2019-08-30 15:53
閱讀 2508·2019-08-30 11:21
閱讀 3557·2019-08-30 10:56