You have to stop thinking about the IBM I RPG only side of things. What if you get a new programmer in that copies your coding sytle into another language that will allow the execute of multiple SQL statements? That's how you need to think about it. Yes SQLRPGLE says that ; is invalid token, but other languages will continue to process and that system will be screwed. Best Practices!
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of D*B
Sent: Friday, August 26, 2011 9:39 AM
To: RPG programming on the IBM i / System i
Subject: Re: Reduce large amount of logicals in SUBFL pgm, take in another direction
On 8/1/2011 9:19 AM, Joe Pluta wrote:
"Nothing idiotic about it. As people have been trying to explain (but it's difficult!), the issue has to do with how the statement is parsed. Most specifically, it has to do with the semicolon, which in an interpreted SQL denotes the end of one statement and the beginning of another. So look at this:
update mytable set description = "?" where key = "value"
In an interpreted statement, the user can replace the ? with a carefully crafted set of character that looks like this:
"; drop table myTable;
The final SQL statement to be executed will look like this:
update mytable set description = ""; drop table myTable;" where key = "value"
The first double-quote in the substituted value closes the quoted string, and thus the semicolon is treated as a statement break. This will execute the statement { update mytable set description = "" } (which will incidentally update every row because there's no where clause), then execute the statement { drop table myTable } which will delete myTable, and then attempt to execute { " where key = "value" } which will fail, but the damage has already been done."
********************************
The buzzing word "Injection" is in the air and the explanation above sounds very impressive, but it doesn't work this way!!!
Having a look to the statement above, having in mind, that we are talking about embedded SQL in RPG::
update mytable set description = ""; drop table myTable;" where key = "value"
sending this to the database with prepare or execute immediate will die at the first pair of doublequotes, because the database engine doesn't know any field named "" (in words: BLANK) and this name would be illegal anyway. changing the two doublequotes to four simple quotes, so that the first part of the statement will become legal, the statement will die at the ; (in Words: semicolon), complaining
Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.
Conclusion of this all:
- injection is no reason to avoid dynamic sql and to use static sql or even stuck with rla
- injection is no reason to write unneeded and useless stored procedures
- db2/400 is not as bad, as some friends of it suppose
- check sqlcode or sqlstate after each SQL statement to catch unpredicted error situations
Dieter Bender
PS: yes, I know, parameter markers are more stable than mounting literals, but in this case, its not injection!
--
This is the RPG programming on the IBM i / System i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/rpg400-l.
________________________________
Notice from Bob Evans Farms, Inc: This e-mail message, including any attachments, may contain confidential information that is intended only for the person or entity to which it is addressed. Any unauthorized review, use, disclosure or distribution is strictly prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message and any attachments.
midrange.com
