Serialize/Unserialize with plpgsql

Question

I'd like to use plpgsql to perform serialize and unserialize of an php serialized string or array.

Could anybody please tell me if someone has tried this before?

A link to the function which is capable of that would be really appreciated!

Answer 1

Martins function did not work for me.

• first of all it's not postgres 9.4 compliant. I'am using greenplum 6.22 with postgresql 9.4 under the hood.
• second of all it assumes that all elements have length less then 10 byte length.

N.B. First of all, please don't add php arrays to relational database. They are not designed for that. If you have no other choice, like to parse php array with something relevant, like php or any programming language, please, use this function.

I have developed alternative to Martins function. it does not support php array objects properly, but works fine with every other datatype from php arrays

function

create or replace function php_deserialize(str text) returns json as
    $$
    DECLARE
        def_len int;
        data_len int;
        total_len int := length(str);

        p int := 1;
        alpha char := substr(str,1,1);

        stack text := '';
        bytes bytea;

        part text;
        k text; -- key
        v text; -- value

        parsed text := '';

        error_message text;
    BEGIN
        if str is null then return NUll::json; end if;
        if total_len = 0 or length(trim(str)) = 0  then return NUll::json; end if;
        if alpha != 'a' then raise 'expected array got: %', str; end if;

        while  p <= total_len loop
            alpha :=  substr(str,p,1);
--             raise info 'p: %', p;
--             raise info 'stack: %', stack;
--             raise info 'alpha: %', alpha;
--             raise info 'parsed: %', parsed;
            if alpha = '{'
                then
                    if right(stack, 1) = '}'
                        then raise 'invalid braces';
                    else
                        stack := stack || alpha ;
                        parsed := parsed || coalesce( k ||':','') || alpha;
                        k := Null;
                        p := p + 1;
                    end if;
            elsif alpha = '}'
                then
                    if right(stack,1) = '{'
                        then
                            stack := substr(stack,1,length(stack)-1);
                            if substr(reverse(parsed),1,1) = ','
                                then
                                    parsed := reverse(substr(reverse(parsed),2));
                            end if;
                            if length(stack) < 1
                                then
                                    parsed := parsed || alpha ;
--                                     raise info 'stack is closed, exiting parsed data: %', parsed;
                                    return parsed;
                                    exit;
                            else
                                    parsed := parsed || alpha || ',';
                            end if;
                            p := p +1;
                    else
                        raise 'invalid braces expected } or none got { str: %' ,substr(str,p);
                    end if;
--             end if;
            -- parsing and skipping
            elsif alpha = 's' and substring(substr(str,p) from '^s:[0-9]+:') is not null
               then
                  def_len := length(substring(substr(str,p) from '^s\:[0-9]+\:'))::int;
                  data_len := substring(substr(str,p) from '^s\:([0-9]+)\:')::int;
