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.
midrange.com
