Sanitized SQL

CREATE OR REPLACE FUNCTION sanitize_sql(sql_text text) 
RETURNS text AS $$
DECLARE
    cleaned_text text;
    first_part_regex_3words text := '([^[:space:]]+)[[:space:]]+([^[:space:]]+)[[:space:]]+([^[:space:]]+)';
    first_part_regex_2words text := '([^[:space:]]+)[[:space:]]+([^[:space:]]+)';
    first_part_regex_1words text := '([^[:space:]]+)';
    first_part text;
    match_array text;
    from_parts_regex_3words text := '(FROM)[[:space:]]+([^[:space:]]+)[[:space:]]*([^[:space:]]*)';
    from_parts text := '';
BEGIN
    -- Remove multi-line comments (/* ... */)
    cleaned_text := regexp_replace(sql_text, '/\*.*?\*/', '', 'g');
    
    -- Remove single-line comments (-- to end of line)
    cleaned_text := regexp_replace(cleaned_text, '--.*?(\n|$)', '', 'g');
    
    -- Extract the first keyword and up to two words after it
    first_part := array_to_string(regexp_match(cleaned_text,first_part_regex_3words),' ');
    if first_part is null or first_part ILIKE '% FROM %' or first_part ILIKE '% FROM' then
      first_part := array_to_string(regexp_match(cleaned_text,first_part_regex_2words),' ');
      if first_part is null or first_part ILIKE '% FROM' then
        first_part := array_to_string(regexp_match(cleaned_text,first_part_regex_1words),' ');
      end if;
    end if;
    first_part := regexp_replace(first_part, '\(.*','(...)');
    
    -- Find all occurrences of FROM and two words after each
    FOR match_array IN 
        SELECT array_to_string(regexp_matches(cleaned_text,from_parts_regex_3words,'gi'),' ') 
    LOOP
        match_array := regexp_replace(match_array, '\(.*','(...)');
        from_parts := from_parts || '...' || match_array;
    END LOOP;
    
    -- Return combined result
    RETURN first_part || from_parts;
END;
$$ LANGUAGE plpgsql;

postgres=# SELECT sanitize_sql($test$

SELECT pgp_sym_encrypt('123-45-6789', 'my_secret_key') AS encrypted_ssn;

$test$);

        sanitize_sql
-----------------------------
 SELECT pgp_sym_encrypt(...)
(1 row)

postgres=# SELECT sanitize_sql($test$

-- Fetch active users only
SELECT id, name  -- user info
FROM users /* main table */
WHERE active = TRUE; /* status flag */

$test$);

            sanitize_sql
------------------------------------
 SELECT id, name...FROM users WHERE
(1 row)

postgres=# SELECT sanitize_sql($test$

SELECT id, name
FROM users
WHERE id IN (
    /* subquery for high-value customers */
    SELECT user_id  -- link to users.id
    FROM orders
    WHERE total > 100  -- filter expensive orders
);
-- end of query

$test$);

                      sanitize_sql
--------------------------------------------------------
 SELECT id, name...FROM users WHERE...FROM orders WHERE
(1 row)

postgres=# SELECT sanitize_sql($test$

-- recent orders per user
WITH recent_orders AS (
    SELECT user_id, MAX(created_at) AS last_order
    FROM orders
    GROUP BY user_id  /* aggregation */
)
SELECT u.name, r.last_order
FROM users u
JOIN recent_orders r ON u.id = r.user_id;  -- join results

$test$);

                       sanitize_sql
----------------------------------------------------------
 WITH recent_orders AS...FROM orders GROUP...FROM users u
(1 row)

postgres=# SELECT sanitize_sql($test$

INSERT INTO users (name, email, created_at)
VALUES (
    'Alice', -- first name
    'alice@example.com', /* email */
    NOW() /* timestamp */
);
-- new user inserted

$test$);

   sanitize_sql
-------------------
 INSERT INTO users
(1 row)

postgres=# SELECT sanitize_sql($test$

UPDATE users
SET last_login = NOW()  -- set current time
WHERE id = 42 /* target specific user */;  -- done

$test$);

   sanitize_sql
------------------
 UPDATE users SET
(1 row)

postgres=# SELECT sanitize_sql($test$

/*
 * Delete old sessions.
 * Keep data from the last 30 days.
 * Be careful: irreversible.
 */
