pymysql对数据库基础操作与模拟sql注入

hyadijxp

一、概述
　　本文将介绍python3中的pymysql模块对mysql进行增,删,改,查日常数据操作;实验的环境Ubuntu 16.04  mysql5.7.20   python3.5.2 数据库的安装忽略,如果也是ubuntu可直接通过
　　sudo apt-get install mysql-server
　　pymysql是专门用于操作MySQL 的python模块.python2.x也支持(还有MySQLdb),但在python3中目前只支持pymysql
　　安装
　　#pip3 install pymysql
　　测试数据库名test1 表tb1可通过以下sql创建
　　

create table tb1(id int(5) not null auto_increment primary key,name varchar(30) not null,gender varchar(3) default null,age int(3) default 0)engine=innodb default charset="utf8";　　

　　以下测试是基于这个tb1空表
　　表结构如下:

二、操作数据库
　　1、连接数据库
　　

import pymysql　　

　　
# 创建连接
　　
conn = pymysql.connect(host='127.0.0.1', port=3306, user='root', passwd='redhat', db='test1',charset='utf8')
　　
# 创建游标
　　
cursor = conn.cursor()   #元组类型返回
　　

　　
##对数据库操作部分####
　　
cursor.execute("")   #对数据库单条操作操作 直接写sql语句放入括号中
　　
cursor.executemany("") #多条操作
　　
#######
　　

　　
#对操作后的提交
　　
conn.commit()
　　

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

　　以上就是对数据连接操作过程的模板,
　　参数说明:
　　host:连接数据库的地址,本例使用本地数据库127.0.0.1,如若连接远程数据库请开放权限并打开防火墙
　　port:连接数据端口,默认为3306,默认端口可不填写
　　user:连接的用户
　　password:连接密码
　　db:需要操作的数据库
　　charset:设置数据类型
　　2、插入数据
　　a、单条数据插入(写入)
　　把以下行代码放入上面模板操作部分执行
　　

f = cursor.execute('insert into tb1(name,gender,age) values("san","男",18)')　　
print(f)     #受影响的行数
　　

　　

　　运行结果:
　　

/usr/bin/python3.5 /home/san/mysqldb/s1.py　　
1
　　
Process finished with exit code 0
　　

　　此时到数据库中登录查询如图:

　　一条数据插入数据成功.
　　b、多条插入
　　

infos = [　　("hehe","女",22),
　　("gege","男",19),
　　("mimi","女",20),
　　
]
　　

　　
f = cursor.executemany('insert into tb1(name,gender,age) values(%s,%s,%s)',infos)
　　
print(f)
　　

　　运行结果:
　　

/usr/bin/python3.5 /home/san/mysqldb/s1.py　　
3
　　
Process finished with exit code 0
　　

　　如图:

　　多条插入成功;
　　3、删除数据
　　

