Dynamic column and tablename in sql statement postgresql | Dynamic query Postgresql

👤 Diwas Poudel 🕒 26 Aug 2021 📁 FIX

Here we will learn about Dynamic SQL. In addition, we will look at the actual purpose and use of dynamic PostgreSQL.

What is Dynamic SQL?
When to use Dynamic SQL?
Dynamic SQL vs Static SQL in Database Engine
Constructing Dynamic SQL in Postgresql
Method 1: Using the format function
Example 1: Using Single String in generating Dynamic SQL
Example 2: Using Multiple String in generating Dynamic SQL
Example 3: Using SQL Identifier in Dynamic SQL
Example 4: Using dynamic SQL inside PostgreSQL function
Method 2: Using the quote indent function
Method 3: Using the string concatenation function (||)
Advantages of Dynamic SQL
Disadvantages of Dynamic SQL
How SQL injection happens in dynamic SQL in Postgresql?
How to prevent SQL Injection in dynamic SQL in PostgreSQL?

What is Dynamic SQL?

Dynamic SQL explanations are removed by a series of characters entered when the program starts. With dynamic SQL, you can create a more powerful and flexible application, as the full text of SQL statements might not be known at the time of compilation. This dynamic code or part of SQL is either entered by the developer or created by the program itself but we must compromise a little in performance.

When to use Dynamic SQL?

Ans: Sometimes you may need to generate dynamic commands inside your PL/pgSQL functions or procedure, that is, commands which involve different tables and datatypes, and their presence in the queries is known only at the runtime. Then, in this case, you have to use dynamic SQL.

Also in some cases, if your static SQL statements cannot perform or in the case where you really don't know the exact SQL statements to be executed by function or procedure then you have to use dynamic SQL

Dynamic SQL vs Static SQL in Database Engine

Static SQL ( Embedded SQL)	Dynamic SQL (Interactive SQL)
SQL Statements are compiled at compile time.	SQL Statements gets compiled only at run time.
Parsing SQL Statement, validating table/view/procedure, optimization, and generation of application plan is done during compilation.	Parsing SQL Statement, validating table/view/procedure, optimization, and generation of application plan are done during run time.
The performance is really fast.	Comparatively, performance is not that much good as of static SQL because all things are done at run time.
It is less flexible.	It is more flexible.
Example: select id, name from employee where salary > 50000	Example: execute format('select id,name from %I where salary > 50000',table_name);

You can generally generate dynamic SQL in 3 ways:

using the format function. (My Best one)
using quote_ident and quote_literal functions
using the string concatenation operator (||) pronounced as pipe

Note: Dynamic values that are to be inserted into the constructed query require careful handling because they may also contain quote characters.

Constructing Dynamic SQL in Postgresql

Method 1: Using the format function

Before going directly into the example we must understand certain string formats:

%s	S formats the value of the argument as a simple string.
%I	I treat the value of the argument as a SQL identifier and double-quote it if necessary.
%L	L quotes the argument value as an SQL literal.

Before moving ahead, let's discuss SQL Identifier and SQL Literal in short

SQL Identifier: SQL Identifier is essentially a name of a database, table name, index name, schema name, constraint name, cursor name, trigger name or table column, view name. In dynamic SQL %I format is replaced by SQL Identifier.

SQL Literal: A literal is an explicit numerical, character, string, or boolean value which is not represented by an SQL identifier. There are different types of SQL literal.

Some are :

