RE: Embedded SQL and Parameter Markers

Always Validate input. Always Validate input. Always Validate input. Always Validate input. Always Validate input. Always Validate input.


With a knowledge of the table definition, someone could inject a string that “unfilters” hidden records.

For example, say everyone is only allowed to see “Active” records so the dynamic statement was defined as
qte = '''';
Sqlstring = 'Where status='+qte+'active'+qte+' and searchfield= '+qte+ userintput+qte;

Depending on how the user input is scrubbed, someone could enter a string that closes the search and adds an additional condition.

Example:
a' or status <>'active

Something like this is the reason we always take the user input and run it through a step to double all the single quotes in the string to escape them.
This type of attack works in RPG because the intent was not to execute a different statement.

Using parameter markers also solves the issue.

Being aware of SQL injection is the first step to mitigating it.
Blaming Dynamic SQL for a Programmer’s lack of input validation is just another example of blaming the tool.
“It’s the hammer’s fault my finger hurts, not *my* fault for hitting the finger in the first place.”
Always Validate input.

Chris Hiebert
Senior Programmer/Analyst
Disclaimer: Any views or opinions presented are solely those of the author and do not necessarily represent those of the company.

From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Daniel Gross
Sent: Thursday, February 17, 2022 10:43 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Embedded SQL and Parameter Markers


Hi,



I just wanted to clear one thing up - because FUD is something that has to be faught.



In RPG embedded SQL a „Bobby Tables“ attack simply does not work. After being challenged by a fellow programmer group our team tried hard to inject ANY type up SQL statement into a dynamic SELECT - no chance, because the SQLRPGLE „runtime“ does not allow a semicolon or another statement in one string.



We really tried every way we could think, but weren’t able to finish even one successful attack.



So injecting DELETE, UPDATE or DROP statements into simple dynamic SELECTs is just FUD.



Of course can you “break” a program by injecting apostrophes, semicolons or something like that.



Just my 2ct.

Bye

Daniel