pymysql模块

PyMySQL 是在 Python3.x 版本中用于连接 MySQL 服务器的一个库，Python2中则使用mysqldb。

连接数据库

import pymysql

# 创建数据库连接
conn = pymysql.connect(host='172.30.100.111',port=3306,user='admin',password='123',database='python',charset='utf8')
# 创建游标
cursor = conn.cursor()

# 执行查询sql
sel_sql = "select class,teacher from users where u_id = %s and name = %s"
sel_result = cursor.execute(sel_sql, args=(5,'Alice'))
# 执行添加sql
add_sql = "insert into users(name,age,class,teacher) values('Faker',24,1,1),('iboy',19,1,2)"
add_result = cursor.execute(add_sql)
# 执行删除sql
del_sql = "delete from users where u_id = 8"
del_result = cursor.execute(del_sql)
# 执行更新sql
update_sql = "update users set name = 'Ming' where u_id = 9"
update_result = cursor.execute(update_sql)
# 增删改操作，需要提交到数据库执行
conn.commit()

# 关闭游标
cursor.close()
# 关闭数据库连接
conn.close()

游标操作

cursor.scroll(n,mode='relative')，n偏移量，mode指定相对或绝对模式。

import pymysql

conn = pymysql.connect(host='172.30.100.111',port=3306,user='admin',password='123',database='python',charset='utf8')
cursor = conn.cursor(pymysql.cursors.DictCursor)

sql = "select name,age from users where age = 28"
cursor.execute(sql)
# 以绝对模式移动游标，移至1位置
cursor.scroll(1,mode='absolute')
data = cursor.fetchone()
print(data)
# 以相对模式移动游标，向前移动1行
cursor.scroll(-1,mode='relative')
data = cursor.fetchone()
print(data)

cursor.close()
conn.close()

获取数据

执行cursor.excute('sql')后返回的是受影响的行数，并不是数据库中查询的内容；利用指定方法可以获取相关数据

fetchone()，从首行开始获取下一行的数据。返回一个元组数据。

fetchall()，获取所有行数数据。获取后，游标移动至最后。返回一个元组，其中包含每行数据的元组。

fetchmany(n)，获取n行数据。游标移动n行。

import pymysql

conn = pymysql.connect(host='172.30.100.111',port=3306,user='admin',password='123',database='python',charset='utf8')
cursor = conn.cursor()

sql = "select * from users where age =  %s"
result = cursor.execute(sql,args=28)
# 返回受影响的行数
print(result)

# 获取第一行数据
data = cursor.fetchone()
print(data)
# 获取第二行数据
data = cursor.fetchone()
print(data)
# 获取第三行数据
data = cursor.fetchone()
print(data)

cursor.close()
conn.close()

#执行结果
(5, 'Alice', 28, 4, 1)
(7, 'Even', 28, 0, None)
None

import pymysql

conn = pymysql.connect(host='172.30.100.111',port=3306,user='admin',password='123',database='python',charset='utf8')
cursor = conn.cursor()

sql = "select * from users where age =  %s"
result = cursor.execute(sql,args=28)
print(result)

data = cursor.fetchall()
print(data)
data = cursor.fetchone()
print(data)

cursor.close()
conn.close()

import pymysql

conn = pymysql.connect(host='172.30.100.111',port=3306,user='admin',password='123',database='python',charset='utf8')
cursor = conn.cursor()

sql = "select * from users where age =  %s"
result = cursor.execute(sql,args=28)
print(result)

# 返回两行数据的元组
data = cursor.fetchmany(2)
print(data)
# 再获取一行数据
data = cursor.fetchmany(1)
print(data)

cursor.close()
conn.close()

# 执行结果
((5, 'Alice', 28, 4, 1), (7, 'Even', 28, 0, None))
()

DictCursor

在创建游标时，添加参数pymysql.cursors.DictCursor，会使查询结果以字典的形式返回。

import pymysql

conn = pymysql.connect(host='172.30.100.111',port=3306,user='admin',password='123',database='python',charset='utf8')
# 指定字典格式游标
cursor = conn.cursor(pymysql.cursors.DictCursor)

sql = "select name,age from users where age = 28"
cursor.execute(sql)
# 获取一行数据
data = cursor.fetchone()
print(data)
# 获取剩余的所有数据
data = cursor.fetchall()
print(data)

