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.