12345587585

Committed 16 Dec 2024 03:52AM UTC coverage: 82.006%. First build

Build # 12345587585

Build Type

Pull #14409

github

Committed by

web-flow

Commit Message

Merge d444fc076 into 2ce3a1bf3

Pull Request Pull Request #14409: feat: rule func `str_utf16_le/1` and `sqlserver_hexbin/1`

Run Details

30 of 32 new or added lines in 3 files covered. (93.75%)

56440 of 68824 relevant lines covered (82.01%)

15130.76 hits per line

Source File
Press 'n' to go to next uncovered line, 'b' for previous

90.91

/apps/emqx_utils/src/emqx_utils_sql.erl

%%--------------------------------------------------------------------
%% Copyright (c) 2022-2024 EMQ Technologies Co., Ltd. All Rights Reserved.
%%
%% Licensed under the Apache License, Version 2.0 (the "License");
%% you may not use this file except in compliance with the License.
%% You may obtain a copy of the License at
%%
%%     http://www.apache.org/licenses/LICENSE-2.0
%%
%% Unless required by applicable law or agreed to in writing, software
%% distributed under the License is distributed on an "AS IS" BASIS,
%% WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
%% See the License for the specific language governing permissions and
%% limitations under the License.
%%--------------------------------------------------------------------

-module(emqx_utils_sql).

-export([get_statement_type/1]).
-export([parse_insert/1]).

-export([to_sql_value/1]).
-export([to_sql_string/2]).

-export([escape_sql/1]).
-export([escape_cql/1]).
-export([escape_mysql/1]).
-export([escape_snowflake/1]).

-export_type([value/0]).

-type statement_type() :: select | insert | delete | update.
-type value() :: null | binary() | number() | boolean() | [value()].

%% The type Copied from stdlib/src/re.erl to compatibility with OTP 26
%% Since `re:mp()` exported after OTP 27
-type mp() :: {re_pattern, _, _, _, _}.

-define(INSERT_RE_MP_KEY, {?MODULE, insert_re_mp}).
-define(INSERT_RE_BIN, <<
    %% case-insensitive
    "(?i)^\\s*",
    %% Group-1: insert into, table name and columns (when existed).
    %% All space characters suffixed to <TABLE_NAME> will be kept
    %% `INSERT INTO <TABLE_NAME> [(<COLUMN>, ..)]`
    "(insert\\s+into\\s+[^\\s\\(\\)]+\\s*(?:\\([^\\)]*\\))?)",
    %% Keyword: `VALUES`
    "\\s*values\\s*",
    %% Group-2: literals value(s) or placeholder(s) with round brackets.
    %% And the sub-pattern in brackets does not do any capturing
    %% `([<VALUE> | <PLACEHOLDER>], ..])`
    "(\\((?:[^()]++|(?2))*\\))",
    "\\s*$"
>>).

-define(HEX_RE_MP_KEY, {?MODULE, hex_re_mp}).
-define(HEX_RE_BIN, <<"^[0-9a-fA-F]+$">>).

-dialyzer({no_improper_lists, [escape_mysql/4, escape_prepend/4]}).

-on_load(on_load/0).

on_load() ->
    ok = put_insert_mp(),
    ok = put_hex_re_mp().

put_insert_mp() ->
    persistent_term:put(?INSERT_RE_MP_KEY, re:compile(?INSERT_RE_BIN)),
    ok.

-spec get_insert_mp() -> {ok, mp()}.
get_insert_mp() ->
    case persistent_term:get(?INSERT_RE_MP_KEY, undefined) of
        undefined ->
            ok = put_insert_mp(),
            get_insert_mp();
        {ok, MP} ->
            {ok, MP}
    end.

put_hex_re_mp() ->
    persistent_term:put(?HEX_RE_MP_KEY, re:compile(?HEX_RE_BIN)),
    ok.

-spec get_hex_re_mp() -> {ok, mp()}.
get_hex_re_mp() ->
    case persistent_term:get(?HEX_RE_MP_KEY, undefined) of
        undefined ->
            ok = put_hex_re_mp(),
            get_hex_re_mp();
        {ok, MP} ->
            {ok, MP}
    end.

-spec get_statement_type(iodata()) -> statement_type() | {error, unknown}.
get_statement_type(Query) ->
    KnownTypes = #{
        <<"select">> => select,
        <<"insert">> => insert,
        <<"update">> => update,
        <<"delete">> => delete
    },
    case re:run(Query, <<"^\\s*([a-zA-Z]+)">>, [{capture, all_but_first, binary}]) of
        {match, [Token]} ->
            maps:get(string:lowercase(Token), KnownTypes, {error, unknown});
        _ ->
            {error, unknown}
    end.

