Thank you for your response, Chuck. Sometimes I wonder if anyone ever takes any interest in the code samples I post. Even if they do find it interesting, they rarely bother to respond.
I shared the code sample to get people thinking about the possibility using high-level wrappers around IBM's CLI functions. In our case, we came up with a procedure interface, but someone else might come up with a generic OAR interface. There are alternatives to embedded SQL and IBM's precompiler. IMHO RPG code is more streamlined and readable when SQL Select statements are extracted out to an externally described VIEW. And people are not necessarily limited by what IBM may or may not implement in the RPG language, or the precompiler.
For an SQL Where clause, I tend to agree that using the question mark (?) for a parameter marker is more readable. Moreover, if a generic procedure were used to insert actual parameter values in place of question marks, that procedure could check for indicators of SQL injection such as relational operators (<,>,=) and other unexpected characters such as quotes. Thanks for the suggestion.
I generally agree that web browser interfaces are more susceptible to SQL injection, but in this particular case, there is not much exposure. Our application runs under a web portal where a globally unique session ID is assigned to each user only after successful login, and another globally unique ID is assigned when a menu item is selected. A developer can rest assured that any request being routed to the program is processed through a secure interface, as opposed to being sent from an HTTP hacking tool.
Rather than having user's enter district and school parameters, we provide navigational links instead, which users must have appropriate authority, or they won't even be shown on the screen.
-Nathan
----- Original Message -----
From: CRPence <CRPbottle@xxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxx
Cc:
Sent: Thursday, April 12, 2012 11:22 PM
Subject: Re: File Encapsulation Quandary
On 09-Apr-2012 12:34 , Nathan Andelin wrote:
RPG Code:
http://code.midrange.com/d2dfaae4db.html
SQL injection can be fun ;-) What if the value for the char(10)
variables ytmlink.district and ytmlink.school [as presumed input from a
user] are the following [non-delimited by quotation marks] string?:
"'or 1='1 "
If accepted as input, then the expression:
filter = 'DISTRICT = ' + squote + ytmlink.district + squote +
' AND SCHOOL = ' + squote + ytmlink.school + squote;
Evaluates to the string:
DISTRICT = ''or 1='1 ' AND SCHOOL = ''or 1='1 '
Even if the application avoids injection by some means of validity
checking the input, the SQL as coded is not very reusable if\when stored
in the cache "as is", because the values are literals instead of
parameter markers. The SQL is made safe from injection, more directly
enabled capable for reuse, and IMO much more readable when constructed
as a string when using instead the expression:
filter = 'DISTRICT = ? AND SCHOOL = ?';
Regards, Chuck
midrange.com
