时间:2021-07-01 10:21:17 帮助过:23人阅读
二 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