Problem with Carriage Returns in settings values in PostgreSQL - Smileys

Started by markf, April 14, 2015, 04:12:24 AM

Previous topic - Next topic

markf

(SMF Version: 2.0.9, with portaMx 1.5.3)

I'm seeing an issue with the smiley's admin page, it's showing all the names against the default with "\n" as 2 literal chars between the names.

So the admin looks like:


default   name               url
           --blank--         http://blah/blah/
           --blank--         http://foo/bar/
           Foo\nBar\nBaz     http://a/n/other


Looking at the database I see that the strings are literal slash and 'n' chars instead of a carriage return.
I confirmed this by doing a string length on the field:


# select char_length(value), value from smf_settings where variable = 'smiley_sets_names';
char_length |                   value
-------------+-------------------------------------------
          41 | Alienine's Set\nAaron's Set\nAkyhne's Set
(1 row)


To fix this, I've had to do following:

# update smf_settings set value = E'Alienine''s Set\nAaron''s Set\nAkyhne''s Set' where variable = 'smiley_sets_names';
UPDATE 1

# select char_length(value), value from smf_settings where variable = 'smiley_sets_names';
char_length |     value
-------------+----------------
          39 | Alienine's Set+
             | Aaron's Set   +
             | Akyhne's Set
(1 row)


And now my smiley admin page is working again, or at least showing 3 names in 3 rows instead of all in same one.

I've looked for other strings in the settings table that have literal slash-n strings, and found 1 more:


# select variable, char_length(value), value from smf_settings where value like '%\\n%';
   variable   | char_length |             value
--------------+-------------+-------------------------------
reserveNames |          29 | Admin\nWebmaster\nGuest\nroot
(1 row)


Is it safe to fix this too, and can you guard against it in future?

margarett

First and foremost: I know nothing about PostgreSQL :P

I would assume that the problem should be related to some form of encoding issue, at install or in another point, as that's problem. Did you moved servers, performed any kind of conversion, etc?
I searched this board and found no relevant issues.

Unfortunately I can't really tell you whether or not
a) the way it is it breaks functionality
b) your fix really fixes stuff
I can only advise you to test it out (especially the reserved usernames part ;) ) So try to register a user under a reserved name and see if it fails (it should return an error). If it doesn't, remove the created user, apply your fix and try again.
Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

markf

It looks like this is a default install issue. I recently reinstalled 2.0.9 and manually put in my forum data from my 2.0.5 instance.

The issue is in install_2-0_postgresql.sql:

INSERT INTO {$db_prefix}settings (variable, value) VALUES ('smiley_sets_names', '{$default_smileyset_name}\n{$default_aaron_smileyset_name}\n{$default_akyhne_smileyset_name}');


According to http://www.postgresql.org/docs/8.3/static/sql-syntax-lexical.html: [nofollow]

QuoteAn escape string constant is specified by writing the letter E (upper or lower case) just before the opening single quote, e.g. E'foo'. ... Within an escape string, a backslash character (\) begins a C-like backslash escape sequence, in which the combination of backslash and following character(s) represents a special byte value. \b is a backspace, \f is a form feed, \n is a newline, \r is a carriage return, \t is a tab...

So, as in the solution I found with my update, you have to have an "E" before the single quoted string if you want to have CR chars in the string.

I've applied the fix to the reserveNames too, and it works.

margarett

Se forem conduzir, não bebam. Se forem beber... CHAMEM-ME!!!! :D

QuoteOver 90% of all computer problems can be traced back to the interface between the keyboard and the chair

Advertisement: