You're right, I hadn't spotted that Alan was building a dynamic string with parameter markers, but this seems even more of a strange idea to when you can simply write syntax checked and unescaped SQL in RDI. I'm just glad I'm not the one that has to maintain it 😁
Get Outlook for Android<
https://aka.ms/AAb9ysg>
________________________________
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of John Yeung <gallium.arsenide@xxxxxxxxx>
Sent: Thursday, February 17, 2022 11:32:18 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Embedded SQL and Parameter Markers
On Thu, Feb 17, 2022 at 1:57 PM Tim Fathers <tim@xxxxxxxxxxxxx> wrote:
1) There's an unnecessary prepare for each run.
The main point of Alan's first post in this thread was that the cost
of ONE prepare to cache a statement used many times isn't that big a
cost.
2) RDI will no longer syntax check the statement and all quotes have to be escaped, making it a maintenance nightmare for the next poor bugger
This could be a pain. It would depend to a large extent on how many
actual quotes you need. One of the many reasons I find Python to be a
much nicer SQL host language is that it provides ways to specify
strings such that you wouldn't need to escape anything that could
occur in any SQL statement you're building.
3) You have to properly cleanse the filter strings to ensure O'Reily type searches don't cause the statement to fail
It took me a long time to figure out what you meant by "O'Reily type
searches". Well, I'm still not 100% sure, but I think you meant
searches where the user wants to find a name with an apostrophe in it.
This is a straw man. The subject line of this thread, and all of the
responses that I've read so far, involve parameter markers. So there
aren't any "exposed" raw apostrophes; the parameter passing mechanism
handles them for you.
4) Dynamically building SQL strings is a poor security practice and the root cause of SQL injection attacks. While there might be limited scope for this on a green screen with smalled fields available to take advantage of, its still a bad idea from a security standpoint.
This is precisely the FUD that Alan was trying to challenge. I think
there is a tendency for some people to have a knee-jerk reaction to
the word "dynamic". But there are dynamic statements, and then there
are dynamic statements.
The dynamic statements that no one is suggesting are the ones where
you build a string with no parameter markers, and user input can turn
into arbitrary live SQL syntax. Example:
sqlstring = 'select phone from contacts where name = ' + user_input;
And then executing sqlstring directly. Yeah, that's ripe for a Little
Bobby Tables injection attack. But the following is (or at least can
be) also "dynamic SQL":
sqlstring = 'select phone from contacts where name = ?';
And then you execute sqlstring, passing in user_input as a parameter.
The value of the user_input variable can be anything, including
accidental or malicious SQL code, but it doesn't get "executed" per
se, so it's not a vector for SQL injection. Nor do you have to escape
any apostrophes/quotes within user_input.
Alan explicitly said "always use parameter markers when building the
statements". That kind of dynamic statement incurs very little
performance penalty (one prepare amortized over all the times the
statement is executed) and little if any security penalty (user input
isn't executable), but gives you more flexibility and potentially
better readability/maintainability.
John Y.
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
https://archive.midrange.com/rpg400-l.
Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.
Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
midrange.com