cursor.close()
conn.close()

#执行结果
{'name': 'Alice', 'age': 28}
[{'name': 'Even', 'age': 28}, {'name': 'mari', 'age': 28}]

调用存储过程

mysql创建存储过程

CREATE PROCEDURE p1 (
    IN v1 INT,
    OUT v2 INT,
    INOUT v3 INT
)
BEGIN
    DECLARE i INT;
    DECLARE j INT DEFAULT 5;
    SET i = 2;
    SET v1 = i + v3;
    SET v2 = v1 + j;
    SET v3 = v1 + v2；
    SELECT name,age from users WHERE u_id = 5 or u_id = 11; 
END;

python调用存储过程

import pymysql

conn = pymysql.connect(host='172.30.100.111',port=3306,user='admin',password='123',database='python',charset='utf8')
cursor = conn.cursor(pymysql.cursors.DictCursor)

# 调用callproc方法执行存储过程
result = cursor.callproc('p1',(1,2,3))
# 返回传入的数值元组
print(result)

# 获取查询集结果
data = cursor.fetchall()
print(data)

# 获取返回值，变量名为@_存储过程名_0,表示第一个参数
cursor.execute('select @_p1_0,@_p1_1,@_p1_2')
args = cursor.fetchall()
print(args)

cursor.close()
conn.close()

#执行结果
(1, 2, 3)
[{'name': 'Alice', 'age': 28}, {'name': 'jarry', 'age': 19}]
[{'@_p1_0': 1, '@_p1_1': 10, '@_p1_2': 15}]

SQL注入攻击指的是通过构建特殊的输入作为参数传入Web应用程序，而这些输入大都是SQL语法里的一些组合，通过执行SQL语句进而执行攻击者所要的操作，其主要原因是程序没有细致地过滤用户输入的数据，致使非法数据侵入系统。

根据相关技术原理，SQL注入可以分为平台层注入和代码层注入。前者由不安全的数据库配置或数据库平台的漏洞所致；后者主要是由于程序员对输入未进行细致地过滤，从而执行了非法的数据查询。基于此，SQL注入的产生原因通常表现在以下几方面：①不当的类型处理；②不安全的数据库配置；③不合理的查询集处理；④不当的错误处理；⑤转义字符处理不合适；⑥多个提交处理不当。

SQL注入示例

实现需求，首先用户输入的用户名和密码，然后利用python模块连接数据库，查询到相关的用户信息。

import pymysql

username = input('username >>>')
password = input('password >>>')
sql = "select username,password,email from tab1 where username = '%s' and password = '%s'" % (username,password)

conn = pymysql.connect(host='172.30.100.111', user='admin',password='123',database='python',charset='utf8')
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql)
data = cursor.fetchall()
print(data)

cursor.close()
conn.close()

以上代码可以实现所需功能

在不需要任何的用户信息情况下也可以获取到内容，在username中输入' or 1=1 -- 。

防止SQL注入

1.永远不要信任用户的输入。对用户的输入进行校验，可以通过正则表达式，或限制长度；对单引号和双"-"进行转换等。

2.永远不要使用动态拼装sql，可以使用参数化的sql或者直接使用存储过程进行数据查询存取。

3.永远不要使用管理员权限的数据库连接，为每个应用使用单独的权限有限的数据库连接。

4.不要把机密信息直接存放，加密或者hash掉密码和敏感的信息。

 上述SQL注入是利用双"-"的特殊含义实现的，所以要对这种特殊字符进行处理。pymsql模块中已进行了处理，在执行sql语句时，使用pymysql参数形式则可避免这种SQL注入。

import pymysql

username = input('username >>>')
password = input('password >>>')
sql = "select username,password,email from tab1 where username = %s and password = %s"  #不需要对内容加双引号

conn = pymysql.connect(host='172.30.100.111', user='admin',password='123',database='python',charset='utf8')
cursor = conn.cursor(pymysql.cursors.DictCursor)
cursor.execute(sql, args=(username,password))  #传入参数
data = cursor.fetchall()
print(data)

cursor.close()
conn.close()

正常输入结果

SQL注入结果，不会拿到内容