Q for R.M. Menon - about SQL injection

[Bill Johnston]

Firstly, I want to apologise for NOT following proper protocal and questioning you on the thread introducing you new book.

Next, I want to tell you that I've read a fair amount in the two sections available @ the site you posted and find it very readable and quite interesting.

I had asked you the following question (still not 'getting the point of a new thread ;o) ...

Why, in your examples, do you code this:

finally
{
  JDBCUtil.close( rset );
  JDBCUtil.close( pstmt );
}
[code]
... instead of this:
[code]
finally
{
  rset.close();
  pstmt.close();
}

Having looked in the API docs for both v1.4x and v1.5x, I conclude what should also have been obvious to me (I am on EST and I suppose I was just very tired - will folks allow me to get away with that? ... at least this once?) ... these are your own functions and are probably coded something like this:

  private static void JDBCclose( Object _sql )  throws Exception {
    if ( _sql instanceof ResultSet )
      if ( _sql != null ) ((ResultSet)_sql).close();
    if ( _sql instanceof Statement )
      if ( _sql != null ) ((Statement)_sql).close();
    if ( _sql instanceof Connection )
      if ( _sql != null ) ((Connection)_sql).close();
  }

... I hope I'm close?

Anyway, more to the point, your section on SQL injection. From my understanding this is really applicable more from a web-application point of view, right? For a C/S application, why would a person do anything other than something like this:

Connection con = DriverManager.getConnection( url, uid, pwd );

Thanks again, and best regards,

~Bill

[R. M. Menon]

private static void JDBCclose( Object _sql ) throws Exception { if ( _sql instanceof ResultSet ) if ( _sql != null ) ((ResultSet)_sql).close(); if ( _sql instanceof Statement ) if ( _sql != null ) ((Statement)_sql).close(); if ( _sql instanceof Connection ) if ( _sql != null ) ((Connection)_sql).close(); }

--------------------------------------------------------------------------------


... I hope I'm close?

Well, I would use different overloaded methods for ResultSet, Statement etc.
e.g.

private static void close( Statement stmt)

private static void close( ResultSet rset )

I would also do the try catch block and ignore the exception (log it if you want to.) That way I dont have to worry about the exception handling when you invoke the close method...

Anyway, more to the point, your section on SQL injection. From my understanding this is really applicable more from a web-application point of view, right? For a C/S application, why would a person do anything other than something like this:

code:
--------------------------------------------------------------------------------

Connection con = DriverManager.getConnection( url, uid, pwd );

--------------------------------------------------------------------------------

Not sure what you mean by the above code - cant see the connection between that and SQL Injection.
Quote from sample chapter 5 of my book "SQL injection is a technique that enables a hacker to execute unauthorized SQL statements by taking advantage of applications that use input criteria to dynamically build a SQL statement string and execute it."
Whenever you acept inputs from the client and that goes into forming of a SQL statement, SQL injection comes into play. You can either google or you can read sample chapter 5 of my book at http://www.bookpool.com/sm/159059407X

In particular, you can read section "Using Bind Variables Makes Your Program More Secure".

[Bill Johnston]

No no no ... I did not make myself clear. I'll try again.

Firstly, that code was my best guess at what you do, not what I would necessarilly do, in your book. IOW, I did not see the implementation of JDBCclose( Interface i), and so I guessed at it. I was not advocating it, simply asking if that's how you do it.

Secondly, about SQL injection. I did read the section on it. That's how I came to ask the question. In that section, the example used revovled around sending the UID and PWD as parameters to develope an SQL query that in turn was passed to build the java.sql.Statement. So - I am amiss as to why - for C/S at least - one would EVER use an SQL statement to query a table in order to confirm or deny a user. Rather, let the DB do that.

~Bill

[R. M. Menon]

Originally posted by Bill Johnston:
No no no ... I did not make myself clear. I'll try again.

Firstly, that code was my best guess at what you do, not what I would necessarilly do, in your book. IOW, I did not see the implementation of JDBCclose( Interface i), and so I guessed at it. I was not advocating it, simply asking if that's how you do it.

OK - I told you how I would do it - so that settles this question.

Secondly, about SQL injection. I did read the section on it. That's how I came to ask the question. In that section, the example used revovled around sending the UID and PWD as parameters to develope an SQL query that in turn was passed to build the java.sql.Statement. So - I am amiss as to why - for C/S at least - one would EVER use an SQL statement to query a table in order to confirm or deny a user. Rather, let the DB do that.
~Bill

Whether it is client server or web, the client still can pass information to the server that can then be used to dynamically generate a SQL to return information to the client. If this is the case then client server is also vulnerable to SQL injection, is not it?

[Bill Johnston]

What we have here is a failure to communicate

In my first question I was not asking you what you would do to implement those methods. I was asking you what you did ... I presumed that they were shown elsewhere in the book. Thuse I was guessing at your actual, not proposed implementation.

For my second question. I was refering specifically to the example you used regarding SQL injection, not the topic in general. You used as your example, building an SQL query, passing in the UID and PWD that runs against a table.

I just can't see why a person would ever do that, when the standard jdbc Connection object is built by passing in the users UID and PWD - and URL - directly to the DB. Why would anyone ever do what you did? I don't get it.

~Bill

[Ernest Friedman-Hill]

To answer the second question: Imagine a site like JavaRanch with tens of thousands of users, each with their own password. How might you store all those usernames and passwords? That's right, in a database. You use the information to authenticate users, not to authenticate the application as a database client. Now, given that you're storing user credentials in the database, you have to be careful not to allow injection attacks, because they could be used by an attacker to log into the site as a given user without a password (for example.) This has nothing to do with authentication to the database.

[R. M. Menon]

Originally posted by Bill Johnston:
What we have here is a failure to communicate

In my first question I was not asking you what you would do to implement those methods. I was asking you what you did ... I presumed that they were shown elsewhere in the book. Thuse I was guessing at your actual, not proposed implementation.

I hope I get it this time So - as I said in my previous respose - yes that is how I would do it and that is what I show in the book. Yes, there is a class called JDBCUtil that I use throughout the code in my book. This class has overloaded methods called close() to close result set, statement etc.

For my second question. I was referring specifically to the example you used regarding SQL injection, not the topic in general. You used as your example, building an SQL query, passing in the UID and PWD that runs against a table.

I just can't see why a person would ever do that, when the standard jdbc Connection object is built by passing in the users UID and PWD - and URL - directly to the DB. Why would anyone ever do what you did? I don't get it.

~Bill

Well, first of all, people do all things in real world (e.g. using Statement when they should be using PreparedStatement) so never bet on what people do or not do

Secondly, my specific example is kind of not representative of the real world for more than one reasons (as I believe I warned in the book as well) It is just to show the concept. The concept is if you dont use bind variables - you are prone to SQL injection - plain and simple.

Bill, I know you were talking only of the example, but just for other readers - once again, it need not, obviously, be just the username and password. It could be "give me all employees with a salary more than the average salary in a given department. If the user specifis null then do it for all departments". Here, the input is department number. Now that input can be used to dynamically construct a SQL (not necessary to do so in this example). If you dont use bind variables here (even in a client server app), you are prone to SQL injection.

[Bill Johnston]

Originally posted by Ernest Friedman-Hill:
To answer the second question: Imagine a site like JavaRanch with tens of thousands of users, each with their own password. How might you store all those usernames and passwords? That's right, in a database. You use the information to authenticate users, not to authenticate the application as a database client. Now, given that you're storing user credentials in the database, you have to be careful not to allow injection attacks, because they could be used by an attacker to log into the site as a given user without a password (for example.) This has nothing to do with authentication to the database.

Thanks for your reply. I do understand the concept of how SQL injection works. And I thought that Memon's explaination in the section on the book is very clear on this - except that the example that he used to demo it threw me a curve (I read ahead to his post and do that's now addressed as well).

