Hi Booth,
SQL is the way to go.
It is much more powerful and flexible than native I/O is.
And (almost) the same SQL Statement can be used in other programming
languages. So I you need to replace your green screen subfile program let's
say with a PHP program, you don't need to reinvent the wheel because the
SQL-Statements can be reused with minor modifications.
Another goal when we are talking about modernization (database) is to move
as much business logic into the data base as possible. But instead of coding
everything directly in the programs, I'd use SQL views for masking complex
queries. Within the source code I'd only add a few WHERE CONDITIONS and an
ORDER BY clause.
The danger with dynamic SQL is, it is not built before runtime and easily
could be replaced with a different SQL statement.
For example someone is accessing the database through a web application or
ODBC access. A dynamic SQL statement could be easily replaced or enhanced
with a different SQL statement.
Let's say: 'Select * from YourTable where x = ' + WhatEver;
Whatever could be replaced with a single value and it works as expected. But
it also could be replaced with something like 'SelectValue or 1=0'. In this
way not only rows with the SELECT value are returned, but all rows. In this
way someone could filter (and consequently also misuse) data he is not
allowed to do.
Meanwhile there are good techniques for protecting data, such as triggers,
constraints and RCAC (Row and Column Access Control) but unfortunately most
companies don't use these techniques and still only use unprotected physical
and logical files.
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Booth
Martin
Gesendet: Wednesday, 02.12 2015 05:01
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Re: AW: Identity column
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.
midrange.com