String Literal	Eg. 'Hello! everyone'
Integer Literal	Eg. 45, 78, +89 , -465,6E5
Decimal Literal	Eg. 45.56
DateTime Literal	Eg. '5/20/2020' , TIMESTAMP '2020-05-20 12:01:01';
Character Literal	A' '%' '9' ' ' 'z' '('
Boolean Literal	true, false, null
...	...

Now let's go with an example :

Example 1: Using Single String in generating Dynamic SQL

 SELECT format('Hello! Welcome to my %s!', 'Blog') as msg;

Output:

msg

Hello! Welcome to my Blog

Here, the 'Blog' string is inserted dynamically in place of %s and you can send any string.

Note: For general simple strings we are using '%s' format.

Example 2: Using Multiple String in generating Dynamic SQL

  SELECT format('%s! Welcome to my %s! - %s','Hi','Blog','Ourtechroom') as msg;

Output:

msg

Hi! Welcome to my Blog! - Ourtechroom

Here, First, %s is replaced by 'Hi', Second %s is replaced by 'Blog' and the Third %s is replaced by 'Ourtechroom'

Note: For general simple strings we are using '%s' format.

Example 3: Using SQL Identifier in Dynamic SQL


SELECT format('INSERT INTO %I VALUES(%L)', 'tbl_test', 'test');

Output:

INSERT INTO tbl_test VALUES('test')

Here, First, %I is replaced by 'tbl_test' and %L is replaced by 'test'

Example 4: Using dynamic SQL inside PostgreSQL function

CREATE OR REPLACE FUNCTION public.fn_test_dynamic_sql(
	tbl_name text
	sorttype text,
	test_id integer)
    RETURNS TABLE() 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
BEGIN
RETURN QUERY 

EXECUTE format('select 
	be.id,	   
	from %I t
	where m.commodity_id = %s 
	order by t.amount %s',tbl_name,test_id,sorttype);

 END;
$BODY$;

Output:

When you run this function, then fn_test_dynamic_sql(text, text, integer) gets created.

Here, First, %I is replaced by 'any tablename passed in the first parameter in function', the second %s is replaced by anyid passed as the second parameter in the function, the third %s is replaced by 'sorttype' passed as third parameter function eg. desc or asc'.

Method 2: Using the quote indent function

The PostgreSQL quote_ident string function is used to make a given string with suitably double-quoted, so as it can be used as a valid identifier in an SQL statement string when required.

Example 1:


 quote_ident('Hello World');  // "Hello World" 
 
 quote_ident('mytable');  // mytable

 quote_ident('MyTable');  // MyTable

Note: String inside quote_ident is case-sensitive.

Example 2: Actual Implementation in PostgreSQL function

CREATE OR REPLACE FUNCTION public.fn_test_dynamic_sql(
	newvalue text,   
	colname text, 
	keyvalue integer)
    RETURNS void   
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 

AS $BODY$
BEGIN
	execute 'update student set '
        + quote_ident(colname)
        + ' = '
        + quote_literal(newvalue)
        + ' where id = '
        + quote_literal(keyvalue);
	
 END;
$BODY$;

--select * from public.fn_test_dynamic_sql1('abc','name',1)

Here we are generating UPDATE dynamic SQL where colname, newvalue, and keyvalue is passed to PostgreSQL function from outside world say somewhere from backend ex. C#, python. Then it creates queries at runtime. You can similarly generate select, insert, delete queries using quote_ident and quote_literal.

Note: Expressions that contain values that must be literal strings in the constructed command must be passed through quote_literal. These functions take the appropriate steps to return the entered text enclosed in double or single quotation marks, respectively, with any embedded special characters duly executed.

Because quote_literal is marked as "STRICT", it will always return "null" when called by the "null" argument. In the above
For example, if newvalue or keyvalue were zero, the entire dynamic query string will be zero, resulting in an error
from performing. You can avoid your problem by using the quote_nullable function, which works in the same way as quote_literal except that when called from null the argument returns a null string.

So it's better to use quote_nullable if you do not want errors for null cases.



CREATE OR REPLACE FUNCTION public.fn_test_dynamic_sql(
	newvalue text,   
	colname text, 
	keyvalue integer)
    RETURNS void   
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 

AS $BODY$
BEGIN

	execute 'update student set '
        + quote_ident(colname)
        + ' = '
        + quote_nullable(newvalue)
        + ' where id = '
        + quote_nullable(keyvalue);
	
 END;
$BODY$;

Method 3: Using the string concatenation function (||)

CREATE OR REPLACE FUNCTION public.fn_test_dynamic_sql(
	tbl_name text
	sorttype text,
	test_id integer)
    RETURNS TABLE() 
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE 
    ROWS 1000
AS $BODY$
BEGIN
    select ' be.id from ' || tbl_name || ' t where m.commodity_id = ' || sorttype  || ' order by t.amount ' ||  test_id  ||;
 END;
$BODY$;

Now let's look at the advantages and disadvantages of dynamic SQL in PostgreSQL

Advantages of Dynamic SQL

Flexibility in coding
Reduce Repetitive tasks when querying.
If the query plan is not cached then there will not be any side effects of PL/pgSQL.

Disadvantages of Dynamic SQL

Prone to SQL injection and security is compromised if not used properly
As with normal SQL queries, the dynamic queries execution plans cannot be cached so performance loss may happen.
Difficult to debug.
Maintenance is difficult

How SQL injection happens in dynamic SQL in Postgresql?

Ans: Dynamic SQL is prone to SQL Injection. In dynamic SQL, it allows the user to enter his code into queries(like in table name, parameter, etc) and later combines and forms a string and makes SQL injection possible.

Prepared Statement (no SQL Injection)

SELECT * FROM employee WHERE Id = someId

Dynamic SQL (prone to SQL injection)

EXECUTE 'SELECT * FROM employee WHERE Id = ' + someId;

Here someId is user defined value , and he/she can pass the value of someId as ' 45 or 1 = 1' and this is true and where clause is always true and return all the employee result as below

EXECUTE 'SELECT * FROM employee WHERE Id = 45 or 1 = 1; // Which is always true

So, user input is controlling the SQL queries which is bad and most prevent it.

But in a prepared statement, someId is treated as a string so whole '45 or 1 = 1' is treated as a string and will compare Id with '45 or 1 =1" and this is always false and return an empty result set.

So, use dynamic SQL only when needed.

How to prevent SQL Injection in dynamic SQL in PostgreSQL?

Ans: There are various methods for preventing SQL injection and let's discuss each of them.

1) Pass values as values with the use of USING clause as shown below.

CREATE OR REPLACE FUNCTION get_employee_shortinfo(_salary text, _age text,_orderby text,_max int)
RETURNS SETOF employee AS $$
BEGIN
RETURN QUERY EXECUTE
'SELECT id,name,age,salary FROM employee
WHERE (salary > $1 and age = $2)
ORDER BY ' || quote_ident(_orderby) || ' DESC LIMIT $3
USING _salary,_age,_max;
END;
$$ LANGUAGE plpgsql;

Then call it like this:

SELECT * FROM get_employee_shortinfo(45000, 24,'age',10);