But back in my first post, I thought I made it clear that I was talking about applications, not individuals, and that is why I asked if he was talking about web applications, because then you certainly do need to authenticate the user.

Thus when you say, "This has nothing to do with authentication to the database.", that just does not make sense in the context of a book on JDBC, which is all about DB connectivity and communication.

~Bill

[Bill Johnston]

Originally posted by R. M. Menon:


Well, first of all, people do all things in real world (e.g. using Statement when they should be using PreparedStatement) so never bet on what people do or not do

Secondly, my specific example is kind of not representative of the real world for more than one reasons (as I believe I warned in the book as well) It is just to show the concept. The concept is if you dont use bind variables - you are prone to SQL injection - plain and simple.

Bill, I know you were talking only of the example, but just for other readers - once again, it need not, obviously, be just the username and password. It could be "give me all employees with a salary more than the average salary in a given department. If the user specifis null then do it for all departments". Here, the input is department number. Now that input can be used to dynamically construct a SQL (not necessary to do so in this example). If you dont use bind variables here (even in a client server app), you are prone to SQL injection.

Thank you for your reply as well. If you're talking about web applications I can not agree with you more that you need bind variables. I have a hard time understanding how SQL injection could occur in a process that runs within an institution's job-submission framework and schedule, or that is used as a tool by that institutions pgm'g staff, within that institution's LAN, to query the DB via either JDBC or otherwise. You just don't give access to anyone but the production control staff to run the former, or to anyone but the technical staff to run the later, anymore that you'd give security access to end users to a unix command prompt or to SQL*Plus. Concerning SQL*Plus, while it does allow the use of bind-variables, it also allows submission w/o bind-vars, and the later is how most folks I know usually use it.

