Mastering PostgreSQL 12

Handling quoting and the string format

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

What is SQL injection? Let's 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 we are doing here is using the || operator to concatenate strings. This works fine if people run normal queries. Consider the following ...