--                   raise info 'part to parse: %', substr(str,p + def_len + 1, data_len );
                  part := replace(substr(str,p + def_len + 1, data_len),'\','\\');
                  bytes := part::bytea; --estimation
                  bytes := substr(bytes,1,data_len)::bytea; -- true data string in bytes
                  part :=  convert_from(bytes, 'UTF-8')::text;
--                   raise info 'part parsed: %', part;
                  data_len := length(part)::int; -- true data length in char
                  p := p + def_len + 1 + data_len + 1;
--                   part := '"' || part || '"';
                  part := to_json(part);
            elsif alpha = 'i' and substring(substr(str,p) from '^i\:\-?[0-9]+') is not null
               then
--                     raise info 'I am i skip block skipping %', substring(substr(str,p) from '^i\:[0-9]+');
                    part := to_json( substring(substr(str,p) from '^i\:\-?([0-9]+)'));
                    p := p + length(substring(substr(str,p) from '^i\:\-?[0-9]+'));

            elsif alpha = 'b' and substring(substr(str,p) from '^b\:[01]') is not null

               then
                    part := substring(substr(str,p) from '^b\:([01])')::boolean::text;
                    p := p + 4;
            elsif alpha = 'N' and   substring(substr(str,p) from '^N;') is not null
                then
                    part := 'null';
                    p := p + 1;
            elsif alpha = 'd' and substring(substr(str,p) from '^d\:\-?[0-9.]+') is not null
                then
                    part := to_json( substring(substr(str,p) from '^d\:(\-?[0-9.]+)'));
                    p := p + length(substring(substr(str,p) from '^d\:\-?[0-9.]+'));
            else
                  p := p + 1;
            end if;

            -- add parsed to parsed
            if k is null
                then
                    k := part;
            elsif v is null
                then
                    v := part;
            end if;

            if k is not null and v is not null
                then
                  parsed := parsed || k || ':' || v || ',';
                  k := null;
                  v := null;
            end if;

            part := null;
--             raise info 'after skipping block p: %', p;
--             raise info 'after skipping block str: %', substr(str,p);
--             raise info 'end loop iteration';
        end loop;

--
        if length(stack) > 0
            then
--                 raise info 'No closing bracket';
                return null::json;
        else
                return parsed::json;
        end if;

    EXCEPTION WHEN OTHERS THEN
        GET STACKED DIAGNOSTICS error_message := MESSAGE_TEXT;
        error_message := error_message || ' [current string] [' || str || ']';
        RAISE 'error message: %', error_message;

    END;
    $$ LANGUAGE plpgsql IMMUTABLE

test cases

drop table if exists php_arrays;
create temp table  php_arrays (id int, str text );
insert into php_arrays values
        (1,'a:1:{s:3:"foo";s:3:"bar"}  '),
        (2,'a:2:{s:3:"foo";a:1:{s:3:"foo";s:3:"bar"}}'),
        (3,'a2:{s:3:"foo";a:2:{s:3:"foo";s:3:"bar";s:3:"bar";s:3:"foo";}}'),
        (4,'a3:{s:3:"foo";a:2:{s:3:"foo";s:3:"bar";s:3:"bar";s:3:"foo";};s:3:"bar";s:3:"foo"}'),
        (5,'a:2:{s:3:"foo";i:3;s:3:"bar";b:1;}'),
        (5,'a:3:{s:3:"foo";i:3;s:3:"bar";b:1;s:6:"foobar";N;}'),
        (7,'a:3:{s:5:"fooN;";i:3;s:3:"bar";b:1;s:6:"foobar";N;}'),
        (8,'a:2:{s:14:"event_partners";a:0:{}s:15:"selective_color";s:7:"#0f68be";}'),
        (9,'a:30:{s:16:"template_version";s:8:"20190920";s:9:"templates";a:1:{s:15:"EventController";a:1:{s:5:"index";s:12:"event_page_3";}}s:17:"seatcheck_version";i:1;s:7:"mapAddr";s:37:"+55.837319738620074,+37.6241507694992";s:14:"event_partners";a:0:{}s:15:"selective_color";s:7:"#0f68be";s:14:"to_description";N;}'),
        (10,'a:1:{s:3:"foo";s:31:"<p><strong>Информация"}'),
        (11,'a:1:{s:3:"foo";s:82:"<p><strong>Информация о событии</strong><br />
Название:"}'),
        (12,'a:1:{s:3:"foo";s:0:""}  '),
        (13,'a:1:{s:11:"hoverFactor";d:2.2;}'),
        (14,'a:2:{s:12:"hover\Factor";d:2.2;s:3:"foo";s:4:"b\ar";}'),
        (15,'a:1:{s:1:"x";d:-138.7060546875;}'),
        (16 , NULL),
        (17 , '  '),
        (18 , ''),
        (19, 'a:1:{s:12:"new_date_end";O:10:"TPDateTime":3:{
                s:4:"date";s:26:"2023-02-15 12:30:00.000000";
                s:13:"timezone_type";i:3;
                s:8:"timezone";s:13:"Europe/Moscow";
                }}')

;

objects semi supported. objects are converted to key value, where value is is body of the object.

example:

input: 'a:1:{
    s:12:"new_date_end";O:10:"TPDateTime":3:{
     s:4:"date";s:26:"2023-02-15 12:30:00.000000";
     s:13:"timezone_type";i:3;
     s:8:"timezone";s:13:"Europe/Moscow";
     }
}'

output: 
{
    "new_date_end":
    {
        "date":"2023-02-15 12:30:00.000000",
        "timezone_type":"3",
        "timezone":"Europe/Moscow"
    }
} 

if invalid array is given then null is returned.

please, feel free to contribute to my open source project on GitHub or to copy project from GitHub

Answer 2

I'm a little bit late to the party, but I've created a postgresql function which does this:

https://gist.github.com/storeman/cecb10bab249f43562cddabc1d9dd7c9

/**
Decode a php serialized value to json. This function only supports basic 
data types:
- arrays (will always become a json object)
- booleans
- integers
- floats
- strings
- NULL

The php_unserialize(text) function is a helper function which extracts the first value
found in the string and returns a ROW( vartype, varval, serializedlength)

The php_unserialize_to_json(text) function returns the json value extracted from
the serialized string. 

Examples:
SELECT php_unserialize_to_json('a:5:{i:0;d:1;s:1:"k";a:3:{i:0;s:1:"a";i:1;s:1:"b";s:3:"sub";a:3:{i:0;s:1:"a";i:1;s:1:"b";i:2;s:1:"c";}}i:1;N;i:2;b:1;i:3;b:0;}')#>>'{k,sub,2}';
SELECT php_unserialize_to_json('s:8:"a string";');
SELECT php_unserialize_to_json('s:0:"";');
SELECT php_unserialize_to_json('i:1337;');
SELECT php_unserialize_to_json('d:1.234;');
SELECT php_unserialize_to_json('b:1;')::TEXT;
SELECT php_unserialize_to_json('b:0;')::TEXT;
SELECT php_unserialize_to_json('N;')::TEXT;
SELECT php_unserialize_to_json('a:0:{}')::TEXT;
SELECT php_unserialize_to_json('a:1:{i:0;s:5:"array";}')::TEXT;

SELECT php_unserialize_to_json('a:1:{i:0;i:1;}')::TEXT;
SELECT php_unserialize_to_json('a:2:{i:0;i:1;i:1;s:1:"b";}')::TEXT;
SELECT php_unserialize_to_json('a:2:{i:0;d:1;s:1:"k";s:1:"b";}')::TEXT;
SELECT php_unserialize_to_json('a:2:{i:0;d:1;s:1:"k";a:2:{i:0;s:1:"a";i:1;s:1:"b";}}')::TEXT;
*/

