CREATE OR REPLACE FUNCTION dynamicJsonValue(varchar(64)) RETURNS VOID AS
'UPDATE "table" SET "field" = ''value''
WHERE "json_field" @> ''{"key": $1}'';'
LANGUAGE SQL VOLATILE;
I can't get my function param to insert in the query like this.
Add a comment
|
2 Answers
Not even the quotes match in your statement...
Besides, it is vulnerable to SQL injection.
Try something like this:
CREATE OR REPLACE FUNCTION dynamicJsonValue(varchar(64)) RETURNS void AS
$$UPDATE "table" SET "field" = 'value'
WHERE "json_field"
@> CAST ('{"key": "' || replace($1, '"', '') || '" }' AS jsonb)$$
LANGUAGE sql STRICT;
1 Comment
pozs
to_json() is perfectly safe to escape string literals when composing json[b] manually (and it is available from 9.3+). to_jsonb() and json_build_object() is available where jsonb introduced (9.4+).Figured it out:
CREATE OR REPLACE FUNCTION dynamicJsonValue(varchar(64)) RETURNS VOID AS
'UPDATE "table" SET "field" = ''value''
WHERE "json_field" @> jsonb_build_object(''field'', $1);'
LANGUAGE SQL VOLATILE;