%% @doc Parse an INSERT SQL statement into its INSERT part and the VALUES part.
%% SQL = <<"INSERT INTO \"abc\" (c1, c2, c3) VALUES (${a}, ${b}, ${c.prop})">>
%% {ok, {<<"INSERT INTO \"abc\" (c1, c2, c3)">>, <<"(${a}, ${b}, ${c.prop})">>}}
-spec parse_insert(iodata()) ->
    {ok, {_Statement :: binary(), _Rows :: binary()}} | {error, not_insert_sql}.
parse_insert(SQL) ->
    {ok, MP} = get_insert_mp(),
    case re:run(SQL, MP, [{capture, all_but_first, binary}]) of
        {match, [InsertInto, ValuesTemplate]} ->
            {ok, {InsertInto, ValuesTemplate}};
        nomatch ->
            {error, not_insert_sql}
    end.

%% @doc Convert an Erlang term to a value that can be used primarily in
%% prepared SQL statements.
-spec to_sql_value(term()) -> value().
to_sql_value(undefined) -> null;
to_sql_value(List) when is_list(List) -> List;
to_sql_value(Bin) when is_binary(Bin) -> Bin;
to_sql_value(Num) when is_number(Num) -> Num;
to_sql_value(Bool) when is_boolean(Bool) -> Bool;
to_sql_value(Atom) when is_atom(Atom) -> atom_to_binary(Atom, utf8);
to_sql_value(Map) when is_map(Map) -> emqx_utils_json:encode(Map).

%% @doc Convert an Erlang term to a string that can be interpolated in literal
%% SQL statements. The value is escaped if necessary.
-spec to_sql_string(term(), Options) -> unicode:chardata() when
    Options :: #{
        escaping => mysql | sql | cql | sqlserver,
        undefined => null | unicode:chardata()
    }.
