Contents


Dynamic SQL support in Informix Dynamic Server Stored Procedure Language

New versatility in IDS 11.5

Comments

Dynamic SQL refers to SQL statements that can be constructed and executed dynamically during run time, based on supplied user input. Many database applications need dynamic SQL capability when queries are not fully known up front during application design or implementation phases.

Informix Client-SDK's ESQL/C and 4GL programming language already supports such functionality at the client level. Informix tools, such as dbaccess and 4GL-ISQL, are classic examples that illustrate how the dynamic SQL mechanism accepts user input, such as table name and column information, and then dynamically construct DDL statements to create or alter table objects. This article discusses the dynamic SQL feature within the Informix SPL.

Introduction to Informix SPL

IDS SPL is an extension to SQL for implementing business logic in the form of modules in the database server. These modules are parsed, optimized, and their execution plans are stored in the system catalog tables, which later can be executed by the IDS upon a user request. The fact that execution plans are stored ahead of time means you save time when the same routine needs to be executed multiple times. In addition, this scheme allows other database users to access and execute the same routine.

Review of static SQL

SQL statements that are known at the time the procedure is created are called static SQL statements. Static statements have advantages, because these SQL statements are checked for syntactical and semantic correctness and optimized during procedure compilation. The SPL executor avoids all these steps to achieve the best performance at run time.

With only static statements support in SPL, the requirement of SQL statements that are expected to be known only during run time (called dynamic SQL) cannot be achieved. On the other hand, with static statements, applications cannot write SPL code, which determines the SQL to execute at run time. That'll have to be done either at the client application program or using the EXEC BLADELET[1].

Listing 1 is an example of generating SPL at runtime and simulating a dynamic statement within the procedure with varying table name and database name.

Listing 1. Generating SPL at run time
dynproc.sh:

dbname=$1
tabname=$2

dbaccess $dbname - << EOF
   DROP PROCEDURE dynproc;
   CREATE PROCEDURE dynproc() RETURNING integer;
	DEFINE l_fname CHAR(30);
	DEFINE l_lname CHAR(30);

	SELECT fname, lname INTO l_fname, l_lname FROM $tabname;

	-- Process the fetched data

	...		   

   END PROCEDURE;

   EXECUTE PROCEDURE dynproc();
   EOF

CREATE PROCEDURE crt_dynproc(database_name CHAR(30), table_name CHAR(30))
	system "dynproc.sh " || database_name || " " || table_name
END PROCEDURE;

EXECUTE PROCEDURE crt_dynproc("stores", "customer");

This workaround has a negative performance impact. In the above example, the procedure is recreated whenever it is executed.

ESQL/C, JDBC, or any other client API can also be considered for writing functions that supports dynamic SQL. This involves network traffic between the client application and the database server for every row fetched.

The dynamic SQL feature introduced inn SPL for IDS 11.5 solves these problems.

Dynamic SQL

The SQL statements determined at SPL run time during when the statements are compiled and executed are called dynamic SQL statements. Dynamic SQL allows the execution of statements that can be constructed at run time, thus allowing the execution of different statements using a single procedure.

The following example illustrates an SPL routine with dynamic SQL. The demo database "stores_demo" created by "dbaccess" is used in all the examples.

Listing 2. SPL routine with dynamic SQL
DATABASE stores_demo;

