Learning PostgreSQL 11 - Third Edition

Recommended practices for dynamic SQL usage

Dynamic SQL can cause security issues if it is not handled carefully; dynamic SQL is vulnerable to the SQL injection technique. SQL injection is used to execute SQL statements that reveal secure information or even destroy data in a database. A very simple example of a PL/pgSQL function that's vulnerable to SQL injection is as follows:

CREATE OR REPLACE FUNCTION car_portal_app.can_login (email text, pass text) RETURNS BOOLEAN AS $$DECLARE  stmt TEXT;  result bool;BEGIN  stmt = E'SELECT COALESCE (count(*)=1, false) FROM car_portal_app.account WHERE email = \''|| $1 || E'\' and password = \''||$2||E'\'';  RAISE NOTICE '%' , stmt;  EXECUTE stmt INTO result;  RETURN result;END;$$ LANGUAGE plpgsql;

The preceding ...