Обсуждение: BUG #15632: Correctly escaped strings are mishandled in function
The following bug has been logged on the website:
Bug reference: 15632
Logged by: Kaleb Akalework
Email address: kaleb.akalework@asg.com
PostgreSQL version: 11.1
Operating system: Windows/Linux
Description:
I'm working on a Java app, and I used prepared statements to create a
function call to postgressql. The java database preparedstament method
correctly adds an additional quote to the existing single quote to escape.
And then I make the postgressql function call, and the function does not
execute because it removes the 2 single quotes and replaces it with one.
Here is the steps to reproduce it.
1. preparestament stmt = .....
2. stmt.setString (3, " SOLD' ")
3. stmt.execute(); (In the java code I see select * from
public.ModifyTopicValue('DEPT', 'SOLD', 'SOLD''') ) Notice how the last
parameter had the single quote escape by another single quote.
Inside my function I take these parameters and build a query like this
WITH upd AS ( UPDATE topic$_dept SET topic_value = 'SOLD'' WHERE
topic_value = 'SOLD' RETURNING 1 ) SELECT COUNT(*) FROM upd
This will throw an error because inside the function SOLD'' is converted to
SOLD' (SET topic_value = 'SOLD'' ). Why is postgressql removing the
additional escape character that was passed in. I would have expected it be
used like this SET topic_value = 'SOLD''' as it was passed in.
On Mon, Feb 11, 2019 at 11:04 AM PG Bug reporting form <noreply@postgresql.org> wrote: > I'm working on a Java app, and I used prepared statements to create a > function call to postgressql. PostgreSQL (one s)... > Inside my function I take these parameters and build a query like this > WITH upd AS ( UPDATE topic$_dept SET topic_value = 'SOLD'' WHERE > topic_value = 'SOLD' RETURNING 1 ) SELECT COUNT(*) FROM upd I doubt this is a bug but rather likely you are not doing something correctly here. But since you don't show the function its impossible to say what. > This will throw an error How hard is it to include the actual error? David J.
Hello David,
Here is the actual function. And I called this using select * FROM public.ModifyTopicValue('DEPT', 'SOLD', 'SOLD''')
CREATE OR REPLACE FUNCTION ModifyTopicValue(
rtn_rc OUT INTEGER,
rtn_row_count OUT INTEGER,
p_topic_id IN VARCHAR,
p_old_value IN VARCHAR,
p_new_value IN VARCHAR)
RETURNS RECORD AS
$func$
DECLARE
v_tpc_data_type INTEGER;
v_tpc_db_table VARCHAR;
v_replace_str VARCHAR;
v_sql VARCHAR;
v_sqlstate VARCHAR;
v_message VARCHAR;
v_sql_state_rec RECORD;
BEGIN
rtn_row_count := 0;
v_replace_str := p_new_value;
v_tpc_db_table = 'topic$_dept';
v_sql := ' WITH upd AS ( ';
v_sql := v_sql || ' UPDATE ' || v_tpc_db_table || ' ';
v_sql := v_sql || ' SET topic_value = ' || v_replace_str || ' ';
v_sql := v_sql || ' WHERE topic_value = ''' || p_old_value || ''' ';
v_sql := v_sql || ' RETURNING 1 ) ';
v_sql := v_sql || ' SELECT COUNT(*) FROM upd ';
--PERFORM VDRDebug.DisplayMessage('v_sql: ' || v_sql);
BEGIN
EXECUTE v_sql INTO rtn_row_count;
EXCEPTION WHEN OTHERS THEN
GET STACKED DIAGNOSTICS v_sqlstate = RETURNED_SQLSTATE, v_message = MESSAGE_TEXT;
SELECT * INTO v_sql_state_rec FROM MapSQLStateToErrorCode(v_sqlstate);
rtn_rc := -1 * v_sql_state_rec.rtn_rc;
PERFORM VDRDebug.DisplayMessage('Unable to update ' || p_topic_id
|| ' old value ' || p_old_value
|| ' new value ' || p_new_value
|| ' error: '
|| to_char(rtn_rc,'FM9999999999') || ' ' || v_sqlstate || ' ' || v_message);
RETURN;
END;
rtn_rc := 0;
RETURN;
END
$func$ LANGUAGE plpgsql VOLATILE;
-----Original Message-----
From: David G. Johnston <david.g.johnston@gmail.com>
Sent: Monday, February 11, 2019 1:19 PM
To: Kaleb Akalework <kaleb.akalework@asg.com>; pgsql-bugs@lists.postgresql.org
Subject: Re: BUG #15632: Correctly escaped strings are mishandled in function
*** External email: Verify sender before opening attachments or links ***
On Mon, Feb 11, 2019 at 11:04 AM PG Bug reporting form <noreply@postgresql.org> wrote:
> I'm working on a Java app, and I used prepared statements to create a
> function call to postgressql.
PostgreSQL (one s)...
> Inside my function I take these parameters and build a query like this
> WITH upd AS ( UPDATE topic$_dept SET topic_value = 'SOLD'' WHERE
> topic_value = 'SOLD' RETURNING 1 ) SELECT COUNT(*) FROM upd
I doubt this is a bug but rather likely you are not doing something correctly here. But since you don't show the
functionits impossible to say what.
> This will throw an error
How hard is it to include the actual error?
David J.
On Mon, Feb 11, 2019 at 11:22 AM Kaleb Akalework <kaleb.akalework@asg.com> wrote: > v_sql := ' WITH upd AS ( '; > v_sql := v_sql || ' UPDATE ' || v_tpc_db_table || ' '; > v_sql := v_sql || ' SET topic_value = ' || v_replace_str || ' '; > v_sql := v_sql || ' WHERE topic_value = ''' || p_old_value || ''' '; > v_sql := v_sql || ' RETURNING 1 ) '; > v_sql := v_sql || ' SELECT COUNT(*) FROM upd '; PostgreSQL provides two features to avoid writing SQL-injection prone code like this. Use one of them. quote_literal() and related functions The format() function and its %L and related specifiers. You can also place "$n" placeholders into the dynamic command as pass literals in via EXECUTE USING. Or some combination of the above. This is all nicely covered in the documentation for pl/pgsql regarding executing dynamic commands. https://www.postgresql.org/docs/11/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN David J.
The actual error I get is error: -42 42601 syntax error at or near "SOLD". Because it removed the double single quote. -----Original Message----- From: David G. Johnston <david.g.johnston@gmail.com> Sent: Monday, February 11, 2019 1:19 PM To: Kaleb Akalework <kaleb.akalework@asg.com>; pgsql-bugs@lists.postgresql.org Subject: Re: BUG #15632: Correctly escaped strings are mishandled in function *** External email: Verify sender before opening attachments or links *** On Mon, Feb 11, 2019 at 11:04 AM PG Bug reporting form <noreply@postgresql.org> wrote: > I'm working on a Java app, and I used prepared statements to create a > function call to postgressql. PostgreSQL (one s)... > Inside my function I take these parameters and build a query like this > WITH upd AS ( UPDATE topic$_dept SET topic_value = 'SOLD'' WHERE > topic_value = 'SOLD' RETURNING 1 ) SELECT COUNT(*) FROM upd I doubt this is a bug but rather likely you are not doing something correctly here. But since you don't show the functionits impossible to say what. > This will throw an error How hard is it to include the actual error? David J.
Yes I so that and I tried all of that and It did not work. Quote_lietral just double quotes it like this "SOLD'", which isstill broken because of the single quote. Using did not work for me as well. Please if you can reformulate this and showme, I would be happy to use it. But I was successful in doing so. The problem is an escaped string is transformed beforebeing used. Can you please try it on your end and see if it works for you? -----Original Message----- From: David G. Johnston <david.g.johnston@gmail.com> Sent: Monday, February 11, 2019 1:28 PM To: Kaleb Akalework <kaleb.akalework@asg.com> Cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #15632: Correctly escaped strings are mishandled in function *** External email: Verify sender before opening attachments or links *** On Mon, Feb 11, 2019 at 11:22 AM Kaleb Akalework <kaleb.akalework@asg.com> wrote: > v_sql := ' WITH upd AS ( '; > v_sql := v_sql || ' UPDATE ' || v_tpc_db_table || ' '; > v_sql := v_sql || ' SET topic_value = ' || v_replace_str || ' '; > v_sql := v_sql || ' WHERE topic_value = ''' || p_old_value || ''' '; > v_sql := v_sql || ' RETURNING 1 ) '; > v_sql := v_sql || ' SELECT COUNT(*) FROM upd '; PostgreSQL provides two features to avoid writing SQL-injection prone code like this. Use one of them. quote_literal() and related functions The format() function and its %L and related specifiers. You can also place "$n" placeholders into the dynamic command as pass literals in via EXECUTE USING. Or some combination of the above. This is all nicely covered in the documentation for pl/pgsql regarding executing dynamic commands. http://TOC.ASG.COM:8080/?dmVyPTEuMDAxJiZiYzk3NTU2ZjZlODIyMzQ3Yz01QzYxQkVCMV8zNzIwNV8xNTk1Nl8xJiY1OTUzNGVmMGRhMWNmMzI9MTIzMiYmdXJsPWh0dHBzJTNBJTJGJTJGd3d3JTJFcG9zdGdyZXNxbCUyRW9yZyUyRmRvY3MlMkYxMSUyRnBscGdzcWwtc3RhdGVtZW50cyUyRWh0bWwlMjNQTFBHU1FMLVNUQVRFTUVOVFMtRVhFQ1VUSU5HLURZTg== David J.
To be clear what I want is to send in 'SOLD''' in the function so in the query it can be used as is to store the final stringas SOLD'. IF I use the functions you mention it will just "" or add more quotes. Which is not what I want. -----Original Message----- From: David G. Johnston <david.g.johnston@gmail.com> Sent: Monday, February 11, 2019 1:28 PM To: Kaleb Akalework <kaleb.akalework@asg.com> Cc: pgsql-bugs@lists.postgresql.org Subject: Re: BUG #15632: Correctly escaped strings are mishandled in function *** External email: Verify sender before opening attachments or links *** On Mon, Feb 11, 2019 at 11:22 AM Kaleb Akalework <kaleb.akalework@asg.com> wrote: > v_sql := ' WITH upd AS ( '; > v_sql := v_sql || ' UPDATE ' || v_tpc_db_table || ' '; > v_sql := v_sql || ' SET topic_value = ' || v_replace_str || ' '; > v_sql := v_sql || ' WHERE topic_value = ''' || p_old_value || ''' '; > v_sql := v_sql || ' RETURNING 1 ) '; > v_sql := v_sql || ' SELECT COUNT(*) FROM upd '; PostgreSQL provides two features to avoid writing SQL-injection prone code like this. Use one of them. quote_literal() and related functions The format() function and its %L and related specifiers. You can also place "$n" placeholders into the dynamic command as pass literals in via EXECUTE USING. Or some combination of the above. This is all nicely covered in the documentation for pl/pgsql regarding executing dynamic commands. http://TOC.ASG.COM:8080/?dmVyPTEuMDAxJiZiYzk3NTU2ZjZlODIyMzQ3Yz01QzYxQkVCMV8zNzIwNV8xNTk1Nl8xJiY1OTUzNGVmMGRhMWNmMzI9MTIzMiYmdXJsPWh0dHBzJTNBJTJGJTJGd3d3JTJFcG9zdGdyZXNxbCUyRW9yZyUyRmRvY3MlMkYxMSUyRnBscGdzcWwtc3RhdGVtZW50cyUyRWh0bWwlMjNQTFBHU1FMLVNUQVRFTUVOVFMtRVhFQ1VUSU5HLURZTg== David J.
On Mon, Feb 11, 2019 at 11:47 AM Kaleb Akalework
<kaleb.akalework@asg.com> wrote:
> To be clear what I want is to send in 'SOLD''' in the function so in the query it can be used as is to store the
finalstring as SOLD'. IF I use the functions you mention it will just "" or add more quotes.
Please don't top-post.
The methods mentioned work perfectly; any failure to do what you are
desiring is from improper usage. Mistakes cannot be pointed out
unless you show them but here is one that does work.
create or replace function echo(in_str text)
returns text
language plpgsql
AS $func$
DECLARE qry text; res text;
BEGIN
--qry = 'SELECT ' || quote_literal(in_str) || ';';
--qry = format('SELECT %L', in_str);
--EXECUTE qry INTO res;
qry = 'SELECT $1';
EXECUTE qry INTO res USING in_str;
RETURN res;
END;
$func$
;
SELECT echo($in$SOLD'$in$);
-> SOLD'
A combination of "format" (for identifiers) and "execute using" (for
literals) is arguably the best solution.
David J.