-- Procedure to dynamically constructs query `cust_qry' using supplied
-- table_name and returns all rows whose first name is the supplied
-- first_name

CREATE PROCEDURE customer_details(table_name CHAR(30),first_name CHAR(30))
RETURNING INTEGER, CHAR(30), CHAR(30), CHAR(30);

   DEFINE cust_qry VARCHAR(250);
   DEFINE l_cust_num INTEGER;

   DEFINE l_fname CHAR(30);
   DEFINE l_lname CHAR(30);
   DEFINE l_state CHAR(30);

   -- Construct a Dynamic query using SPL argument table_name

   LET cust_qry = "select customer_num, fname, lname, state from " 
		||table_name || " where fname = ?"; 

   -- Prepare the above constructed query
   -- Get the statement handle "statement_id"

   PREPARE stmt_id FROM cust_qry;

	-- Declare the cursor for the prepared "statement_id"
	-- get the cursor handle "cust_cur"

	DECLARE cust_cur cursor FOR stmt_id;

           -- Open the declared cursor using handle "cust_cur"
           -- Supply the first_name as an input. This will be
           -- substituted in the place of "?" in the query

	   OPEN cust_cur USING first_name;

	   WHILE (1 = 1)

                -- Fetch a row from the cursor "cust_cur" and store
                -- the returned column values to the SPL variables

		FETCH cust_cur INTO l_cust_num, l_fname, l_lname, l_state;

                -- Check if FETCH reached end-of-table (SQLCODE = 100)
                -- if so, exit from while loop; else return the columns
                -- and continue

		IF (SQLCODE != 100) THEN
		   RETURN l_cust_num, l_fname,l_lname, l_state WITH RESUME;
		ELSE
                   -- break the while loop
		   EXIT;
		END IF

	   END WHILE

           -- Close the cursor "cust_cur"

	   CLOSE cust_cur;

           -- Free the resources allocated for cursor "cust_cur"

           FREE cust_cur ;

           -- Free the resources allocated for statement "statement_id"

           FREE stmt_id ;

END PROCEDURE;

-- Execute the procedure; Look for rows with first name "Raymond" 
-- in "customer" table

EXECUTE PROCEDURE customer_details("customer", "Raymond");

The cust_qry could be different as long as the query produces the expected types of output. The following sections discuss each of the dynamic statements used in the above example.

Glossary

  • statement ID: Identifier declared for the prepared object.
  • cursor ID: Identifier declared for the cursor object.

IDS 11.5 supported dynamic SQL statements in SPL

Dynamic SQL statements in SPL are classified into two types: non-cursor and cursor statements.

Non-cursor statement: Can execute SQL statements that can return at most a single row[4]:

  • EXECUTE IMMEDIATE


Cursor statements: The result set of an associated query can be retrieved:

  • PREPARE
  • DECLARE
  • OPEN
  • FETCH
  • CLOSE
  • FREE


The syntax for dynamic SQL statements within Informix SPL are inherited from Informix ESQL/C language.

EXECUTE IMMEDIATE statement

The EXECUTE IMMEDIATE statement makes it easy to execute a single SQL statement dynamically that is constructed during procedure execution. For example, CREATE, INSERT, and GRANT statements are valid non-cursor SQL statements. IDS releases the resources allocated for executing the supplied statement as soon as the execution is complete (unlike the cursor statements where the state and resources are maintained until an explicit CLOSE/FREE statement).

Listing 3. EXECUTE IMMEDIATE statement example
DATABASE stores_demo;
	   
CREATE PROCEDURE create_tab (table_name CHAR(128), column_list CHAR(512))

   DEFINE l_crtstmt   CHAR(1024);

   LET l_crtstmt = "CREATE TABLE " || table_name ||"("|| column_list || " )";

   EXECUTE IMMEDIATE l_crtstmt;

END PROCEDURE;

EXECUTE PROCEDURE create_tab ("tmp_cust","cust_num INTEGER,cust_fname CHAR(30)");

PREPARE statement

The PREPARE statement syntactically verifies the supplied statement (SELECT / EXECUTE PROCEDURE) and creates a prepared object that is preserved for subsequent usage by the DECLARE statements that uses statement ID. The scope of this prepared statement ID is the procedure in which it is defined.

Listing 4. Prepare statement example
CREATE PROCEDURE customer_details(table_name CHAR(30),first_name CHAR(30))

   ...

   LET cust_qry = "select customer_num, fname, lname, state from " 
		||table_name || " where fname = ?"; 

   PREPARE stmt_id FROM cust_qry;

   ...

END PROCEDURE;

DECLARE statement

The DECLARE statement associates a cursor with a statement ID returned by the PREPARE statement. The cursor identifier, or cursor ID, returned by DECLARE can be used with subsequent SQL statements like OPEN, FETCH, CLOSE, and FREE. The scope of the declared cursor is the procedure in which it is defined.

Listing 5. DECLARE statement example
CREATE PROCEDURE customer_details(table_name CHAR(30),first_name CHAR(30))

   ...

   LET cust_qry = "select customer_num, fname, lname, state from " 
		||table_name || " where fname = ?"; 

   PREPARE stmt_id FROM cust_qry;
   DECLARE cust_cur cursor FOR stmt_id;

   ...

END PROCEDURE;

A default cursor can fetch rows one after the other, but when the transaction is ended by COMMIT or ROLLBACK the cursor is automatically closed. It has to be reopened if necessary. It is also possible to declare a hold cursor using the WITH HOLD clause, in which case the cursor remains OPEN even when the transaction ends. Therefore, a hold cursor allows uninterrupted access to a set of rows across multiple transactions.

Listing 6. WITH HOLD clause example
CREATE PROCEDURE customer_details(table_name CHAR(30),first_name CHAR(30))

   ...

   PREPARE stmt_id FROM cust_qry;
   DECLARE cust_cur cursor WITH HOLD FOR stmt_id;

   ...

END PROCEDURE;

OPEN statement

The OPEN statement opens a declared cursor, dynamically optimizes the query based on the supplied inputs (if any), and preserves the execution plan available for subsequent cursor operations.

Listing 7. OPEN statement example
CREATE PROCEDURE customer_details(table_name CHAR(30),first_name CHAR(30))

   ...

   PREPARE stmt_id FROM cust_qry;
   DECLARE cust_cur CURSOR FOR stmt_id;
   OPEN cust_cur; 

   ...

END PROCEDURE;

The OPEN statement also supports the USING clause to supply input parameters. In order to use the USING clause, the SQL statement that was prepared must have had placeholders represented by "?" (question mark) symbols. Listing 8 illustrates the usage of this.

Listing 8. USING clause example
CREATE PROCEDURE customer_details(table_name CHAR(30),first_name CHAR(30))

   ...

   LET cust_qry = "select customer_num, fname, lname, state from " 
		||table_name || " where fname = ?"; 

   PREPARE stmt_id FROM cust_qry;
   DECLARE cust_cur cursor FOR stmt_id;
   OPEN cust_cur USING first_name;

   ...

END PROCEDURE;

Note: The USING clause variables have a one-to-one correspondence with the query's inputs represented by "?". Hence, the number of variables must be equal to number of "?" in the query and also the corresponding types must match in the order from left to right. The values of these variables are supplied at run time.

FETCH statement

The FETCH statement retrieves a row from the opened cursor and assigns the fetched column values to the SPL variables listed in the INTO clause. The FETCH statement can fetch only one row at a time, and always fetches the next row from the cursor as Informix SPL currently supports only a sequential cursor[5].

Listing 9. FETCH statement example
CREATE PROCEDURE customer_details(table_name CHAR(30),first_name CHAR(30))

   ...

   LET cust_qry = "select customer_num, fname, lname, state from " 
               ||table_name || " where fname = ?"; 

   PREPARE stmt_id FROM cust_qry;
   DECLARE cust_cur cursor FOR stmt_id;
   OPEN cust_cur USING first_name;
   FETCH cust_cur INTO l_cust_num, l_fname, l_lname, l_state;

   ...

END PROCEDURE;

When FETCH goes beyond the last row in the query result set, a new built-in SQLCODE[2] function is set to 100. It is up to the user to check for this SQLCODE and take the appropriate action. When FETCH is invoked beyond the last row, the last fetched values of the INTO variables are preserved. Listing 10 demonstrates the usage of SQLCODE.

Listing 10. SQLCODE example
CREATE PROCEDURE customer_details(table_name CHAR(30),first_name CHAR(30))

   ...

   FETCH cust_cur INTO l_cust_num, l_fname, l_lname, l_state;

   IF (SQLCODE != 100) THEN
         LET result = "f_name " || "l_name ";
   ELSE
         LET result = "No such customer found";
   END IF 

   ...

END PROCEDURE;

CLOSE statement

The CLOSE statement closes the cursor that was previously opened with in the current procedure. Once the cursor is closed, the FETCH statement associated with that cursor cannot be used until the cursor is explicitly reopened using the OPEN statement. After closure of the cursor, OPEN and FREE are the only valid statements with respect to that cursor.

There are scenarios when the cursor will be automatically closed. The end of transaction (COMMIT/ROLLBACK) closes all the cursors except those that are declared as WITH HOLD. Although it is good programming practice to close all the open cursors explicitly before returning from the procedure, SPL executor will automatically close all the open cursors when the procedure terminates. The procedure can be terminated either by the RETURN statement or when an unhandled exception occurs. However, the RETURN ... WITH RESUME statement maintains the state of the cursors and is not closed until the completion of a procedure.

Listing 11. CLOSE statement example
CREATE PROCEDURE customer_details(table_name CHAR(30),first_name CHAR(30))
	
   ...  
	   
   LET cust_qry = "select customer_num, fname, lname, state from " 
            ||table_name || " where fname = ?"; 

   PREPARE stmt_id FROM cust_qry;
   DECLARE cust_cur cursor FOR stmt_id;
   OPEN cust_cur USING first_name;
   FETCH cust_cur INTO l_cust_num, l_fname, l_lname, l_state;

   ...

   CLOSE cust_cur;

   ...

END PROCEDURE;

FREE statement

The FREE statement frees the statement ID or cursor ID and makes the statement ID and cursor ID available for reuse. The FREE statement frees the resources that the database server allocated for the statement ID through the PREPARE statement or the resources allocated for the cursor ID through the DECLARE statement. The cursor must be explicitly closed before it is freed.

Listing 12. FREE statement example
CREATE PROCEDURE customer_details(table_name CHAR(30),first_name CHAR(30))

   ...

   LET cust_qry = "select customer_num, fname, lname, state from " 
         ||table_name || " where fname = ?"; 

   PREPARE stmt_id FROM cust_qry;
   DECLARE cust_cur cursor FOR stmt_id;
   OPEN cust_cur USING first_name;
   FETCH cust_cur INTO l_cust_num, l_fname, l_lname, l_state;
	   
   ....
						
   CLOSE cust_cur;
   FREE cust_cur ;FREE stmt_id ;

   ...

END PROCEDURE;

All the cursors and statements are automatically freed when the procedure is ended by using the RETURN statement, at the end of the procedure, or when an unhandled exception occurs. The FREE statement ID only releases the resources allocated for the statement, and the cursor, based on this statement ID can still be used and the other way around.

Security considerations

The IDS user who is the owner of the SPL routine might hold privileges to access database tables that the PUBLIC group and most other users cannot access. However, when another user calls the same routine, it runs with the same table access privileges that the owner of the procedure holds. As a result, tables that are not accessible to the user of the routine now become accessible through the permissions granted to the owner of the procedure. This might lead to the accidental misuse. Therefore, carefully design procedures executing dynamic statements.

Possible mistakes while coding SPL using dynamic SQL

When an application fails to properly scrutinize user-supplied input, it is possible for an attacker to alter the construction of SQL statements. When an attacker is able to alter the SQL statements through user-supplied input, it could lead to a security exposure. This kind of altering statements through user supplied inputs is called SQL injection. SQL injection can lead to security issues[3] and it can be destructive as well. Listing 13 illustrates these scenarios.

Listing 13. SQL injection security issues example
CREATE PROCEDURE customer_details(tab_name CHAR(30),first_name CHAR(30))

   ...

   LET cust_qry = "select customer_num, fname, lname, state from " || tab_name || 
         " where fname = '" || first_name || "'";

   ...
 
END PROCEDURE;

In Listing 13, the string "first_name" is not validated before its usage and directly used in the dynamic query construction. If a malicious user passes the first_name as " X' OR 'X' = 'X ", the cust_qry becomes:

SELECT customer_num, fname, lname, state FROM ... WHERE fname = 'X' OR 'X' = 'X'

When this query string is executed, you are exposing all the rows from the table that is not originally intended when coding SPL and might be a security exposure. If this code is used to authenticate the specific user identified by the "first_name", a user can still take control by the above process due to the insecure SPL code. To avoid this, Listing 2 used "?" and accepted input from the USING clause. This is safest way, because the query is actually:

SELECT customer_num, fname, lname, state FROM ... WHERE fname = " X\' OR \'X\' = \'X "

In the above query, the single quotes are actually part of the data.

Listing 14 demonstrates a similar case that is more destructive.

Listing 14. More destructive example
CREATE PROCEDURE delete_rec(tab_name CHAR(128), id CHAR(30))

   ...

   LET delstmt = "delete from " || tab_name || " where id =  '" || id || "'";
   EXECUTE IMMEDIATE delstmt;

   ...

   END PROCEDURE;
	
EXECUTE PROCEDURE delete_rec("customer", "C0660' OR 'X' = 'X");

With the supplied inputs, the "delstmt" becomes:

DELETE FROM customer WHERE id = 'C0660' OR 'X' = 'X'

And the result of this statement is an empty customer table.

Prevent SQL injections in 11.5[3]

All the inputs to the SPL must be properly validated before you use them to construct the dynamic SQL query. In Listings 13 and 14, when the input is analyzed and all the quotes (single and double) are escaped by '\', the issue would not occur. That is, when the input "C0660' OR 'X' = 'X" is converted to "C0660\' OR \'X\' = \'X", then all the single quotes becomes part of data and the entire supplied string becomes an ID value.

Some precautions can be taken to avoid such SQL injections.

In Listing 14, the argument "id" is defined to be CHAR(30), but actually it takes five characters. The rest of the characters leave room for misuse as described in the example. So, defining the ID as CHAR(5) would have reduced the risk.

Performance considerations

As mentioned before, a dynamic SQL statement undergoes compilation and optimization of the given query only during run time (that is, during the EXECUTE PROCEDURE phase). Whereas a static SQL statement is compiled and optimized during the creation of a procedure itself. Therefore, a user needs to keep this in mind before converting any static SQL statements into corresponding dynamic SQL (static SQLs in SPL can be implemented using a FOR EACH statement). The execution time (post optimization phase) for both static and dynamic SQL statement is more or less the same.

However, since the dynamic SQL statements are optimized just before execution of the query, the optimizer may come up with a better execution plan based on the most current statistics of the database objects, and the configuration of the database server[6]. This can help the query to run faster due to a more optimal execution plan.

Conclusion

The dynamic SQL is a handy feature in Informix SPL that provides lot more flexibility over static SQL in terms of addressing the limitations of static SQL. The syntaxes are carefully matched with the ESQL/C dynamic SQL syntax so that it is intuitive for ESQL/C programmers to use this facility from with in the SPL as well. The new SQLCODE provides an explicit error handling mechanism that was internally handled earlier with in the FOR EACH statement. Finally, this article discussed that the dynamic SQL, if not used carefully with in the SPL, could lead to some security issues. However, it also provided some guidelines to secure from unintended operations by validating all the input parameters to SPL that are used to construct the dynamic SQL statements.

References

[2] The new SQLCODE built-in function returns the value of sqlca.sqlcode (indicates the status) for the most recently executed SQL statement. This function expression, which can be invoked only from SPL routines, is useful in error handling and in program logic to exit from a loop after the last row of the active set of a cursor has been processed. The usage of this SQLCODE feature can be extended to static SQL statements as well with the help of ON EXCEPTION ... END EXCEPTION WITH RESUME mechanism. For more information, refer to "What's New in SQL Syntax for Dynamic Server, Version 11.5."

[3] The SPL in IDS 11.5 does not support multiple statements within the non-cursor statement EXECUTE IMMEDIATE. Though it is a limitation, this restriction considerably reduces the risk of other types of SQL injection. To explain this, assume EXECUTE IMMEDIATE can support multi-statements such as EXECUTE IMMEDIATE '<statement-1> ; <statement-2> ; . . .'. Consider in Listing 3, what if "column_list" is passed as x INTEGER); DROP TABLE customer; CREATE TABLE JunkTab (x INTEGER? In such case, this dynamic SQL procedure would have executed CREATE TABLE tmp_tab (x INTEGER); DROP TABLE customer; CREATE TABLE JunkTab (x INTEGER) [Assume: table_name = "tmp_tab"]. Note how the DROP statement has been injected to drop the customer table. This kind of SQL injection is very destructive and therefore care must be taken to handle these cases.

[4] In IDS 11.5, SPL supports only an EXECUTE IMMEDIATE non-cursor statement that does not return any row. However, ESQL/C additionally supports another non-cursor statement EXECUTE ... INTO. The query in this statement can return a single row that is assigned to the SPL variables listed after the INTO clause. For more information, refer to the "IBM Informix ESQL/C Programmers Manual."

[5] The SCROLL cursor allows FETCH to read the same row multiple times and also read the previous or the next row from within the result set of the cursor. ESQL/C supports the SCROLL cursor. For more information on the SCROLL cursor, refer to the "IBM Informix ESQL/C Programmers Manual."

[6] The reason for an optimal plan in dynamic SQL could be due to the latest statistics gathered by UPDATE STATISTICS as opposed to the static SQL that relies on the optimal execution plan that was generated based on the statistics that existed at the time of procedure creation. Also, the database server could have configured differently, say for example, to have additional buffers that could influence the optimizer to choose different execution plan that is optimal.

Acknowledgements

Our sincere thanks to Keshava Murthy, Tom Houston, and Nancy Miller for spending their valuable time reviewing this article and providing various suggestions that helped to refine it. Also thanks to Bharath Sriram for helping us with formatting this article.


Downloadable resources


Related topic


Comments

Sign in or register to add and subscribe to comments.

static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=Information Management
ArticleID=313366
ArticleTitle=Dynamic SQL support in Informix Dynamic Server Stored Procedure Language
publish-date=06122008