CODEWITHSUNDEEP
postgresqlencryptionencodingsubquery
justacoder
justacoder

Reputation: 2704

Decryption Error in Query

I have a large query that encrypts/decrypts one table in PostgreSQL. I added a new conditional to this query and it now throws an error. The original query retrieves all records based on date and inner join relevance (all records from tblSessions that meet the conditions).

The updated query retrieves only records from tblSessions where parent_session_id is not null (any records from tblSessions that have a child record are omitted from results).

tblsessions (only showing fields that pertain to join conditions)

sessionid | decision_id | start_time | end_time | is_comlete | parent_session_id
---------------------------------------------------------------------------------
SERIAL    | BYTEA       | BYTEA      | BYTEA    | BYTEA      | INTEGER DEFAULT 0

In the SQL window of Posgres, Running the second query gives me the following error:

ERROR: function decrypt(integer, "unknown", "unknown") does not exist SQL state: 42883 Hint: No function matches the given name and argument types. You may need to add explicit type casts.

Original query (works):

SELECT z.conditionname, x.name AS domainname, d.decisionName,
                            c.firstname AS counselor_first_name, c.lastname AS counselor_last_name,
                            o.name AS organization_name,
                            encode(decrypt(s.start_time, '####salt####', '###encryption mode###'), 'escape') AS start_time,
                            encode(decrypt(s.end_time, '####salt####', '###encryption mode###'), 'escape') AS end_time,
                            encode(decrypt(s.is_complete, '####salt####', '###encryption mode###'), 'escape') AS is_complete,
                            s.parent_session_id


                    FROM tblDecisions d
                        INNER JOIN tblSessions s ON encode(decrypt(s.decision_id, '####salt####', '###encryption mode###'), 'escape') = d.decisionid
                        INNER JOIN tblCounselors c ON encode(decrypt(s.counselor_ck, '####salt####', '###encryption mode###'), 'escape') = c.campuskey
                        INNER JOIN tblCounselor_to_organization co ON co.counselor_id = c.counselorid
                        INNER JOIN tblOrganizations o ON o.organizationid = co.organization_id
                        INNER JOIN tblDomains x ON x.domainid = d.domain_id

                        INNER JOIN tblConditions z ON z.conditionid = x.condition_id
                    AND encode(decrypt(s.start_time, '####salt####', '###encryption mode###'), 'escape') >= '2012-01-01 00:00:00'

                    AND encode(decrypt(s.is_complete, '####salt####', '###encryption mode###'), 'escape') = 'true'



                    ORDER BY encode(decrypt(s.start_time, '####salt####', '###encryption mode###'), 'escape'), encode(decrypt(s.last_name, '####salt####', '###encryption mode###'), 'escape'), encode(decrypt(s.first_name, '####salt####', '###encryption mode###'), 'escape')

Revised query (doesn't work; commented additions below)

    SELECT z.conditionname, x.name AS domainname, d.decisionName,
                                c.firstname AS counselor_first_name, c.lastname AS counselor_last_name,
                                o.name AS organization_name,
                                encode(decrypt(s.start_time, '####salt####', '###encryption mode###'), 'escape') AS start_time,
                                encode(decrypt(s.end_time, '####salt####', '###encryption mode###'), 'escape') AS end_time,
                                encode(decrypt(s.is_complete, '####salt####', '###encryption mode###'), 'escape') AS is_complete,
                                s.parent_session_id

//////// ADDITION START

                                , (SELECT MAX(encode(decrypt(start_time, '####salt####', '###encryption mode###'), 'escape')) AS start_time
                                FROM tblSessions s2
                                    WHERE encode(decrypt(s2.parent_session_id, '####salt####', '###encryption mode###'), 'escape') = encode(decrypt(s.parent_session_id, '####salt####', '###encryption mode###'), 'escape') )

//////// ADDITION END

                        FROM tblDecisions d
                            INNER JOIN tblSessions s ON encode(decrypt(s.decision_id, '####salt####', '###encryption mode###'), 'escape') = d.decisionid
                            INNER JOIN tblCounselors c ON encode(decrypt(s.counselor_ck, '####salt####', '###encryption mode###'), 'escape') = c.campuskey
                            INNER JOIN tblCounselor_to_organization co ON co.counselor_id = c.counselorid
                            INNER JOIN tblOrganizations o ON o.organizationid = co.organization_id
                            INNER JOIN tblDomains x ON x.domainid = d.domain_id
                            INNER JOIN tblConditions z ON z.conditionid = x.condition_id
                        AND encode(decrypt(s.start_time, '####salt####', '###encryption mode###'), 'escape') >= '2012-01-01 00:00:00'

                        AND encode(decrypt(s.is_complete, '####salt####', '###encryption mode###'), 'escape') = 'true'

/////// ADDITION START

                        AND NOT EXISTS (

                          SELECT 1
                          FROM   tblSessions s1
                          WHERE  encode(decrypt(s1.parent_session_id, '####salt####', '###encryption mode###'), 'escape') = encode(decrypt(s.sessionid, '####salt####', '###encryption mode###'), 'escape') )
                        AND (
                                ( encode(decrypt(s.parent_session_id, '####salt####', '###encryption mode###'), 'escape') IS NULL) OR
                                ( encode(decrypt(s.start_time, '####salt####', '###encryption mode###'), 'escape') = (
                                    SELECT MAX(encode(decrypt(start_time, '####salt####', '###encryption mode###'), 'escape')) AS start_time
                                    FROM tblSessions s2
                                    WHERE encode(decrypt(s2.parent_session_id, '####salt####', '###encryption mode###'), 'escape') = encode(decrypt(s.parent_session_id, '####salt####', '###encryption mode###'), 'escape')
                                 )
                             )
                        )

///////// ADDITION END

                        ORDER BY encode(decrypt(s.start_time, '####salt####', '###encryption mode###'), 'escape'), encode(decrypt(s.last_name, '####salt####', '###encryption mode###'), 'escape'), encode(decrypt(s.first_name, '####salt####', '###encryption mode###'), 'escape')

I understand the error message, but that can't be the actual issue since all additions to the query have been properly decoded. Am I missing something obvious?

Upvotes: 0

Views: 2019

Answers (1)

Daniel Vérité
Daniel Vérité

Reputation: 61506

The question mentions that parent_session_id is type INTEGER but it is passed as the first argument to decrypt in the snippet below, unlike the rest of the code that seem to pass only bytea fields there.

decrypt(s.parent_session_id, '####salt####', '###encryption mode###')

This is likely to provokes the error mentioned, since there's no flavor of decrypt that takes an integer as the first argument (from the error message, could be confirmed with \df decrypt in psql)

Are you sure these session IDs are encrypted?

Upvotes: 1

Related Questions