Mastering PostgreSQL 10

Handling quoting

One of the most important things in database programming is quoting. If you are not using proper quoting, you will surely get into trouble with SQL injection and open, unacceptable security holes.

What is SQL injection? Consider the following example:

CREATE FUNCTION broken(text) RETURNS void AS 
$$  
DECLARE 
  v_sql text; 
BEGIN 
  v_sql := 'SELECT schemaname 
            FROM pg_tables 
            WHERE tablename = ''' || $1 || ''''; 
  RAISE NOTICE 'v_sql: %', v_sql; 
  RETURN; 
END; 
$$ LANGUAGE 'plpgsql'; 

In this example, the SQL code is simply pasted together without ever worrying about security. All I am doing here is using the || operator to concatenate strings. This works fine if people run normal queries:

SELECT broken('t_test'); 

However, we have to be ...