Table of Contents
- 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.|
select id, name from employee where salary > 50000
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 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 formats the value of the argument as a simple string.
I treat the value of the argument as a SQL identifier and double-quote it if necessary.
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;
|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;
|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');
|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$;
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.
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)
Dynamic SQL (prone to SQL injection)
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
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.
Then call it like this:
- Use this RETURN QUERY EXECUTE to return the query results in one go.
- Use quote_ident() , quote_literal() while concatinating identifiers.
- use format() for concatenating values in sql string