一次SQL请求，返回分页数据和总条数，你学会了吗？

日常搬砖，请求总少不了需要获取分页数据和总行数。返回分页

一直以来的数据实践是编码两次sql请求，分别拉分页数据和totalCount。和总

最近我在思考：

常规实践为什么不是条数 在一次sql请求中中执行多次sql查询或多次更新，显而易见的请求优势：

① 能显著减低“客户端和服务器之间的网络往返次数”，提高吞吐量② 简化客户端代码逻辑

1. mysql 默认单sql请求单语句

mysql客户端选项client_multi_statements默认为false：会禁止多条 SQL 语句的返回分页执行，这意味着在单个sql请求中只有第一条 SQL 语句会被执行，数据后续的和总 SQL 语句将被忽略。

这是条数一种提高数据库操作安全性的方法，可以有效防止 SQL 注入攻击和意外执行多条语句带来的请求风险。

MySQL客户端支持修改这样的返回分页设定 ：client_multi_statements=true。

图片

劣势：存在sql注入的数据风险， 错误处理比较复杂。和总

(1) go-sql-driver开启多语句支持: multiStatements=true

(2)

SELECT * FROM `dict_plugin` limit 20 ,条数10; SELECT count(*) as totalCount from `dict_plugin`;

将会形成2个数据集，golang的实践如下：

results, err = p.Query(querystring) 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)

如果从界面输入的b2b供应网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注入

参数化查询可以防止sql注入风险[1]

// 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注入的IT技术网原理， 给出了参数化查询（预编译语句）能防止sql注入的核心机制。

参考资料

[1]参数化查询可以防止sql注入风险: https://go.dev/doc/database/sql-injection