---
--- This function is the helper function
---

CREATE OR REPLACE FUNCTION php_unserialize(str text)
  RETURNS json AS
$BODY$
DECLARE
  vartype CHAR;
  varlength INT;
  jsonstr TEXT;
  varcount INT;
  jsonval JSONB;
  arrkey JSON;
  arrval JSON;
  -- String length of the serialized data
  serialized_string_length INT;
BEGIN
  CASE substring(str, 1, 1)
    WHEN 'a' THEN -- array
      -- init object
      jsonval := '{}'::jsonb;

      -- remove the "a" and ":" characters
      str := substring(str, 3);

      -- Detect number of values in array
      varlength := substring(str, 1, position(':' IN str) - 1)::INT;

      -- Base size of array is 5 (a:[size]:{})
      serialized_string_length := 5 + char_length(varlength::TEXT);

      -- If no values, return empty object, as this always returns objects
      IF varlength = 0 THEN 
    return json_build_array('array', jsonval, serialized_var_size)::JSON;
      END IF;

      -- remove the array size and ":{"
      str := substring(str, char_length(varlength::TEXT) + 3);

      -- Find the number of variables specified
      FOR varcount IN 1 .. varlength LOOP
    -- Find the value of the key and remove it from base string
    arrkey := php_unserialize(str);
    str := substring(str, (arrkey->>2)::INT + 1);

    -- Find the value of the value and remove it from base string
    arrval := php_unserialize(str);
    str := substring(str, (arrval->>2)::INT + 1);

    serialized_string_length := serialized_string_length + (arrkey->>2)::INT + (arrval->>2)::INT;

    -- Append value
    jsonval := jsonval || jsonb_build_object(arrkey->>1, arrval->1);
      END LOOP;

      return json_build_array('array', jsonval, serialized_string_length);
    WHEN 'b' THEN -- boolean
      return json_build_array('bool',(CASE substring(str, 3, 1) WHEN '1' THEN TRUE ELSE FALSE END)::TEXT, 4);
    WHEN 'd' THEN -- float
      return json_build_array('float', substring(str, 3, position(';' IN str) - 3)::TEXT, position(';' IN str));
    WHEN 'i' THEN -- int
      return json_build_array('int', substring(str, 3, position(';' IN str) - 3)::TEXT, position(';' IN str));
    WHEN 'N' THEN -- null
      return json_build_array('null', 'null'::TEXT, 2);
    WHEN 's' THEN -- string
      varlength := substring(str, 3, position(':' IN str) - 1)::INT;

      return json_build_array('string', substring(str, char_length(varlength::TEXT) + 5, varlength)::TEXT, position(';' IN str));
    ELSE
      RAISE EXCEPTION 'Unable to decode serialized value, unsupported type: %', substr(str, 1, 1);
  END CASE;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 10;

---
--- The main function
---
CREATE OR REPLACE FUNCTION php_unserialize_to_json(str text)
  RETURNS json AS
$BODY$
DECLARE
  varlength INT;
BEGIN
  CASE substring(str, 1, 1)
    WHEN 'a' THEN
      return php_unserialize(str)->1;
    WHEN 'b' THEN
      return php_unserialize(str)->1;
    WHEN 'd' THEN
      return php_unserialize(str)->1;
    WHEN 'i' THEN
      return php_unserialize(str)->1;
    WHEN 'N' THEN
      return php_unserialize(str)->1;
    WHEN 's' THEN
      return php_unserialize(str)->1;
    ELSE
      RETURN NULL;
  END CASE;
END;
$BODY$
  LANGUAGE plpgsql IMMUTABLE
  COST 10;

Answer 3

A dedicated PHP function Serialize/Unserialize doesn't exist for postgre. Look: PostgreSQL Functions

As for the database is an only possibility json, but it doesn't have supporting in PHP for postgres. More about this: JSON Functions and Operators

My solution:

I used serialize and unserialize in PHP.

$array = array('t1' => 1,'t2' => 2,'t3' => 3,'t4' => 4);

$arraySerialize = serialize($array);

print_r($arraySerialize); //a:4:{s:2:"t1";i:1;s:2:"t2";i:2;s:2:"t3";i:3;s:2:"t4";i:4;}

1. Save $arraySerialize in a database column of type text

2. Get $arraySerialize from the database and extract ($stringUnserialize)

   $stringUnserialize = unserialize($arraySerialize); //Array ( [t1] => 1 [t2] => 2 [t3] => 3 [t4] => 4 )