SQL Injection in RPGLE

[yellowcat]

Hi guys,

I'm trying to understand and learn about SQL Injection. If I say something wrong please correct me.

From what I understand so far SQL Injection is the risk of your query being modified by potential user input. Therefore it is best to use parameterized or static SQL where the query is "pre-compiled/pre-prepared" by the SQL engine and the parameters that you provide the query are substituted into the "ready-to-run" static SQL. Examples of Static SQL are just your basic embedded sql like

exec sql select acct_num into :acct from customers where acct_num = :user_input; 

Another example of safe SQL would be parameterized SQL where we make use of the prepare, execute statements, and substitution variables

stmt = 'update YOURLIB.YOURTBL set color = ? where  name = ?';
sub_name = 'JACK';
sub_color = 'RED';
exec sql prepare s1 from :stmt;
exec sql execute s1 using :sub_color, :sub_name; 

That is also supposed to be good because the 'prepared' statement is pre-compiled by the SQL engine and the ? values are substituted in as variables. Therefore any user input can not modify/manipulate the actual query.


The risk of SQL injection is primarily from dynamic SQL that is not parameterized. Dynamic SQL is the case where your statement can be modified at run time.

However when I try to create an example of a drop table SQL injection in RPGLE I get SQLSTATE errors (42601 )? Is IBM i/RPGLE/DB2 immune to SQL injection? Am I doing something wrong with my example? Where am I going wrong?

this_value = 'black'');DROP TABLE MYTSTTBL';
stmt = 'insert into MYLIB.MYTSTTBL (COLOR) values  (''' + this_value;
exec sql execute immediate :stmt; 

https://www.ibm.com/docs/en/i/7.4?to...qlstate-values (42601 A character, token, or clause is invalid or missing.)

[B.Hauser]

You just try to execute 2 SQL Statements as a single one! That is not allowed!
EXECUTE IMMEDIATE and all of the other (embedded) SQL Commands can only perform a single SQL Statement at time.
IMHO this has nothing to do with SQL Injection.

[TedHolt]

SQL injection is certainly possible with Db2 for i. Here's an example.

https://www.itjungle.com/2005/10/05/fhg100505-story02/