pymysql如何防止sql注入

注意：pymysql版本1.0.2， Python3

 

1. 在写原生sql语句的时候，需要拼接sql语句，这个拼接sql的方式特别容易sql注入的攻击

列如：

user = 'zhangsan'
pwd = '123456'
sql = f'''select * from user where username="{user}" and password="{pwd}";'''

看着没啥问题，但是你输入账号user输入这样的账号：sb"or"2"="2, 密码就随便输入一串字符串

最后sql语句就变成：

select * from user where "sb"or"2"="2" and passwod="afsdffasdfas";

然后去查询数据，竟然成功了，因为“2“=“2”是true成立，而or在“2”=“2”后面，password就忽略执行了，所以sql执行就，当然了不光这种sql注入，还有sleep等等

 

2. 解决办法：在sql语句写入占位符(%s)

 

import pymysql

conn = pymysql.connect(host="127.0.0.1", user='root', password="12345" charset='utf8' database='test')
curs = conn.cursor()
sql = "select * from user where username=%s and password=%s;"
cdn_tuple = ("zhangsan", "123456")
curs.execute(sql, cdn_tuple)
res = curs.fetchone()
conn.close()

 

 

2.1 如果是模糊查询怎么写

import pymysql

conn = pymysql.connect(host="127.0.0.1", user='root', password="12345" charset='utf8' database='test')
curs = conn.cursor()
sql = "select * from user where username like %s;"

user = '42b'
format_str = f"%%{user}%%"
cdn_tuple = (format_str)
curs.execute(sql, cdn_tuple)
res = curs.fetchone()
conn.close()

 

2.2 日期的sql语句

 

import pymysql

conn = pymysql.connect(host="127.0.0.1", user='root', password="12345" charset='utf8' database='test')
curs = conn.cursor()
sql = "select * from user where date_format(addtime, "%%Y-%%m-%%d")>=str_to_date(%s, "%%Y-%%m-%%d");"

addtime = '2022-07-12'
cdn_tuple = (addtime)
curs.execute(sql, cdn_tuple)
res = curs.fetchone()
conn.close()

 

  

暂时遇到这么多，有新的知识点，我在继续添加。

如果上面有啥问题，谢谢指出。奥利给打工人