Python学习第113天（pymysql模块）

二 execute()之sql注入

注意：符号--会注释掉它之后的sql，正确的语法：--后至少有一个任意字符

根本原理：就根据程序的字符串拼接name=‘%s‘，我们输入一个xxx‘ -- haha,用我们输入的xxx加‘在程序中拼接成一个判断条件name=‘xxx‘ -- haha‘

最后那一个空格，在一条sql语句中如果遇到select * <span style="color: #0000ff;">from</span> t1 <span style="color: #0000ff;">where</span> id > <span style="color: #800080;">3</span> -- and name=<span style="color: #800000;">‘</span><span style="color: #800000;">egon</span><span style="color: #800000;">‘</span>;则--<span style="color: #000000;">之后的条件被注释掉了
#</span><span style="color: #800080;">1</span><span style="color: #000000;">、sql注入之：用户存在，绕过密码
egon</span><span style="color: #800000;">‘</span><span style="color: #800000;"> -- 任意字符</span>
<span style="color: #000000;">
#</span><span style="color: #800080;">2</span><span style="color: #000000;">、sql注入之：用户不存在，绕过用户与密码
xxx</span><span style="color: #800000;">‘</span><span style="color: #800000;"> or 1=1 -- 任意字符</span>

解决方法：

<span style="color: #000000;"># 原来是我们对sql进行字符串拼接
# sql</span>=<span style="color: #800000;">"</span><span style="color: #800000;">select * from userinfo where name=‘%s‘ and password=‘%s‘</span><span style="color: #800000;">"</span> %<span style="color: #000000;">(user,pwd)
# print(sql)
# res</span>=<span style="color: #000000;">cursor.execute(sql)
#改写为（execute帮我们做字符串拼接，我们无需且一定不能再为</span>%<span style="color: #000000;">s加引号了）
sql</span>=<span style="color: #800000;">"</span><span style="color: #800000;">select * from userinfo where name=%s and password=%s</span><span style="color: #800000;">"</span> #！！！注意%<span style="color: #000000;">s需要去掉引号，因为pymysql会自动为我们加上
res</span>=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题，只要我们按照pymysql的规矩来。

三 增、删、改：conn.commit()

