Charles Wilt wrote
"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
****************************************
Charles,
I would not have posted, if i wouldn't know how injection works. Starting point of injection attacks is, that a user fills in multiple SQL Strings in an input field, where the programmer was expecting only one string and mounts this string into another string sending to the database.
According to the documentation and my verification, embedded SQL on as400 doesn't allow multiple statements in one prepare or execute! So the risc is reduced to a statement that doesn't work or maybe in a diffrent way. Bringing this to the problem of the OP, nothing harmfull is happening, the result will be comparable to providing selection data without sense, worse case in an error.
Coming now to the discussion how to prevent and how to reduce the risc potential:
First question; what is an sql string? simple answer: it starts with a single apostroph and is terminated with the next apostroph. If the string should contain an apostroph (somebody might have the name O'Hara) it has to be. HexaDecimal strings have an x before the starting apostroph.
In typical code for mounting sql statements, as it might be used in such a subfile application, the starting and ending apostrophs are part of the programms concatenation. The one and only way to open the dor for injection is, to put an apostrophy in the input data, but this would not work, if sufficient input validation is happening (it's needed anyway for Mr. O'Hara). The technic is called filtering, just doubling all apostrophies in the user input; not too hard to do. Using Parameter Markers, the database should do this for you.
In the OPs question we have a prepare cursor statement with a following open, if the prepare doesn't work, the open will fail too, the user will not see any data. The maximum of data, he will see is the selection without any specifications of narrowing down criteria, there is no other risc as an empty selection! Another fact reducing the risc in the OPs example is, that all possibilities to insert data are limited in length, not comparable to web applications, where the injection discussion is coming from.
Coming to the discussion of the riscs:
A highest potential of risc exists, if you are using database APIs allowing to execute multiple statements at once, for as400 there are RUNSQLSTM, STRQMQRY in this case its an open door for injection, even if they are used for preformulated statements enhanced by user input! Never saw a discussion about this in the as400 community, maybe I missed it! If there is a chance to choose a database API only allowing one statement, take this and never take the other one (this is true for some jdbc drivers, one driver allows only one statement, another driver for the same database allows multiple => never take the second one!).
next stage of risc are statements altering data, in this cases it's very easy in all situations to use parameter markers, but this does not replace input validation, which is needed anyway!!! And especialy for cases uf unexpected errors, altering data should never (in words never!!!) be done without commitment controll (is true for RLA too, discussions about this are very rare in the as400 community!). For web applications the risc is very high for all data altering user input, Strings are often unlimited and a subselect could be entered, where a name was expected, but correct filtering would catch this too.
next stage of risc are security validations (the password examples in the discussion), These functionalities have to be treated very diffrent, there are better mechanisms as reading this information from the database as a yes/no check!
lowest level of risc are selection lists as subfiles and for flexible solutions the resulting sql statements for retrieval of the ResultSet might look very diffrent and it might become complicated and more effort to implement this with parameter markers for all user input. In this case you would have too choose between effort, security and risc.
In the as400 community the buzzword injection is often abused from the advocats of RLA, I saw this in the last days even in the context of supplying a date value from a 10 character field, or as argument to use only stored procedures.
Dieter
midrange.com
