使用一次sql请求,返回分页数据和总条数
日常搬砖,总少不了需要获取分页数据和总行数。
一直以来的实践是编码两次sql请求,分别拉分页数据和totolCount。
最近我在思考:
常规实践为什么不是 在一次sql请求中中执行多次sql查询或多次更新,显而易见的优势:
① 能显著减低“客户端和服务器之间的网络往返次数”,提高吞吐量
② 简化客户端代码逻辑
1. mysql 默认单sql请求单语句
mysql客户端选项client_multi_statements
默认为false: 会禁止多条 SQL 语句的执行,这意味着在单个sql请求中只有第一条 SQL 语句会被执行,后续的 SQL 语句将被忽略。
这是一种提高数据库操作安全性的方法,可以有效防止 SQL 注入攻击和意外执行多条语句带来的风险。
MySQL客户端支持修改这样的设定 :client_multi_statements=true, 一次sql请求,内多次执行sql查询或操作。
劣势:存在sql注入的风险, 错误处理比较复杂。
(1) go-sql-driver开启多语句支持: multiStatements=true
(2) 常规编码: 一次sql请求内多次sql操作
qStr:= "SELECT * FROM `dict_plugin` limit 20 ,10;"
qStr += "SELECT count(*) as totalCount from `dict_plugin`;"
将会形成2个数据集,golang的实践如下:
results, err = p.Query(qStr)
for results.Next() {
err = results.Scan(&...)
}
if !results.NextResultSet() {
log.ErrorF(ctx, "expected more result sets: %v", results.Err())
}
for results.Next() {
err = results.Scan(&totalCount)
}
既然提到了开启client_multi_statements 有sql注入的风险,我们就展开聊一聊。
2. sql注入
我们先看下sql注入的原理:
有这样的业务sql:
var input_name string
query: = "select * from user where user_name='" + input_name+"'"
sql.Query(query)
如果从界面输入的input_name
="janus';delete from user; --",
会形成恶意sql:select * from user where user_name='janus';delete from user; --' 。
这个时候,客户端的client_multi_statements默认值为false
就能于水火之间挽救数据库: 执行第一个sql之后,后面的恶意sql都不会执行。
由此可知,client_multi_statements=false
,确实可以显著降低sql注入的风险,但是还是没有办法避免单sql注入, 比如从界面密码框注入' OR '1'='1
会绕过登录认证。
query:= "select * from user where user='" + input_name +"' and pwd='" +input_pwd +"'"
select * from user where user='xxx' and pwd='' OR '1'='1' -- 会绕过认证逻辑。
3. 参数化查询防止sql注入
javaer 有文章预编译SQL为什么能够防止SQL注入指出: 参数化防止sql注入有两种实现
- 驱动对参数字符串中 特殊字符做转义
- 预编译语句
golang 官方有参数化查询可以防止sql注入风险,
- 常规的Query(str) 形成的就是 sql Query硬编码写法,
- 参数化的Query(str,args...) 会利用预编译语句, 当然golang 也有明确要求使用预编译语句的写法,下面有介绍。
// Correct format for executing an SQL statement with parameters.
var queryStr = "SELECT * FROM `dict_plugin_Test` WHERE `plugin_name` = ?"
var args string = "55 union select * from `dict_plugin_Test`"
rows, err := db.Query(queryStr, args)
sql查询内部会利用提供的参数1创建预编译语句, 后面执行的是: 用参数替换占位符后预编译语句。
在服务器收到的查询日志如下:
2024-08-13T08:07:18.922818Z 26 Connect root@localhost on tcinfra_janus_sharing using TCP/IP
2024-08-13T08:07:18.924525Z 26 Prepare SELECT * FROM `dict_plugin_Test` WHERE `plugin_name` = ?
2024-08-13T08:07:18.924671Z 26 Execute SELECT * FROM `dict_plugin_Test` WHERE `plugin_name` = '55 union select * from `dict_plugin_Test`'
2024-08-13T08:07:18.925273Z 26 Close stmt
判断mysql数据库开启了查询日志: show variables like '%general_log%';
打开sql查询日志的开关: set global general_log = on; 。
注意: 参数占位符根据DBSM和驱动而有所不同,例如,Postgres 的pq驱动程序接受占位符形式是 $1而不是?。
3.1 预编译语句
数据库预编译后, SQL语义结构和数据分离,这样即使输入包含恶意代码,它也只会被当作数据处理,不会影响已经被解析固定的SQL语义结构。
预编译语句包含两次 sql交互:
① 预编译阶段(Prepare Phase):
- 客户端向服务器发送一个包含 SQL 语句(带有参数占位符)的请求。
- sql服务器对SQL 语句进行语法和语义检查,然后对其进行预编译,并为其分配一个标识符(Statement ID)。
- 服务器返回一个确认响应,表示预编译语句已经成功准备好。
② 执行阶段(Execute Phase):
- 客户端发送执行请求,包含预编译语句的标识符和实际参数值。
- 服务器将参数值绑定到预编译语句的占位符上,然后执行该语句。
- 服务器返回执行结果(如结果集或影响的行数)。
图示如下:
客户端 服务器
| |
|----预编译语句(Prepare)------>|
| |
|<-------确认响应(OK)----------|
| |
|---执行语句(Execute) + 参数---->|
| |
|<----------查询结果-------------|
我们了解到预编译语句,将SQL语义和数据分离,通过两次sql交互(在预编译阶段固定了sql语义结构), 有效防止了SQL注入攻击, 另一方面,预编译语句在重复执行某一sql语句时确实有加快查询结果的效果。
golang的明确的预编译语句写法,与常规的sql查询类似:
stmt, err := p.Prepare("SELECT * FROM `dict_plugin_Test` WHERE `plugin_name` = ?")
var args string = "55 union select * from `dict_plugin_Test`"
results, err := stmt.Query(args)
if err != nil {
fmt.Printf("query fail: %v", err)
return err
}
defer stmt.Close()
for results.Next() {
err = results.Scan(.....)
......
}
btw, C# 其实也支持预编译语句版本的sqlCommand: SqlCommand.Prepare()
总结
本文通过我们最初开始数据库编程时的一个实践, 提出在【一次sql请求中执行多次sql查询】的猜想;
了解到client_multi_statements= false 确实能避免一部分sql注入风险;
之后落地到sql注入的核心, 给出了参数化查询(预编译语句)能防止sql注入的核心作用。
本文来自博客园,作者:{有态度的马甲},转载请注明原文链接:https://www.cnblogs.com/JulianHuang/p/18358370
欢迎关注我的原创技术、职场公众号, 加好友谈天说地,一起进化![](https://blog-static.cnblogs.com/files/JulianHuang/QR.gif)