DELETE FROM sessions
WHERE last_access < NOW() - INTERVAL '30 days';

$test$);

         sanitize_sql
------------------------------
 DELETE...FROM sessions WHERE
(1 row)

postgres=# SELECT sanitize_sql($test$

-- Upsert settings
INSERT INTO user_settings (user_id, theme, notifications)
VALUES (
    1, /* user id */
    'dark',  -- theme
    TRUE  -- notifications on
)
ON CONFLICT (user_id)
DO UPDATE
SET theme = EXCLUDED.theme,  -- overwrite
    notifications = EXCLUDED.notifications;

$test$);

       sanitize_sql
---------------------------
 INSERT INTO user_settings
(1 row)

postgres=# SELECT sanitize_sql($test$

-- mark inactive users
WITH inactive_users AS (
    SELECT id
    FROM users
    WHERE last_login < NOW() - INTERVAL '1 year'  /* cutoff */
)
UPDATE users
SET active = FALSE
WHERE id IN (
    SELECT id FROM inactive_users  -- CTE reference
);

$test$);

                            sanitize_sql
--------------------------------------------------------------------
 WITH inactive_users AS...FROM users WHERE...FROM inactive_users );
(1 row)

postgres=# SELECT sanitize_sql($test$

-- create table if missing
CREATE TABLE IF NOT EXISTS audit_log (  /* audit records */
    id SERIAL PRIMARY KEY, -- identity column
    user_id INT REFERENCES users(id),  /* FK */
    action TEXT NOT NULL,  -- what happened
    created_at TIMESTAMP DEFAULT NOW() /* timestamp */
);

$test$);

  sanitize_sql
-----------------
 CREATE TABLE IF
(1 row)

postgres=# SELECT sanitize_sql($test$

/*
   This query finds top customers.
   It uses multiple CTEs and subqueries.
*/
WITH order_totals AS (
    SELECT user_id, SUM(total) AS lifetime_value
    FROM orders
    GROUP BY user_id  -- one row per user
),
top_customers AS (
    SELECT user_id
    FROM order_totals
    WHERE lifetime_value > 10000  /* threshold */
)
SELECT u.id, u.name, o.lifetime_value  -- main output
FROM users u
JOIN order_totals o ON u.id = o.user_id
WHERE u.id IN (SELECT user_id FROM top_customers)
ORDER BY o.lifetime_value DESC  /* high to low */
LIMIT 10;  -- top 10

$test$);

                                                 sanitize_sql
---------------------------------------------------------------------------------------------------------------
 WITH order_totals AS...FROM orders GROUP...FROM order_totals WHERE...FROM users u...FROM top_customers) ORDER
(1 row)

postgres=# SELECT sanitize_sql($test$

SELECT * FROM generate_series(1,10);

$test$);

             sanitize_sql
--------------------------------------
 SELECT *...FROM generate_series(...)
(1 row)

postgres=# SELECT sanitize_sql($test$

DO $$
DECLARE
    tbl RECORD;
BEGIN
    OPEN table_cursor;
    LOOP
        FETCH table_cursor INTO tbl;
        EXIT WHEN NOT FOUND;
        EXECUTE 'VACUUM ' || tbl.tablename;
    END LOOP;
    CLOSE table_cursor;
END $$;

$test$);

 sanitize_sql
---------------
 DO $$ DECLARE
(1 row)

postgres=# SELECT sanitize_sql($test$

-- Declare a cursor for employees in Engineering
DECLARE emp_cursor CURSOR FOR
SELECT id, name, salary
FROM employees
WHERE department = 'Engineering';

$test$);

                   sanitize_sql
--------------------------------------------------
 DECLARE emp_cursor CURSOR...FROM employees WHERE
(1 row)

postgres=# SELECT sanitize_sql($test$

SELECT
    c.name AS customer_name,
    o.order_id,
    o.order_date,
    oi.product_name,
    oi.quantity,
    'Orders coming from customers are listed below' AS description
FROM customers c, orders o, order_items oi
WHERE c.customer_id = o.customer_id
  AND o.order_id = oi.order_id
ORDER BY c.name, o.order_date;

$test$);

                       sanitize_sql
-----------------------------------------------------------
 SELECT c.name AS...from customers are...FROM customers c,
(1 row)