PostgreSql 动态SQL

动态SQL

1.using语法

推荐优先使用using处理变量防止SQL注入，语句中使用冒号参数占位符，使用using赋值。
示例：

execute 'select * from tableName where id =:_id and name=:_name' using _id,_name;

2.格式化函数format

格式化语法占位符说明：

• %I 处理参数值作为SQL标识符，必要时增加双引号（如果是正常数据不会加双引号，特殊数据会加双引号，参考示例2）；
• %s 格式化参数作为简单字符串不做任何操作原样输出；
• %L 引用参数作为SQL字面值（参数常用语quote_literal()作用类似）;

示例1：

select format('select * from %I.%I where "id" = %s and type = %L','schema','tableName','1','1');
--结果
select * from schema.tableName where "id"=1 and type = '1';

示例2：

--%I自动识别标识符，如果表名输入的是数字则会自动加上双引号防止报错。
select format('select * from %I.%I where "id" = %s and type = %L','schema','tableName','1','1');
--结果schema 1加了双引号
select * from “1”.tableName where "id"=1 and type = '1';

3.quote_indent()、quote_literal()函数

通常quote_indent()作为处理标识符使用，转换结果会自动判断是否加双引号，例如表名是数字的情况，字符串的不会加；quote_literal()作为引用参数使用，转换结果加单引号；

示例：

execute 'select'||quote_indent('name') || 'from tableName where id ='||quote_literal('123');
--结果
select name from tableName where id = '123';

总结

以上三种方式使用优先级推荐 1>2>3，较简单的SQL可以使用1、2处理。复杂的SQL拼接在接收外部参数时建议使用quote_literal()转换，防止SQL注入。