f = cursor.execute("delete from tb1 where>
print(f)　　

　　运行结果:
　　

/usr/bin/python3.5 /home/san/mysqldb/s1.py　　
1
　　
Process finished with exit code 0
　　

　　删除id为了的mini信息成功
　　如图:

　　4、修改数据(更新)
　　

f = cursor.execute("update tb1 set age='28' where>
print(f)　　

　　运行结果:
　　

/usr/bin/python3.5 /home/san/mysqldb/s1.py　　
1
　　
Process finished with exit code 0
　　

　　如图:

　　5、查询数据
　　a、获取一条数据
　　

f = cursor.execute("select * from tb1")　　
print(f,cursor.fetchonel())
　　

　　

　　运行结果:
　　

/usr/bin/python3.5 /home/san//mysqldb/s1.py　　
3
　　
(1, 'san', '男', 18)
　　
Process finished with exit code 0
　　

　　b、获取所有数据
　　

f = cursor.execute("select * from tb1")　　
print(f,cursor.fetchall())
　　

　　

　　运行结果 :
　　

/usr/bin/python3.5 /home/san/mysqldb/s1.py　　
3
　　
((1, 'san', '男', 18), (2, 'hehe', '女', 22), (3, 'gege', '男', 28))
　　
Process finished with exit code 0
　　

　　c、获取多条数据
　　为了更好的效果往tb1中再多插几条数据:
　　

insert into tb1(name,gender,age) values('while','男',26),('red','女',18),('Ling','女',25),('hile','男',22),('ded','女',28),('emma','女',24);　　

mysql> select * from tb1;　　
+----+-------+--------+------+

　　
|>　　
+----+-------+--------+------+
　　
|  1 | san   | 男     |   18 |
　　
|  2 | hehe  | 女     |   22 |
　　
|  3 | gege  | 男     |   28 |
　　
|  5 | while | 男     |   26 |
　　
|  6 | red   | 女     |   18 |
　　
|  7 | Ling  | 女     |   25 |
　　
|  8 | hile  | 男     |   22 |
　　
|  9 | ded   | 女     |   28 |
　　
| 10 | emma  | 女     |   24 |
　　
+----+-------+--------+------+
　　
9 rows in set (0.00 sec)
　　

　　

　　#获取4条数据
　　

f = cursor.execute("select * from tb1")　　
print(f)
　　
print(cursor.fetchmany(4))
　　

　　运行结果:
　　

/usr/bin/python3.5 /home/san/mysqldb/s1.py　　
9
　　
((1, 'san', '男', 18), (2, 'hehe', '女', 22), (3, 'gege', '男', 28), (5, 'while', '男', 26))
　　

　　
Process finished with exit code 0
　　

　　d、关于查询时的位置(指针)说明
　　cursor.fetchone()   #只拿查询结果中的第一条
　　cursor.fetchmany(4) #指定从查询结果中取多少条,默认是从第一条开始拿
　　cursor.fetchall()    #取出所有查询结果
　　问题:当我们fetchall所有结果后或fetchmany指定条结果后如何再获取第一条数据?
　　例如:
　　我们在获取了前4条结果后想重新获取第一条数据和全部结果时
　　

f = cursor.execute("select * from tb1")　　
print(f)
　　
print(cursor.fetchmany(4))
　　
print("#########")
　　
print(cursor.fetchone())
　　
print("#########")
　　
print(cursor.fetchall())
　　

　　结果如下:
　　

/usr/bin/python3.5 /home/san/mysqldb/s1.py　　
9
　　
((1, 'san', '男', 18), (2, 'hehe', '女', 22), (3, 'gege', '男', 28), (5, 'while', '男', 26))
　　
#########
　　
(6, 'red', '女', 18)
　　
#########
　　
((7, 'Ling', '女', 25), (8, 'hile', '男', 22), (9, 'ded', '女', 28), (10, 'emma', '女', 24))
　　
Process finished with exit code 0
　　

　　并非是我们想要的结果,当我们取出前4条再运行fetchone时获取的是第5条数据
　　再fetchall时是从第5条开始.要想得到我们想要的结果就需要涉及到位置问题或叫指针位置,可以通过以下两个函数来重新获取新的位置;
　　cursor.scroll(0,mode="absolute")       #数字0表示从第一条开始,绝对模式,即把位置指针移动到第一条开始
　　因此把cursor.scroll(0,mode="absolute") 代码添加到以上print(cursor.fetchmany(4))后面和print(cursor.fetchall())前面;就可以达到我们要的效果;
　　cursor.scroll(1,mode="relative")   #相对模式,即相对于当前位置 ,正数是往后,负数是往前获取
　　来个实例:
　　

为了便于测试 说明了问题,先往数据中插入id为4的信息,　　
>insert into tb1(id,name,gender,age) values(4,'min','女',19);
　　

f = cursor.execute("select * from tb1")　　
print(f)
　　
print("#####获取前4条####")
　　
print(cursor.fetchmany(4))
　　
print("####往后移两条即第7条#####")
　　
cursor.scroll(2,mode="relative")
　　
print(cursor.fetchone())
　　

　　
print("#####往前移动4条即第4条####")
　　
cursor.scroll(-4,mode="relative")
　　
print(cursor.fetchone())
　　

　　
print("####位置移动到第一条开始#####")
　　
cursor.scroll(0,mode="absolute")
　　
print(cursor.fetchall())
　　

　　运行结果:

　　tb1表:
　　

mysql> select * from tb1;　　
+----+-------+--------+------+
　　
|>　　
+----+-------+--------+------+
　　
|  1 | san   | 男     |   18 |
　　
|  2 | hehe  | 女     |   22 |
　　
|  3 | gege  | 男     |   28 |
　　
|  4 | min   | 女     |   19 |
　　
|  5 | while | 男     |   26 |
　　
|  6 | red   | 女     |   18 |
　　
|  7 | Ling  | 女     |   25 |
　　
|  8 | hile  | 男     |   22 |
　　
|  9 | ded   | 女     |   28 |
　　
| 10 | emma  | 女     |   24 |
　　
+----+-------+--------+------+
　　
10 rows in set (0.00 sec)
　　

　　以上是对数据库的基本操作,注意点:
　　向数据库中写入,删除,修改后需要注意的是必须要conn.commit()提交
　　查询时尽量避免select * ,数据量大会导致很慢;甚至会死机;

三、安全相关其他
　　1、简单sql注入
　　以上的操作没有和用户名交互,比如说插入数据时用的都是硬编码写在程序中
　　如果我们需要用户输入时就涉及到sql连接问题,先来一个示例:
　　把tb1增加一列password并修改密码为1234
　　

mysql>>
mysql> update tb1 set password="1234";
　　
Query OK, 10 rows affected (0.03 sec)
　　
Rows matched: 10  Changed: 10  Warnings: 0
　　

　　
mysql> select * from tb1;
　　
+----+-------+----------+--------+------+

　　
|>　　
+----+-------+----------+--------+------+
　　
|  1 | san   | 1234     | 男     |   18 |
　　
|  2 | hehe  | 1234     | 女     |   22 |
　　
|  3 | gege  | 1234     | 男     |   28 |
　　
|  4 | min   | 1234     | 女     |   19 |
　　
|  5 | while | 1234     | 男     |   26 |
　　
|  6 | red   | 1234     | 女     |   18 |
　　
|  7 | Ling  | 1234     | 女     |   25 |
　　
|  8 | hile  | 1234     | 男     |   22 |
　　
|  9 | ded   | 1234     | 女     |   28 |
　　
| 10 | emma  | 1234     | 女     |   24 |
　　
+----+-------+----------+--------+------+
　　
10 rows in set (0.00 sec)
　　

　　

　　模拟验证用户名和密码:
　　

sql = 'select name,password from tb1 where name="%s" and password="%s"'　　
sql = sql %('min',1234)  #正常获取结果
　　
cursor.execute(sql)
　　
result=cursor.fetchone()
　　
print(result)
　　
if result:
　　print("login ok")
　　
else:
　　print("login fatal!")
　　

　　运行结果:
　　

/usr/bin/python3.5 /home/san/mysqldb/s1.py　　
('min', '1234')
　　
1
　　
login ok
　　
Process finished with exit code 0
　　

　　

　　但我们把密码修改非正确的密码1234时
　　运行是
　　

None　　
0
　　
login fatal!
　　

　　我们把代码 换成以下:
　　

sql = 'select name,password from tb1 where name="%s" and password="%s"'　　
sql = sql %('min" -- ', 1236)
　　
cursor.execute(sql)
　　
result=cursor.fetchone()
　　
print(result)
　　

　　
if result:
　　print("login ok")
　　
else:
　　print("login fatal!")
　　

　　

　　运行结果:
　　

/usr/bin/python3.5 /home/san/mysqldb/s1.py　　
('min', '1234')
　　
login ok
　　
Process finished with exit code 0
　　

　　错误的密码竟然也能登录成功,why?先不说why 我们再用错误的用户名和密码试试!
　　

sql = 'select name,password from tb1 where name="%s" and password="%s"'　　
sql = sql %('lsb" or 1=1 -- ', 1236)
　　

　　
cursor.execute(sql)
　　
result=cursor.fetchone()
　　
print(result)
　　
if result:
　　print("login ok")
　　
else:
　　print("login fatal!")
　　

　　

　　结果:
　　

/usr/bin/python3.5 /home/san/mysqldb/s1.py　　
('san', '1234')
　　
login ok
　　
Process finished with exit code 0
　　

　　

　　错误填写用户名和密码也能获取第一条正确的数据?why,必须知道why
　　这其实是一个简单的注入原因在于sql语句不规范,用了字符串拼接,sql中的--是注释
　　sql = sql %('min" -- ', 1236)  被替换成
　　sql = 'select name,password from tb1 where name='min -- " and password="1236"'
　　从--之后都被注释了,变成 了
　　select name,password from tb1 where name='min';
　　而用错误 的用户名 ql = sql %('lsb" or 1=1 -- ', 1236)时被替换成
　　sql = 'select name,password from tb1 where name='lsb or 1=1 -- " and password="1236"'
　　成为select name,password from tb1;
　　解决 方案:
　　规范操作sql语句
　　

cursor.execute('select name,password fromt b1 where name=%s and password=%s',('min',1234))　　
result= cursor.fetchone()
　　
print(result)
　　

　　这里再输入错误字符时转换成
　　

info=('min" -- ',1234)　　
cursor.execute('select name,password from tb1 where name=%s and password=%s',info)
　　
result=cursor.fetchone()
　　
print(result)
　　
if result:
　　print("login ok")
　　
else:
　　print("login fatal!")
　　

　　执行结果没有问题:
　　

/usr/bin/python3.5 /home/san/mysqldb/s1.py　　
None
　　
login fatal!
　　
Process finished with exit code 0
　　

　　而正确的用户名和密码就会登录成功没有问题!
　　2、修改游标类型
　　默认的获取数据的类型是元组,只能通过索引去获取,要是有一种方案能直接通过键获取值不是很好?
　　pymysql提供了方案,修改游标返回类型
　　修改获取为字典类型
　　

cursor = conn.cursor(pymysql.cursors.DictCursor)  #字典类型　　
f = cursor.execute("select * from tb1")
　　
print(f)
　　
print(cursor.fetchall())
　　

　　运行结果:
　　

/usr/bin/python3.5 /home/san/mysqldb/s1.py　　
10
　　
[{'id': 1, 'gender': '男', 'name': 'san', 'password': '1234', 'age': 18}, {'id': 2, 'gender': '女', 'name': 'hehe', 'password': '1234', 'age': 22}, {'id': 3, 'gender': '男', 'name': 'gege', 'password': '1234', 'age': 28}, {'id': 4, 'gender': '女', 'name': 'min', 'password': '1234', 'age': 19}, {'id': 5, 'gender': '男', 'name': 'while', 'password': '1234', 'age': 26}, {'id': 6, 'gender': '女', 'name': 'red', 'password': '1234', 'age': 18}, {'id': 7, 'gender': '女', 'name': 'Ling', 'password': '1234', 'age': 25}, {'id': 8, 'gender': '男', 'name': 'hile', 'password': '1234', 'age': 22}, {'id': 9, 'gender': '女', 'name': 'ded', 'password': '1234', 'age': 28}, {'id': 10, 'gender': '女', 'name': 'emma', 'password': '1234', 'age': 24}]
　　

　　
Process finished with exit code 0
　　

　　

　　以上就是python3通过pymysql模块操作mysql的基础操作和需要注意力的事项,以防注入;如有不当之处 欢迎指正 !