Oracle codec updates

[noloader]

Hi Everyone,

At $dayjob we have a couple of apps that use the Oracle encoder. The apps cannot cut-over to Parameterized Queries/Prepared Statements at the moment. We are looking at the current OracleCodec class and want to make it a little more modern by handling both character escapes and character sequence escapes per the Oracle 10 docs.

I think the Oracle codec needs to handle three cases:

1. Provide existing behavior
2. Provide traditional character escaping
3. Provide Oracle Text (formerly interMedia Text (IMT)) character sequence escaping

Item (1) provides the current behavior for those who want it: https://github.com/ESAPI/esapi-java-legacy/blob/e4fc652ae20fe7ed69d0d18fc59846f0af067153/src/main/java/org/owasp/esapi/codecs/OracleCodec.java#L45

Item (2) provides traditional character escaping on a character-by-character basis. Note: this will fail to escape some reserved words like AND in a text query like SELECT ... WHERE name = '" + safeName + "'", when safeName is something like Dun And Bradstreet.

For Item (2) we really need a lexer/tokenizer. When the token is AND, I believe the codec should escape the reserved word as \AND to ensure it passes to the engine properly. (Corrections, please). There are other reserved words like ABOUT, ACCUM and BT.

Item (3) provides escaping of character sequences. The codec only needs to handle escaping of a closing } since braces are used to denote the sequence. An example of escaping character sequences is given in the Oracle 10 docs as {AT&T}. Everything within the braces are escaped, and it should handle {Dun And Bradstreet} as expected.

The three use cases lead to the next question. How to implement three different behaviors in one codec? I think the answer is a constructor that takes a Mode like with the MySQL codec. However, the Mode ctors are marked as deprecated. Also see https://github.com/ESAPI/esapi-java-legacy/blob/develop/src/main/java/org/owasp/esapi/codecs/MySQLCodec.java#L110 .

Any thoughts?

[kwwall]

@noloader -Thanks for contributing this to the ESAPI discussion board. I understand the need, but for the most part, the OracleCodec, MySQLCodec, and similar were considered to support for very rare edge cases and not so much as an emergency patch for poorly written SQL. That said, as a previous full-time developer for the first half of my career, I can sympathize with the developers who presently find themselves stuck between a rock and a hard place because of previously poor design designs such as not using parameterized SQL statements.

Furthermore, if item #2 on your list is really going to require complete lexical analysis and tokenization for OracleCodec, it seems pretty doubtful that those revisions would make it into any future ESAPI 2.x releases because at this point we are trying to limit the # of "features" that we introduce so we can get on with ESAPI 3.0. For the past 2 or 3 years, we've been doing nothing but patching / bug fixing ESAPI 2.x and other than some privately shared architecture and high-level design discussions, have barely moved the ball on ESAPI 3.0. In that regard though, even if we didn't have to write a Oracle SQL statement lexer from scratch, unless we could find one with very minimal dependencies that has a record of being well-maintained, I'm not sure we would ever consider trying to address the stuff that that you brought up in Item #2. And writing a lexer from scratch in Java would likely require a lot of work in terms of researching the dark corners of Oracle SQL with all its flavors (e.g., is it different for different Oracle versions??) and testing to make sure there are no surprises. If you or someone at your $dayjob would like to contribute a PR, we definitely will do a code review for it and merge it if it looks good and doesn't introduce a bunch of new dependencies, but otherwise, I can't see Item #2 ever making it into ESAPI 2.x releases.

Finally, to answer your last question, yes, I think adding a OracleCodec CTOR that accepts a Mode would be a good approach. One positive benefit is that it would not risk breaking anyone's code if we choose a Mode representing the current processing approach and another benefit is that it would mirror MySQLCodec.