Thread: Weird double single quote issue
It is like the weirdest thing ever...
I have a proc that dynamically generates SQL, executes it and returns
results as setof record. Some of fields are strings with single quotes
inside them. Since these strings are being picked up from database I
store them as:
'string to be stored with '' quotemark'
which is being inserted in generated SQL string as:
select 'string to be stored with '' quotemark'::text as returned_string
and gets returned to user as:
string to be stored with ' quotemark
Now, the weirdest thing is that for some db users everything works as it
should, but for others the returned string is with TWO single quotes!
select 'string to be stored with '' quotemark'::text as returned_string
returns
string to be stored with '' quotemark
I cannot replicate the same behavior from SQL Editor, or psql
commandline. It only happens inside my proc (which is rather monstrous).
The behavior is consistent across users (if it returns two single-quotes
instead of one it keeps doing it for the same user). However the
behavior is NOT consistent across different examples: some strings are
returned with single quote, others always get two single quotes.
I can't put my finger on exactly what's causing such behaviour, but gut
feeling is that it's got something to do with other fields and their
values that are being returned in the same SELECT statement. Sample SELECT:
select 'RCCU'::varchar as field_name, 'RCCU'::text as field_caption,
''::varchar as field_index, coalesce("RCCU"::varchar,'')::varchar as
field_value, 'select "CU" as item_id,"CUNAME" as item_data from
prf_import_culst where user_id=get_effective_user() order by
"CUNAME"'::text as field_listsource, '1043'::int as field_type, '<?xml
version="1.0" encoding="utf-8"?>
<field_attr></field_attr>
'::text as field_attr from "prf_import_rcrds" where import_id like '%'
and user_id=get_effective_user() and is_visible
Owner user (the one who owns the proc) always gets expected behaviour
from the proc (single quote instead of two single quotes). Other users
(all or nearly all) get two single-quotes on some strings (always the
same strings).
We're running Postgres 8.1.3 FWIW
Any ideas?
Peter
On Thu, Nov 02, 2006 at 01:35:41PM +0200, Peter wrote: > It is like the weirdest thing ever... > > I have a proc that dynamically generates SQL, executes it and returns > results as setof record. Some of fields are strings with single quotes > inside them. Since these strings are being picked up from database I > store them as: <snip> Not quite sure, but maybe some users are using inline parameters and some are using out of line parameters? The quoting rules only apply to inline paramaters. In any case, it appears someone is screwing up the quoting somewhere... Note that there are quoting functions to help create dynamic sql safely. Have a nice day, -- Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/ > From each according to his ability. To each according to his ability to litigate.
Attachment
Peter wrote: > Now, the weirdest thing is that for some db users everything works as it > should, but for others the returned string is with TWO single quotes! > > select 'string to be stored with '' quotemark'::text as returned_string > > returns > > string to be stored with '' quotemark > > > I cannot replicate the same behavior from SQL Editor, or psql > commandline. It only happens inside my proc (which is rather monstrous). 1. Have you done ALTER ROLE ... SET ... on any users? 2. Are the locales/encodings different for the users? 3. Can you isolate the problem part of the procedure? I'm guessing you'll have to do #3 since you can't reproduce it with psql. If I were you, I'd suspect the proc rather than the DB in this case. -- Richard Huxton Archonet Ltd
>> Now, the weirdest thing is that for some db users everything works as
>> it should, but for others the returned string is with TWO single quotes!
>>
>> select 'string to be stored with '' quotemark'::text as returned_string
>>
>> returns
>>
>> string to be stored with '' quotemark
>>
>>
>> I cannot replicate the same behavior from SQL Editor, or psql
>> commandline. It only happens inside my proc (which is rather monstrous).
>
> 1. Have you done ALTER ROLE ... SET ... on any users?
> 2. Are the locales/encodings different for the users?
> 3. Can you isolate the problem part of the procedure?
>
> I'm guessing you'll have to do #3 since you can't reproduce it with
> psql. If I were you, I'd suspect the proc rather than the DB in this
> case.
>
1. No
2. No. In fact I can replicate the problem on the same computer using
PgAdmin (connecting as two different users)
3. I can isolate the part but it does not help much. I'm even doing
RAISE NOTICE and printing out the actual SQL statement that is being
executed... and it's IDENTICAL for both users yet returns different
values. Executing exactly the same SQL statement from commandline
produces identical results for both users.
4. I'm totally baffled... :-/
Here is excerpt from my proc that does the 'magic':
elseif operation='QUERY' then
/* Return list of all columns in table for single row with prim key
matching primarykeyvalue[] */
/* retrieve specified columns in our table */
/* iterate over array elements */
for i in array_lower(conf_field_list,1) ..
array_upper(conf_field_list,1) loop
sFieldNameOrig := conf_field_list[i];
sFieldAttr := conf_field_attr[i]; /*
coalesce(replace(substring(sFieldName from ':+(.*)?'),':',''),''); */
/* loop thru columns and execute SELECT for each one of them. Place
results in variable retval */
sFieldName := conf_field_list_vc[i]; /* fully qualified fieldname,
suitable for varchar casting */
/* raise notice '%',conf_sListSource[i]; */
begin
sListSource:=conf_sListSource[i];
exception when array_subscript_error then
end;
sListSource:=coalesce(sListSource,'');
/* in sListSource replace $USER_ID$ macro with our user's ID */
sListSource:=replace(sListSource,'$USER_ID$',get_effective_user());
/* raise notice 'fieldname % fieldtype % fieldattr
%',sFieldName,iFieldType,sFieldAttr; */
sSQL := $$select '$$ || sFieldNameOrig || $$'::varchar as field_name,
$$ ||
coalesce(conf_field_capt[i],'')::text || $$::text as field_caption,
$$ ||
$$''::varchar as field_index, $$ ||
sFieldName::varchar || $$ as field_value, $$ ||
$$'$$ || sListSource::text || $$'::text as field_listsource, '$$ ||
coalesce(conf_field_type[i],'') || $$'::int as field_type, $$ ||
$$'$$ || sFieldAttr || $$'::text as field_attr $$ ||
$$ from "$$ || conf_table_name || $$" $$ || sWhere;
raise notice 'executing %',sSQL;
execute sSQL INTO retval;
/* catch empty rowset */
get diagnostics rowcount=ROW_COUNT;
if ( rowcount = 0 ) then
retval.field_name:=sFieldNameOrig;
retval.field_caption:=coalesce(conf_field_capt[i],'');
retval.field_index:=0;
retval.field_value:='';
retval.field_listsource:=sListSource;
retval.field_type:=coalesce(conf_field_type[i],'');
retval.field_attr:=sFieldAttr;
end if;
return next retval;
end loop;
Peter
Peter wrote: > >>> Now, the weirdest thing is that for some db users everything works as >>> it should, but for others the returned string is with TWO single quotes! >>> >>> select 'string to be stored with '' quotemark'::text as returned_string >>> >>> returns >>> >>> string to be stored with '' quotemark >>> >>> >>> I cannot replicate the same behavior from SQL Editor, or psql >>> commandline. It only happens inside my proc (which is rather monstrous). >> >> 1. Have you done ALTER ROLE ... SET ... on any users? >> 2. Are the locales/encodings different for the users? >> 3. Can you isolate the problem part of the procedure? >> >> I'm guessing you'll have to do #3 since you can't reproduce it with >> psql. If I were you, I'd suspect the proc rather than the DB in this >> case. >> > > > 1. No > 2. No. In fact I can replicate the problem on the same computer using > PgAdmin (connecting as two different users) > 3. I can isolate the part but it does not help much. I'm even doing > RAISE NOTICE and printing out the actual SQL statement that is being > executed... and it's IDENTICAL for both users yet returns different > values. Executing exactly the same SQL statement from commandline > produces identical results for both users. > 4. I'm totally baffled... :-/ [snip] > sSQL := $$select '$$ || sFieldNameOrig || $$'::varchar as field_name, > $$ || > coalesce(conf_field_capt[i],'')::text || $$::text as field_caption, > $$ || > $$''::varchar as field_index, $$ || > sFieldName::varchar || $$ as field_value, $$ || > $$'$$ || sListSource::text || $$'::text as field_listsource, '$$ || > coalesce(conf_field_type[i],'') || $$'::int as field_type, $$ || > $$'$$ || sFieldAttr || $$'::text as field_attr $$ || > $$ from "$$ || conf_table_name || $$" $$ || sWhere; > > raise notice 'executing %',sSQL; I assume you're doubling the quotes in these variables earlier in the function. I'd tidy this up via quote_literal: sSQL := 'select ' || quote_literal(sFieldNameOrig) || '::varchar as field_name...' There's a quote_ident() function too - details in the "string functions" section of the manuals. -- Richard Huxton Archonet Ltd
>>>> Now, the weirdest thing is that for some db users everything works >>>> as it should, but for others the returned string is with TWO single >>>> quotes! >>>> >>>> select 'string to be stored with '' quotemark'::text as >>>> returned_string >>>> >>>> returns >>>> >>>> string to be stored with '' quotemark >>>> >>>> >>>> I cannot replicate the same behavior from SQL Editor, or psql >>>> commandline. It only happens inside my proc (which is rather >>>> monstrous). >>> >>> 1. Have you done ALTER ROLE ... SET ... on any users? >>> 2. Are the locales/encodings different for the users? >>> 3. Can you isolate the problem part of the procedure? >>> >>> I'm guessing you'll have to do #3 since you can't reproduce it with >>> psql. If I were you, I'd suspect the proc rather than the DB in this >>> case. >>> >> >> >> 1. No >> 2. No. In fact I can replicate the problem on the same computer using >> PgAdmin (connecting as two different users) >> 3. I can isolate the part but it does not help much. I'm even doing >> RAISE NOTICE and printing out the actual SQL statement that is being >> executed... and it's IDENTICAL for both users yet returns different >> values. Executing exactly the same SQL statement from commandline >> produces identical results for both users. >> 4. I'm totally baffled... :-/ > [snip] >> sSQL := $$select '$$ || sFieldNameOrig || $$'::varchar as >> field_name, $$ || >> coalesce(conf_field_capt[i],'')::text || $$::text as >> field_caption, $$ || >> $$''::varchar as field_index, $$ || >> sFieldName::varchar || $$ as field_value, $$ || >> $$'$$ || sListSource::text || $$'::text as field_listsource, '$$ >> || coalesce(conf_field_type[i],'') || $$'::int as field_type, $$ || >> $$'$$ || sFieldAttr || $$'::text as field_attr $$ || >> $$ from "$$ || conf_table_name || $$" $$ || sWhere; >> >> raise notice 'executing %',sSQL; > > I assume you're doubling the quotes in these variables earlier in the > function. I'd tidy this up via quote_literal: > sSQL := 'select ' || quote_literal(sFieldNameOrig) || '::varchar as > field_name...' > > There's a quote_ident() function too - details in the "string > functions" section of the manuals. Allright, the quote_literal() function helped to a certain extent. One field is now always properly formatted, and one other sometimes is quoted... and sometimes is not. It still feels like internal PG issue to me... we'll try upgrading to latest revision and see what happens... thanks for the help! Peter
Peter wrote: > > Allright, the quote_literal() function helped to a certain extent. One > field is now always properly formatted, and one other sometimes is > quoted... and sometimes is not. > > It still feels like internal PG issue to me... we'll try upgrading to > latest revision and see what happens... The only place I can think is some subtle bug in the dollar-quoting code. Otherwise, I think plpgsql just uses the SQL expression evaluator so it can't come up with a different answer. And that doesn't sound likely to me. If you run that segment of the procedure with some hard-coded values, does it still do it? -- Richard Huxton Archonet Ltd
Peter <peter@greatnowhere.com> writes:
> 2. No. In fact I can replicate the problem on the same computer using
> PgAdmin (connecting as two different users)
> 3. I can isolate the part but it does not help much. I'm even doing
> RAISE NOTICE and printing out the actual SQL statement that is being
> executed... and it's IDENTICAL for both users yet returns different
> values. Executing exactly the same SQL statement from commandline
> produces identical results for both users.
It's really pretty hard to believe that the same function would work
differently for two different users. I'm wondering if maybe the two
users aren't executing the same function --- perhaps not the specific
one you are looking at, but some other one it depends on. Do the
users have different search_path settings?
regards, tom lane
Tom Lane wrote: > Peter <peter@greatnowhere.com> writes: > >>2. No. In fact I can replicate the problem on the same computer using >>PgAdmin (connecting as two different users) >>3. I can isolate the part but it does not help much. I'm even doing >>RAISE NOTICE and printing out the actual SQL statement that is being >>executed... and it's IDENTICAL for both users yet returns different >>values. Executing exactly the same SQL statement from commandline >>produces identical results for both users. > > > It's really pretty hard to believe that the same function would work > differently for two different users. I'm wondering if maybe the two > users aren't executing the same function --- perhaps not the specific > one you are looking at, but some other one it depends on. Do the > users have different search_path settings? > I'm thinking that the data returned might be dependent upon the user. Is this the case? And can any of those columns used in the proc be NULL? Frankly, i couldn't read it very well (but then, i'm on my first cup of coffee this morning), but i'm wondering if the problem lies somewhere in between the dollar-quoting and coalesce(). brian
2. No. In fact I can replicate the problem on the same computer using PgAdmin (connecting as two different users) 3. I can isolate the part but it does not help much. I'm even doing RAISE NOTICE and printing out the actual SQL statement that is being executed... and it's IDENTICAL for both users yet returns different values. Executing exactly the same SQL statement from commandline produces identical results for both users.It's really pretty hard to believe that the same function would work differently for two different users. I'm wondering if maybe the two users aren't executing the same function --- perhaps not the specific one you are looking at, but some other one it depends on. Do the users have different search_path settings?
I totally follow you. It really is hard to believe that the same code can be executed differently for different users. I did double-check that db is the same, and the actual proc being executed is the same. There are no significant dependencies to speak of... I did full trace (via RAISE NOTICE calls) and the SQL string being executed was exactly the same for both users, and did not involve calls to user-defined funcs.
We upgraded to 8.1.4 overnight, and I rewrote my code to use quote_literal() function, and that seems to have helped - at least results are now consistent across users. I still have no idea what was the cause, but it was definitely there....
Peter