当前位置:Gxlcms > 数据库问题 > Python学习第113天(pymysql模块)

Python学习第113天(pymysql模块)

时间:2021-07-01 10:21:17 帮助过:23人阅读

=input(用户名: ).strip() pwd=input(密码: ).strip() #链接 conn=pymysql.connect(host=localhost,user=root,password=123,database=egon,charset=utf8) #游标 cursor=conn.cursor() #执行完毕返回的结果集默认以元组显示 #cursor=conn.cursor(cursor=pymysql.cursors.DictCursor) #执行sql语句 sql=select * from userinfo where name="%s" and password="%s" %(user,pwd) #注意%s需要加引号 print(sql) res=cursor.execute(sql) #执行sql语句,返回sql查询成功的记录数目 print(res) cursor.close() conn.close() if res: print(登录成功) else: print(登录失败)

二 execute()之sql注入

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

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

  1. 最后那一个空格,在一条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;">之后的条件被注释掉了
  2. #</span><span style="color: #800080;">1</span><span style="color: #000000;">、sql注入之:用户存在,绕过密码
  3. egon</span><span style="color: #800000;">‘</span><span style="color: #800000;"> -- 任意字符</span>
  4. <span style="color: #000000;">
  5. #</span><span style="color: #800080;">2</span><span style="color: #000000;">、sql注入之:用户不存在,绕过用户与密码
  6. xxx</span><span style="color: #800000;">‘</span><span style="color: #800000;"> or 1=1 -- 任意字符</span>

技术图片

 

 

技术图片

 

 

 技术图片

解决方法:

  1. <span style="color: #000000;"># 原来是我们对sql进行字符串拼接
  2. # 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)
  3. # print(sql)
  4. # res</span>=<span style="color: #000000;">cursor.execute(sql)
  5. #改写为(execute帮我们做字符串拼接,我们无需且一定不能再为</span>%<span style="color: #000000;">s加引号了)
  6. 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会自动为我们加上
  7. res</span>=cursor.execute(sql,[user,pwd]) #pymysql模块自动帮我们解决sql注入的问题,只要我们按照pymysql的规矩来。

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

  1. <span style="color: #000000;">import pymysql
  2. #链接
  3. 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;">)
  4. #游标
  5. cursor</span>=<span style="color: #000000;">conn.cursor()
  6. #执行sql语句
  7. #part1
  8. # 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;">
  9. # res</span>=<span style="color: #000000;">cursor.execute(sql) #执行sql语句,返回sql影响成功的行数
  10. # print(res)
  11. #part2
  12. # 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;">
  13. # 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影响成功的行数
  14. # print(res)
  15. #part3
  16. 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;">
  17. 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影响成功的行数
  18. print(res)
  19. conn.commit() #提交后才发现表中插入记录成功
  20. cursor.close()
  21. conn.close()</span>

四 查:fetchone,fetchmany,fetchall

技术图片

 

 

 

  1. <span style="color: #000000;">import pymysql
  2. #链接
  3. 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;">)
  4. #游标
  5. cursor</span>=<span style="color: #000000;">conn.cursor()
  6. #执行sql语句
  7. sql</span>=<span style="color: #800000;">‘</span><span style="color: #800000;">select * from userinfo;</span><span style="color: #800000;">‘</span><span style="color: #000000;">
  8. rows</span>=<span style="color: #000000;">cursor.execute(sql) #执行sql语句,返回sql影响成功的行数rows,将结果放入一个集合,等待被查询
  9. # 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;">) # 相对绝对位置移动
  10. # 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;">) # 相对当前位置移动
  11. res1</span>=<span style="color: #000000;">cursor.fetchone()
  12. res2</span>=<span style="color: #000000;">cursor.fetchone()
  13. res3</span>=<span style="color: #000000;">cursor.fetchone()
  14. res4</span>=cursor.fetchmany(<span style="color: #800080;">2</span><span style="color: #000000;">)
  15. res5</span>=<span style="color: #000000;">cursor.fetchall()
  16. print(res1)
  17. print(res2)
  18. print(res3)
  19. print(res4)
  20. print(res5)
  21. 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)
  22. conn.commit() #提交后才发现表中插入记录成功
  23. cursor.close()
  24. conn.close()
  25. </span><span style="color: #800000;">‘‘‘
  26. </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;">)
  27. (</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;">)
  28. (</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;">)
  29. ((</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;">))
  30. ((</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;">))
  31. 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)
  32. </span><span style="color: #800000;">‘‘‘</span>

 

Python学习第113天(pymysql模块)

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

人气教程排行