Don’t mix glue and SQL

This post isn’t really related to ADF in particular, but it is of benefit. I’ve seen a number of posts (well, OK 2 of them) on the Oracle JDeveloper forum in the past week where someone “glues” literals into their SQL statements like this:

String sqlStmt = “select x from y where username='” + userName + “‘”;

stmt = new PreparedStatement(sqlStmt, 0);

//etc

Now, anyone who reads Ask Tom is already falling out of their chair. The real problem is in the first line of code; first of all, imagine what happens if someone puts this string into userName: x’ or ‘1’ = ‘1

Can you say “SQL Injection?” The second problem with this approach is that for each value of userName, this generates a unique SQL statement, which Oracle has never seen before, and must hard parse. Hard parsing in Oracle, well in most any database, really, is an operation that takes lots of CPU and inherently limits scalability. If you run this query a lot with different values of userName, you’ll bring the system to it’s knees. What the query should do is use binds, like this:

String sqlStmt = “select x from y where username= :1”;

stmt = new PreparedStatement(sqlStmt, 0);

stmt.setString(1, userName);

//etc

/

Now, no matter what that pesky user puts in userName, this code does not expose the security risks as the first one. Additionally, the SQL is the same from call to call (it never changes) – therefore you don’t have the hard parsing problem, either. Now to make the code even better, we could cache the prepared statement and bind/execute on subsequent calls, but I’ll leave that one to you.

Advertisements

One Response to Don’t mix glue and SQL

  1. Steve Muench says:

    The Developer:FRAMEWORKS Oracle Magazine column from March/April 2006 Using Bind Variables explains how to put this good advice into practice with Oracle ADF view objects, and section 5.9 Using Named Bind Variables in the ADF Developer’s Guide for Forms/4GL Developers goes into even more detail for reference.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: