Re: AW: Identity column

The danger is dynamic SQL where you directly concat user provided criteria
onto you SQL statement.

In the screen shots you provided a link too, you've got a "Select by" line
where the user can enter company name, last name, first name, ect.

Lets say for instance that the user entered company name is passed into the
program in wCompanySelect. If you are building a statement dynamically
like so:
wSqlStmt = 'Select ...';
...
if wCompanySelect <> *BLANKS;
wSqlStmt += ' and CMPNAME like ' + wCompanySelect;
endif;

That would be bad, I'm directly contacting the user provided wCompanySelect
into my statement.

I wouldn't call it advanced usage. It's probably easier to build such a
SQL statement with lots of optional criteria this way. Especially in the
non-IBM i world. To many "tutorials" show doing this kind of thing, which
is why SQL Injection became such a problem!

If you haven't run across this yet, it's good for a laugh.
https://xkcd.com/327/


Charles


On Tue, Dec 1, 2015 at 11:00 PM, Booth Martin <booth@xxxxxxxxxxxx> wrote:

Thank you for the heads up, and for the explanation in terms I can follow.

To answer your question, if I am reading you right, the danger is in
dynamic SQL. That is way above where I am at. That'd be at least advanced
intermediate? Its not clear if I will ever reach Intermediate although I
have been pleasantly surprised at how SQL has so many choices, but the
choices make sense, and provide simplicity of programming and complexity of
results.

On 12/1/2015 10:41 AM, Charles Wilt wrote:

Booth,

Since you're learning...I have to ask, are you taking care to avoid SQL
Injection?

You should always either use static SQL or if you must use dynamic, then
use parameter markers.

Good (static)
exec sql
update mytbl
set myfld = 'XYZ'
where mykey = :hostvar;

Also good (dynamic with parameter marker)
wSql = "update mytbl
set myfld = 'XYZ'
where mykey = ?";
exec sql
prepare mystm from wSQL;
exec SQL
execute mystm using :hostvar;

Bad (dynamic sql w/o parameter marker)
wSql = "update mytbl
set myfld = 'XYZ'
where mykey = " + hostvar;

Charles


--

This is the RPG programming on the IBM i (AS/400 and iSeries) (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.