Re: Reduce large amount of logicals in SUBFL pgm, take in another direction

[Charles Wilt <charles.wilt@xxxxxxxxx>]

Dieter,

While I want believe you are correct about DB2's susceptibility to one
common form of SQL injection... namely multiple statements separated
by a semicolon...a single failed test doesn't make a proof.  Not to
mention, what if IBM "enhances" the DB to allow for multiple
statements at a time.  It's possible, given that other DBs allow it.

Also, that is not the only form of SQL injection....

consider

wSqlStmt = 'Select *
                + ' from customers '
                + ' where name like ' + cQUOTE + %trim(nameFilter) + cQUOTE
                + '    and salesrep = ' + %char(salesRepCode);

The above incorporates a business rule that a sales rep may only see
their own customers...

What happens if somebody manages to pass '%'' -- '

Answer, they get back a list of all customers...

SQL injection can also simply be used to find out more than you want
known...I highly recommend reading:
http://www.unixwiz.net/techtips/sql-injection.html

The bottom line, DB2 for i _IS_ susceptible to SQL injection.  The
recommended remediation is to use parametrized queries.  If you're
subject to PCI compliance or are otherwise required or willing to
follow secure coding practices...then you'd better be using
parametrized queries.

HTH,
Charles



On Fri, Aug 26, 2011 at 9:39 AM, D*B <dieter.bender@xxxxxxxxxxxx> wrote:
> 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.