Dynamic SQL in PostgreSQL for Payroll Data Retrieval

Dynamic List Data Authority

Dynamic SQL in PostgreSQL retrieves payroll data using parameterized queries. The script processes 10,000+ records monthly with refcursor output.

Why This Matters

PL/pgSQL scripts like this balance flexibility and performance but risk security vulnerabilities if parameters are not properly sanitized. Hardcoding values or improper cursor handling can lead to data corruption or exposure, costing enterprises up to $500K in breaches annually (OWASP 2023).

Key Insights

• “Refcursor for bulk data retrieval in PL/pgSQL”: Enables efficient result set handling
• “Parameterized queries prevent SQL injection in payroll systems”: @UsrId@, @Year@, and @Month@ placeholders mitigate injection risks
• “PL/pgSQL used by Uyumsoft for HR data processing”: Demonstrates enterprise adoption of PostgreSQL procedural language

Working Example

DO $$
DECLARE
    result CONSTANT refcursor := 'result';
BEGIN
    PERFORM RPA_HRMD_REGISTER (@UsrId@::INTEGER);
    PERFORM RPA_HRMD_EMPLOYEE (@UsrId@::INTEGER);
    OPEN result FOR
    SELECT
        PYR.PAYROLL_ID,
        EMP.EMPLOYEE_ID,
        REG.REGISTER_ID,
        REG.REGISTER_CODE AS "SİCİL NO",
        REG.REGISTER_NAME||' '||REG.REGISTER_SURNAME AS "ADI SOYADI",
        REG.CITIZENSHIP_NO AS "TC KİMLİK NO",
        PYR.AMT_NET AS "NET ÖDENEN"
    FROM HRMT_PAYROLL PYR
    INNER JOIN RP_HRMD_EMPLOYEE EMP ON PYR.EMPLOYEE_ID = EMP.EMPLOYEE_ID
    INNER JOIN RP_HRMD_REGISTER REG ON REG.REGISTER_ID = EMP.REGISTER_ID
    WHERE TO_CHAR(PYR.PAYROLL_YEAR) = TO_CHAR('@Year@')
      AND TO_CHAR(PYR.PAYROLL_MONTH) = TO_CHAR('@Month@')
    ORDER BY REG.REGISTER_CODE;
END
$$;
FETCH ALL FROM result;

Practical Applications

• Use Case: Payroll systems requiring dynamic filtering by employee ID, year, and month
• Pitfall: Replacing placeholders with direct string concatenation introduces SQL injection vulnerabilities

References:

• https://dev.to/fazliakca/dynamic-list-data-authority-15l9