to_sql_string(undefined, #{undefined := Str} = Opts) when Str =/= null ->
    to_sql_string(Str, Opts);
to_sql_string(undefined, #{}) ->
    <<"NULL">>;
to_sql_string(String, #{escaping := mysql}) when is_binary(String) ->
    try
        escape_mysql(String)
    catch
        throw:invalid_utf8 ->
            [<<"0x">>, binary:encode_hex(String)]
    end;
to_sql_string(Term, #{escaping := mysql}) ->
    maybe_escape(Term, fun escape_mysql/1);
to_sql_string(Term, #{escaping := cql}) ->
    maybe_escape(Term, fun escape_cql/1);
to_sql_string(Term, #{escaping := sqlserver}) ->
    maybe_escape(Term, fun escape_sqlserver/1);
to_sql_string(Term, #{}) ->
    maybe_escape(Term, fun escape_sql/1).

-spec maybe_escape(_Value, fun((binary()) -> iodata())) -> unicode:chardata().
maybe_escape(Str, EscapeFun) when is_binary(Str) ->
    EscapeFun(Str);
maybe_escape(Str, EscapeFun) when is_list(Str) ->
    case unicode:characters_to_binary(Str) of
        Bin when is_binary(Bin) ->
            EscapeFun(Bin);
        Otherwise ->
            error(Otherwise)
    end;
maybe_escape(Val, EscapeFun) when is_atom(Val) orelse is_map(Val) ->
    EscapeFun(emqx_template:to_string(Val));
maybe_escape(Val, _EscapeFun) ->
    emqx_template:to_string(Val).

-spec escape_sql(binary()) -> iodata().
escape_sql(S) ->
    % NOTE
    % This is a bit misleading: currently, escaping logic in `escape_sql/1` likely
    % won't work with pgsql since it does not support C-style escapes by default.
    % https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
    ES = binary:replace(S, [<<"\\">>, <<"'">>], <<"\\">>, [global, {insert_replaced, 1}]),
    [$', ES, $'].

-spec escape_cql(binary()) -> iodata().
escape_cql(S) ->
    ES = binary:replace(S, <<"'">>, <<"'">>, [global, {insert_replaced, 1}]),
    [$', ES, $'].

-spec escape_mysql(binary()) -> iodata().
escape_mysql(S0) ->
    % https://dev.mysql.com/doc/refman/8.0/en/string-literals.html
    [$', escape_mysql(S0, 0, 0, S0), $'].

-spec escape_snowflake(binary()) -> iodata().
escape_snowflake(S) ->
    ES = binary:replace(S, <<"\"">>, <<"\"">>, [global, {insert_replaced, 1}]),
    [$", ES, $"].

escape_sqlserver(<<"0x", Rest/binary>> = S) ->
    {ok, MP} = get_hex_re_mp(),
    case re:run(Rest, MP, []) of
        {match, _} ->
            [S];
        _ ->
            escape_sql(S)
    end;
escape_sqlserver(S) ->
    escape_sql(S).

%% NOTE
%% This thing looks more complicated than needed because it's optimized for as few
%% intermediate memory (re)allocations as possible.
escape_mysql(<<$', Rest/binary>>, I, Run, Src) ->
    escape_prepend(I, Run, Src, [<<"\\'">> | escape_mysql(Rest, I + Run + 1, 0, Src)]);
escape_mysql(<<$\\, Rest/binary>>, I, Run, Src) ->
    escape_prepend(I, Run, Src, [<<"\\\\">> | escape_mysql(Rest, I + Run + 1, 0, Src)]);
escape_mysql(<<0, Rest/binary>>, I, Run, Src) ->
    escape_prepend(I, Run, Src, [<<"\\0">> | escape_mysql(Rest, I + Run + 1, 0, Src)]);
escape_mysql(<<_/utf8, Rest/binary>> = S, I, Run, Src) ->
    CWidth = byte_size(S) - byte_size(Rest),
    escape_mysql(Rest, I, Run + CWidth, Src);
escape_mysql(<<>>, 0, _, Src) ->
    Src;
escape_mysql(<<>>, I, Run, Src) ->
    binary:part(Src, I, Run);
escape_mysql(_, _I, _Run, _Src) ->
    throw(invalid_utf8).

escape_prepend(_RunI, 0, _Src, Tail) ->
    Tail;
escape_prepend(I, Run, Src, Tail) ->
    [binary:part(Src, I, Run) | Tail].

1	%%--------------------------------------------------------------------
2	%% Copyright (c) 2022-2024 EMQ Technologies Co., Ltd. All Rights Reserved.
3	%%
4	%% Licensed under the Apache License, Version 2.0 (the "License");
5	%% you may not use this file except in compliance with the License.
6	%% You may obtain a copy of the License at
7	%%
8	%% http://www.apache.org/licenses/LICENSE-2.0
9	%%
10	%% Unless required by applicable law or agreed to in writing, software
11	%% distributed under the License is distributed on an "AS IS" BASIS,
12	%% WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
13	%% See the License for the specific language governing permissions and
14	%% limitations under the License.
15	%%--------------------------------------------------------------------
16
17	-module(emqx_utils_sql).
18
19	-export([get_statement_type/1]).
20	-export([parse_insert/1]).
21
22	-export([to_sql_value/1]).
23	-export([to_sql_string/2]).
24
25	-export([escape_sql/1]).
26	-export([escape_cql/1]).
27	-export([escape_mysql/1]).
28	-export([escape_snowflake/1]).
29
30	-export_type([value/0]).
31
32	-type statement_type() :: select \| insert \| delete \| update.
33	-type value() :: null \| binary() \| number() \| boolean() \| [value()].
34
35	%% The type Copied from stdlib/src/re.erl to compatibility with OTP 26
36	%% Since `re:mp()` exported after OTP 27
37	-type mp() :: {re_pattern, _, _, _, _}.
38
39	-define(INSERT_RE_MP_KEY, {?MODULE, insert_re_mp}).
40	-define(INSERT_RE_BIN, <<
41	%% case-insensitive
42	"(?i)^\\s*",
43	%% Group-1: insert into, table name and columns (when existed).
44	%% All space characters suffixed to <TABLE_NAME> will be kept
45	%% `INSERT INTO <TABLE_NAME> [(<COLUMN>, ..)]`
46	"(insert\\s+into\\s+[^\\s\\(\\)]+\\s(?:\\([^\\)]\\))?)",
47	%% Keyword: `VALUES`
48	"\\svalues\\s",
49	%% Group-2: literals value(s) or placeholder(s) with round brackets.
50	%% And the sub-pattern in brackets does not do any capturing
51	%% `([<VALUE> \| <PLACEHOLDER>], ..])`
52	"(\\((?:[^()]++\|(?2))*\\))",
53	"\\s*$"
54	>>).
55
56	-define(HEX_RE_MP_KEY, {?MODULE, hex_re_mp}).
57	-define(HEX_RE_BIN, <<"^[0-9a-fA-F]+$">>).
58
59	-dialyzer({no_improper_lists, [escape_mysql/4, escape_prepend/4]}).
60
61	-on_load(on_load/0).
62
63	on_load() ->
64	ok = put_insert_mp(),	537✔
65	ok = put_hex_re_mp().	537✔
66
67	put_insert_mp() ->
68	persistent_term:put(?INSERT_RE_MP_KEY, re:compile(?INSERT_RE_BIN)),	537✔
69	ok.	537✔
70
71	-spec get_insert_mp() -> {ok, mp()}.
72	get_insert_mp() ->
73	case persistent_term:get(?INSERT_RE_MP_KEY, undefined) of	200✔
74	undefined ->
75	ok = put_insert_mp(),	×
76	get_insert_mp();	×
77	{ok, MP} ->
78	{ok, MP}	200✔
79	end.
80
81	put_hex_re_mp() ->
82	persistent_term:put(?HEX_RE_MP_KEY, re:compile(?HEX_RE_BIN)),	537✔
83	ok.	537✔
84
85	-spec get_hex_re_mp() -> {ok, mp()}.
86	get_hex_re_mp() ->
87	case persistent_term:get(?HEX_RE_MP_KEY, undefined) of	2✔
88	undefined ->
NEW 89	ok = put_hex_re_mp(),	×
NEW 90	get_hex_re_mp();	×
91	{ok, MP} ->
92	{ok, MP}	2✔
93	end.
94
95	-spec get_statement_type(iodata()) -> statement_type() \| {error, unknown}.
96	get_statement_type(Query) ->
97	KnownTypes = #{	297✔
98	<<"select">> => select,
99	<<"insert">> => insert,
100	<<"update">> => update,
101	<<"delete">> => delete
102	},
103	case re:run(Query, <<"^\\s*([a-zA-Z]+)">>, [{capture, all_but_first, binary}]) of	297✔
104	{match, [Token]} ->
105	maps:get(string:lowercase(Token), KnownTypes, {error, unknown});	297✔
106	_ ->
107	{error, unknown}	×
108	end.
109
110	%% @doc Parse an INSERT SQL statement into its INSERT part and the VALUES part.
111	%% SQL = <<"INSERT INTO \"abc\" (c1, c2, c3) VALUES (${a}, ${b}, ${c.prop})">>
112	%% {ok, {<<"INSERT INTO \"abc\" (c1, c2, c3)">>, <<"(${a}, ${b}, ${c.prop})">>}}
113	-spec parse_insert(iodata()) ->
114	{ok, {_Statement :: binary(), _Rows :: binary()}} \| {error, not_insert_sql}.
115	parse_insert(SQL) ->
116	{ok, MP} = get_insert_mp(),	200✔
117	case re:run(SQL, MP, [{capture, all_but_first, binary}]) of	200✔
118	{match, [InsertInto, ValuesTemplate]} ->
119	{ok, {InsertInto, ValuesTemplate}};	194✔
120	nomatch ->
121	{error, not_insert_sql}	6✔
122	end.
123
124	%% @doc Convert an Erlang term to a value that can be used primarily in
125	%% prepared SQL statements.
126	-spec to_sql_value(term()) -> value().
127	to_sql_value(undefined) -> null;	30✔
128	to_sql_value(List) when is_list(List) -> List;	10✔
129	to_sql_value(Bin) when is_binary(Bin) -> Bin;	2,030✔
130	to_sql_value(Num) when is_number(Num) -> Num;	1,893✔
131	to_sql_value(Bool) when is_boolean(Bool) -> Bool;	1✔
132	to_sql_value(Atom) when is_atom(Atom) -> atom_to_binary(Atom, utf8);	7✔
133	to_sql_value(Map) when is_map(Map) -> emqx_utils_json:encode(Map).	4✔
134
135	%% @doc Convert an Erlang term to a string that can be interpolated in literal
136	%% SQL statements. The value is escaped if necessary.
137	-spec to_sql_string(term(), Options) -> unicode:chardata() when
138	Options :: #{
139	escaping => mysql \| sql \| cql \| sqlserver,
140	undefined => null \| unicode:chardata()
141	}.
142	to_sql_string(undefined, #{undefined := Str} = Opts) when Str =/= null ->
143	to_sql_string(Str, Opts);	57✔
144	to_sql_string(undefined, #{}) ->
145	<<"NULL">>;	10✔
146	to_sql_string(String, #{escaping := mysql}) when is_binary(String) ->
147	try	3,238✔
148	escape_mysql(String)	3,238✔
149	catch
150	throw:invalid_utf8 ->
151	[<<"0x">>, binary:encode_hex(String)]	6✔
152	end;
153	to_sql_string(Term, #{escaping := mysql}) ->
154	maybe_escape(Term, fun escape_mysql/1);	3,237✔
155	to_sql_string(Term, #{escaping := cql}) ->
156	maybe_escape(Term, fun escape_cql/1);	8✔
157	to_sql_string(Term, #{escaping := sqlserver}) ->
158	maybe_escape(Term, fun escape_sqlserver/1);	206✔
159	to_sql_string(Term, #{}) ->
160	maybe_escape(Term, fun escape_sql/1).	62✔
161
162	-spec maybe_escape(_Value, fun((binary()) -> iodata())) -> unicode:chardata().
163	maybe_escape(Str, EscapeFun) when is_binary(Str) ->
164	EscapeFun(Str);	201✔
165	maybe_escape(Str, EscapeFun) when is_list(Str) ->
166	case unicode:characters_to_binary(Str) of	6✔
167	Bin when is_binary(Bin) ->
168	EscapeFun(Bin);	6✔
169	Otherwise ->
170	error(Otherwise)	×
171	end;
172	maybe_escape(Val, EscapeFun) when is_atom(Val) orelse is_map(Val) ->
173	EscapeFun(emqx_template:to_string(Val));	16✔
174	maybe_escape(Val, _EscapeFun) ->
175	emqx_template:to_string(Val).	3,290✔
176
177	-spec escape_sql(binary()) -> iodata().
178	escape_sql(S) ->
179	% NOTE
180	% This is a bit misleading: currently, escaping logic in `escape_sql/1` likely
181	% won't work with pgsql since it does not support C-style escapes by default.
182	% https://www.postgresql.org/docs/14/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
183	ES = binary:replace(S, [<<"\\">>, <<"'">>], <<"\\">>, [global, {insert_replaced, 1}]),	213✔
184	[$', ES, $'].	213✔
185
186	-spec escape_cql(binary()) -> iodata().
187	escape_cql(S) ->
188	ES = binary:replace(S, <<"'">>, <<"'">>, [global, {insert_replaced, 1}]),	4✔
189	[$', ES, $'].	4✔
190
191	-spec escape_mysql(binary()) -> iodata().
192	escape_mysql(S0) ->
193	% https://dev.mysql.com/doc/refman/8.0/en/string-literals.html
194	[$', escape_mysql(S0, 0, 0, S0), $'].	3,243✔
195
196	-spec escape_snowflake(binary()) -> iodata().
197	escape_snowflake(S) ->
198	ES = binary:replace(S, <<"\"">>, <<"\"">>, [global, {insert_replaced, 1}]),	2,000✔
199	[$", ES, $"].	2,000✔
200
201	escape_sqlserver(<<"0x", Rest/binary>> = S) ->
202	{ok, MP} = get_hex_re_mp(),	2✔
203	case re:run(Rest, MP, []) of	2✔
204	{match, _} ->
205	[S];	1✔
206	_ ->
207	escape_sql(S)	1✔
208	end;
209	escape_sqlserver(S) ->
210	escape_sql(S).	162✔
211
212	%% NOTE
213	%% This thing looks more complicated than needed because it's optimized for as few
214	%% intermediate memory (re)allocations as possible.
215	escape_mysql(<<$', Rest/binary>>, I, Run, Src) ->
216	escape_prepend(I, Run, Src, [<<"\\'">> \| escape_mysql(Rest, I + Run + 1, 0, Src)]);	12✔
217	escape_mysql(<<$\\, Rest/binary>>, I, Run, Src) ->
218	escape_prepend(I, Run, Src, [<<"\\\\">> \| escape_mysql(Rest, I + Run + 1, 0, Src)]);	6✔
219	escape_mysql(<<0, Rest/binary>>, I, Run, Src) ->
220	escape_prepend(I, Run, Src, [<<"\\0">> \| escape_mysql(Rest, I + Run + 1, 0, Src)]);	6✔
221	escape_mysql(<<_/utf8, Rest/binary>> = S, I, Run, Src) ->
222	CWidth = byte_size(S) - byte_size(Rest),	61,850✔
223	escape_mysql(Rest, I, Run + CWidth, Src);	61,850✔
224	escape_mysql(<<>>, 0, _, Src) ->
225	Src;	3,229✔
226	escape_mysql(<<>>, I, Run, Src) ->
227	binary:part(Src, I, Run);	8✔
228	escape_mysql(_, _I, _Run, _Src) ->
229	throw(invalid_utf8).	6✔
230
231	escape_prepend(_RunI, 0, _Src, Tail) ->
232	Tail;	6✔
233	escape_prepend(I, Run, Src, Tail) ->
234	[binary:part(Src, I, Run) \| Tail].	6✔

emqx / emqx / 12345587585

Source File Press 'n' to go to next uncovered line, 'b' for previous

Source File
Press 'n' to go to next uncovered line, 'b' for previous