<span style="color: #000000;">import pymysql
#链接
conn</span>=pymysql.connect(host=<span style="color: #800000;">‘</span><span style="color: #800000;">localhost</span><span style="color: #800000;">‘</span>,user=<span style="color: #800000;">‘</span><span style="color: #800000;">root</span><span style="color: #800000;">‘</span>,password=<span style="color: #800000;">‘</span><span style="color: #800000;">123</span><span style="color: #800000;">‘</span>,database=<span style="color: #800000;">‘</span><span style="color: #800000;">egon</span><span style="color: #800000;">‘</span><span style="color: #000000;">)
#游标
cursor</span>=<span style="color: #000000;">conn.cursor()
#执行sql语句
#part1
# sql</span>=<span style="color: #800000;">‘</span><span style="color: #800000;">insert into userinfo(name,password) values("root","123456");</span><span style="color: #800000;">‘</span><span style="color: #000000;">
# res</span>=<span style="color: #000000;">cursor.execute(sql) #执行sql语句，返回sql影响成功的行数
# print(res)
#part2
# sql</span>=<span style="color: #800000;">‘</span><span style="color: #800000;">insert into userinfo(name,password) values(%s,%s);</span><span style="color: #800000;">‘</span><span style="color: #000000;">
# res</span>=cursor.execute(sql,(<span style="color: #800000;">"</span><span style="color: #800000;">root</span><span style="color: #800000;">"</span>,<span style="color: #800000;">"</span><span style="color: #800000;">123456</span><span style="color: #800000;">"</span><span style="color: #000000;">)) #执行sql语句，返回sql影响成功的行数
# print(res)
#part3
sql</span>=<span style="color: #800000;">‘</span><span style="color: #800000;">insert into userinfo(name,password) values(%s,%s);</span><span style="color: #800000;">‘</span><span style="color: #000000;">
res</span>=cursor.executemany(sql,[(<span style="color: #800000;">"</span><span style="color: #800000;">root</span><span style="color: #800000;">"</span>,<span style="color: #800000;">"</span><span style="color: #800000;">123456</span><span style="color: #800000;">"</span>),(<span style="color: #800000;">"</span><span style="color: #800000;">lhf</span><span style="color: #800000;">"</span>,<span style="color: #800000;">"</span><span style="color: #800000;">12356</span><span style="color: #800000;">"</span>),(<span style="color: #800000;">"</span><span style="color: #800000;">eee</span><span style="color: #800000;">"</span>,<span style="color: #800000;">"</span><span style="color: #800000;">156</span><span style="color: #800000;">"</span><span style="color: #000000;">)]) #执行sql语句，返回sql影响成功的行数
print(res)
conn.commit() #提交后才发现表中插入记录成功
cursor.close()
conn.close()</span>

四 查：fetchone，fetchmany，fetchall

<span style="color: #000000;">import pymysql
#链接
conn</span>=pymysql.connect(host=<span style="color: #800000;">‘</span><span style="color: #800000;">localhost</span><span style="color: #800000;">‘</span>,user=<span style="color: #800000;">‘</span><span style="color: #800000;">root</span><span style="color: #800000;">‘</span>,password=<span style="color: #800000;">‘</span><span style="color: #800000;">123</span><span style="color: #800000;">‘</span>,database=<span style="color: #800000;">‘</span><span style="color: #800000;">egon</span><span style="color: #800000;">‘</span><span style="color: #000000;">)
#游标
cursor</span>=<span style="color: #000000;">conn.cursor()
#执行sql语句
sql</span>=<span style="color: #800000;">‘</span><span style="color: #800000;">select * from userinfo;</span><span style="color: #800000;">‘</span><span style="color: #000000;">
rows</span>=<span style="color: #000000;">cursor.execute(sql) #执行sql语句，返回sql影响成功的行数rows,将结果放入一个集合，等待被查询
# cursor.scroll(</span><span style="color: #800080;">3</span>,mode=<span style="color: #800000;">‘</span><span style="color: #800000;">absolute</span><span style="color: #800000;">‘</span><span style="color: #000000;">) # 相对绝对位置移动
# cursor.scroll(</span><span style="color: #800080;">3</span>,mode=<span style="color: #800000;">‘</span><span style="color: #800000;">relative</span><span style="color: #800000;">‘</span><span style="color: #000000;">) # 相对当前位置移动
res1</span>=<span style="color: #000000;">cursor.fetchone()
res2</span>=<span style="color: #000000;">cursor.fetchone()
res3</span>=<span style="color: #000000;">cursor.fetchone()
res4</span>=cursor.fetchmany(<span style="color: #800080;">2</span><span style="color: #000000;">)
res5</span>=<span style="color: #000000;">cursor.fetchall()
print(res1)
print(res2)
print(res3)
print(res4)
print(res5)
print(</span><span style="color: #800000;">‘</span><span style="color: #800000;">%s rows in set (0.00 sec)</span><span style="color: #800000;">‘</span> %<span style="color: #000000;">rows)
conn.commit() #提交后才发现表中插入记录成功
cursor.close()
conn.close()
</span><span style="color: #800000;">‘‘‘
</span>(<span style="color: #800080;">1</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">root</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">123456</span><span style="color: #800000;">‘</span><span style="color: #000000;">)
(</span><span style="color: #800080;">2</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">root</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">123456</span><span style="color: #800000;">‘</span><span style="color: #000000;">)
(</span><span style="color: #800080;">3</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">root</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">123456</span><span style="color: #800000;">‘</span><span style="color: #000000;">)
((</span><span style="color: #800080;">4</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">root</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">123456</span><span style="color: #800000;">‘</span>), (<span style="color: #800080;">5</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">root</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">123456</span><span style="color: #800000;">‘</span><span style="color: #000000;">))
((</span><span style="color: #800080;">6</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">root</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">123456</span><span style="color: #800000;">‘</span>), (<span style="color: #800080;">7</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">lhf</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">12356</span><span style="color: #800000;">‘</span>), (<span style="color: #800080;">8</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">eee</span><span style="color: #800000;">‘</span>, <span style="color: #800000;">‘</span><span style="color: #800000;">156</span><span style="color: #800000;">‘</span><span style="color: #000000;">))
rows </span><span style="color: #0000ff;">in</span> <span style="color: #0000ff;">set</span> (<span style="color: #800080;">0.00</span><span style="color: #000000;"> sec)
</span><span style="color: #800000;">‘‘‘</span>

Python学习第113天（pymysql模块）

标签：问题   一个   from   复习   mode   lse   nbsp   exe   es5