Basically, you use either prepared statements or stored procedures to do all of your data access and scrub the data coming in. This applies to much more than CGIDEV2.
Also note that the i can be somewhat less vulnerable to these attacks when using RPG or COBOL. The reason is that unlike a lot of other languages used for web development (Java, PHP, Net.Data to name a few that can run on i that don't have fixed length string variables. PHP and Net.Data are particularly bad since they don't have strongly typed variables), RPG and COBOL have fixed length string variables. If those variables are fairly short, it limits what someone can do to the SQL statement.
Here is a classic example of a vulnerable statement (assume this is built dynamically in the language of your choice):
SELECT USER_NAME FROM PASSWORDS
WHERE USER_NAME = '<user_name_var>' AND PASSWORD = '<password_var>'
If someone learns a valid user name, they can enter it and comment escape characters as the user name and anything as the password and get a result. Assuming "--" means a comment (it does for Oracle), they could enter "gooduser' --" as the user name and "haha" as the password which would result in this SQL statement:
SELECT USER_NAME FROM PASSWORDS
WHERE USER_NAME = 'good_user' --' AND PASSWORD = 'haha'
Everything after "--" is ignored so they just bypassed the password check.
Also, some languages (like classic ASP) would be default show the query that failed which made a hacker's job easier.
Here's the prepared statement version of the same query:
SELECT USER_NAME FROM PASSWORDS
WHERE USER_NAME = ? AND PASSWORD = ?
When the parameters are evaluated, it doesn't matter what gets entered. The literal values are used and any values that have escape characters in them get marked up so they don't affect the query itself. These types of queries also tend to be faster since the query plan can be cached by the database manager.
If you are using a language that doesn't have the ability to do prepared statements (like Net.Data), you will need to scrub the data to handle marking up special characters. It is also a good idea to do some checking on what's entered. For example, if user names and passwords can only be letters and numbers, you can check them before the query is executed to see if they contain anything else.
Error messages are another big area that is easy to screw up and give away too much information. If someone enters a bad user name or password, displaying an error message that only one of them is wrong will tell someone if they have a valid user name which makes the hacker's job easier.
5 years ago, I was sent to a week long training course on how to write software that doesn't have these types of problems. The course was presented by a company called @stake. It was very good and was an eye opening experience.
Matt
-----Original Message-----
From: web400-bounces@xxxxxxxxxxxx [mailto:web400-bounces@xxxxxxxxxxxx] On Behalf Of Shane_Cessna@xxxxxxx
Sent: Friday, May 30, 2008 9:44 AM
To: web400@xxxxxxxxxxxx
Subject: [WEB400] SQL Injection???
All:
Has anyone using CGIDEV2 implemented a solution to prevent SQL Injection
attacks in your applications? If so, what did you use and how did you do
it?
This is one of my security goals for this year in case you're curious.
Shane Cessna
Senior Programmer
North American Lighting, Inc.
217.465.6600 x7776
--
This is the Web Enabling the AS400 / iSeries (WEB400) mailing list
To post a message email: WEB400@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/web400
or email: WEB400-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at
http://archive.midrange.com/web400.
midrange.com