So to sum up, I'm really coming at this whole thing from a C/S, LAN, pgm'r point of view, while I think you are concerning yourself more with web application usage. I realized that all along to for the most part, but when I read something like: "using Statement when they should be using PreparedStatement." - which you emphasize in your book excerpt - I am simply questioning how far you would extend that opinion.

~Bill

[R. M. Menon]

Thank you for your reply as well. If you're talking about web applications

I can not agree with you more that you need bind variables. I have a hard time understanding how SQL injection could occur in a process that runs within an institution's job-submission framework and schedule, or that is used as a tool by that institutions pgm'g staff, within that institution's LAN, to query the DB via either JDBC or otherwise. You just don't give access to anyone but the production control staff to run the former, or to anyone but the technical staff to run the later, anymore that you'd give security access to end users to a unix command prompt or to SQL*Plus. Concerning SQL*Plus, while it does allow the use of bind-variables, it also allows submission w/o bind-vars, and the later is how most folks I know usually use it.

So to sum up, I'm really coming at this whole thing from a C/S, LAN, pgm'r point of view, while I think you are concerning yourself more with web application usage. I realized that all along to for the most part, but when I read something like: "using Statement when they should be using PreparedStatement." - which you emphasize in your book excerpt - I am simply questioning how far you would extend that opinion.

~Bill

Well, two things. Even though you may have security in terms of LAN and stuff - you still always want to increase security by using the "defense in depth" principal. So bind variables would still be applicable (though perhaps less critically from security point of view - agreed.)

But you wanna still use PreparedStatement regardless of client server or not for simply performance/scalability reasons (for most applications regardless of client server or web app.)

[Bill Johnston]

Okay, I see your point. And you make a strong case for that as well in the excerpts I read. From what I read, I think I'd like to read more, and as I said I found it quite readable on the whole; the parts you've made available. Thank you.

Still on your - and I well know that you are NOT alone here BTW - strong preference in general and for several reasons for PreparedStatement over Statement: Can you give me an example of how you'd implement the actual usage of the former in place of the later for free-form SQL? I am sure it's possible, but I think program complexity would outweigh the other advantages. Since you are an 'Oracle' person, do you happen to know how SQL*Plus handles it securly? I know this is not stricly a JDBC question, but your book touches on must that is Oracle and not strictly Java as well, so maybe you'll let me get away with it?

Thanks and best regards,

~Bill