摘要：簡述是中操作的模塊，其使用方法和幾乎相同。但目前支持而后者不支持版本。因此要避免這種情況需使用提供的參數化查詢。使用存儲過程動態執行防注入使用存儲過程自動提供防注入，動態傳入到存儲過程執行語句。

pymsql是Python中操作MySQL的模塊，其使用方法和MySQLdb幾乎相同。但目前pymysql支持python3.x而后者不支持3.x版本。
本文測試python版本：3.5.2。mysql版本：5.7.18

pip install pymysql

#!/usr/bin/env python
# _*_ coding:utf-8 _*_
__author__ = "junxi"

import pymysql

# 創建連接
conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8")

# 創建游標, 查詢數據默認為元組類型
cursor = conn.cursor()


# 執行SQL，并返回收影響行數
row1 = cursor.execute("update users set password = "123"")
print(row1)
# 執行SQL，并返回受影響行數
row2 = cursor.execute("update users set password = "456" where id > %s", (1,))
print(row2)
# 執行SQL，并返回受影響行數（使用pymysql的參數化語句防止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)

# 提交，不然無法保存新建或者修改的數據
conn.commit()
# 關閉游標
cursor.close()
# 關閉連接
conn.close()

提示：存在中文的時候，連接需要添加charset="utf8"，否則中文顯示亂碼。

#!/usr/bin/env python
# _*_ coding:utf-8 _*_
__author__ = "junxi"

import pymysql

# 創建連接
conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8")

# 創建游標, 查詢數據默認為元組類型
cursor = conn.cursor()
cursor.execute("select * from users")

# 獲取第一行數據
row_1 = cursor.fetchone()
print(row_1)
# 獲取前n行數據
row_n = cursor.fetchmany(3)
print(row_n)
# 獲取所有數據
row_3 = cursor.fetchall()
print(row_3)


# 提交，不然無法保存新建或者修改的數據
conn.commit()
# 關閉游標
cursor.close()
# 關閉連接
conn.close()

3、獲取新創建數據自增ID
可以獲取到最新自增的ID，也就是最后插入的一條數據ID

#!/usr/bin/env python
# _*_ coding:utf-8 _*_
__author__ = "junxi"

import pymysql

# 創建連接
conn = pymysql.connect(host="127.0.0.1", port=3306, user="blog", passwd="123456", db="blog", charset="utf8")

# 創建游標, 查詢數據默認為元組類型
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)


# 提交，不然無法保存新建或者修改的數據
conn.commit()
# 關閉游標
cursor.close()
# 關閉連接
conn.close()

操作都是靠游標，那對游標的控制也是必須的

注：在fetch數據時按照順序進行，可以使用cursor.scroll(num,mode)來移動游標位置，如：
 
cursor.scroll(1,mode="relative") # 相對當前位置移動
cursor.scroll(2,mode="absolute") # 相對絕對位置移動

關于默認獲取的數據是元組類型，如果想要或者字典類型的數據，即：

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)
# 左連接查詢
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)

# 查詢一個表的所有字段名
c = cursor.execute("SHOW FULL COLUMNS FROM users FROM blog")
cc = cursor.fetchall()


# 提交，不然無法保存新建或者修改的數據
conn.commit()
# 關閉游標
cursor.close()
# 關閉連接
conn.close()

查看運行結果：

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

6、調用存儲過程

a、調用無參存儲過程

#! /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("p2")  #等價于cursor.execute("call p2()")
 
row_1 = cursor.fetchone()
print row_1
 
 
conn.commit()
cursor.close()
conn.close()

b、調用有參存儲過程

#! /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))
#獲取執行完存儲的參數,參數@開頭
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()

正常查詢語句：

#! /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"
# 正常構造語句的情況
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()

構造注入語句：

#! /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)
 
# 拼接語句被構造成下面這樣，永真條件，此時就注入成功了。因此要避免這種情況需使用pymysql提供的參數化查詢。
# 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()

正常參數化查詢

#! /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"
#執行參數化查詢
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()

構造注入，參數化查詢注入失敗。

#! /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"
#執行參數化查詢
row_count=cursor.execute("select username,password from users where username=%s and password=%s",(username,password))
#內部執行參數化生成的SQL語句，對特殊字符進行了加轉義，避免注入語句生成。
# 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"被轉義的語句。
 
row_1 = cursor.fetchone()
print row_count,row_1
 
conn.commit()
cursor.close()
conn.close()

結論：excute執行SQL語句的時候，必須使用參數化的方式，否則必然產生SQL注入漏洞。

使用MYSQL存儲過程自動提供防注入，動態傳入SQL到存儲過程執行語句。
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? and nid
pymsql中調用
#! /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
四、使用with簡化連接過程
# 使用with簡化連接過程，每次都連接關閉很麻煩，使用上下文管理，簡化連接過程
import pymysql
import contextlib


# 定義上下文管理器，連接后自動關閉連接
@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()

# 執行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)
查看運行結果：
[{"title": "星光大道", "username": "tangtang", "user_id": 2, "email": "xinlei3166@126.com", "a.id": 2, "content": "成名之路", "password": "456", "id": 2}]