Interesting -- what DB/Driver were you using? As I understand (understood) it, the PreparedStatement should be parsed and compiled, with the values bound in to placeholders in the compiled expression. If I recall, this is exactly how OCI on Oracle works when preparing statements, and also how Perl/DBI works (with the right drivers), so I figured JDBC would implement a similar regimen. Of course, the driver implementor should have the option of just storing the SQL and binding values into that before passing it off to the DB, but that would be naturally less efficient (though more portable). I'd be interested to hear if anyone can point me to the exact specifications that driver implementors use when implementing the JDBC interfaces: a quick dig on the Sun site didn't turn up too much -- the specs seemed a little brief, and the javadocs aren't especially explicit.
Cheers, Chris -----Original Message----- From: A mailing list about Java Server Pages specification and reference [mailto:[EMAIL PROTECTED]]On Behalf Of Chen, Gin Sent: Thursday, November 15, 2001 9:38 AM To: [EMAIL PROTECTED] Subject: Re: Login Authentication against database... Actually Chris. I've had problems with even that. The resulting SQL is pretty much the same so it will cause problems anyways. At least this is true of JDBC 2.0 -Tim -----Original Message----- From: Chris Tucker [mailto:[EMAIL PROTECTED]] Sent: Thursday, November 15, 2001 12:34 PM To: [EMAIL PROTECTED] Subject: Re: Login Authentication against database... Bob, I would hope that people are escaping any SQL characters in the username parameter in the example below...anything else would be plain bad practice. If you want JDBC to handle that stuff for you, you can do it robustly with: String psql = "SELECT * FROM SAMM.UsersLogin WHERE LOWER(UserLoginId) = LOWER(?) AND UserPassword = ?"; PreparedStatement psth = conn.prepareStatement(psql); psth.setString(1, username); psth.setString(2, password); ResultSet rs = psth.executeQuery(); Chris -----Original Message----- From: A mailing list about Java Server Pages specification and reference [mailto:[EMAIL PROTECTED]]On Behalf Of Bob V� Sent: Thursday, November 15, 2001 7:43 AM To: [EMAIL PROTECTED] Subject: Re: Login Authentication against database... For those of you who use this method of login authentication, use the following username/password in your login page when you're done coding and see what happens: UserName: x' or 1=1-- Password: x >From: Chris Tucker <[EMAIL PROTECTED]> >Reply-To: A mailing list about Java Server Pages specification and >reference <[EMAIL PROTECTED]> >To: [EMAIL PROTECTED] >Subject: Re: Login Authentication against database... >Date: Wed, 14 Nov 2001 11:29:59 -0800 > >MessageYou shouldn't need to do any comparisons at all in your code. Use: > >String sqlStr = "SELECT * FROM SAMM.UsersLogin WHERE LOWER(UserLoginId) = >LOWER('"+username+"') AND UserPassword = '"+password+"'"; >stmt = myConn.createStatement(); >myResultSet = stmt.executeQuery(sqlStr); >if( myResultSet.next() ) { > // we have a valid user! >} >else { > // we don't have a valid user! >} > >And make sure you catch and log any SQLExceptions that may occur, as >they'll >help you out no end in debugging... > -----Original Message----- > From: A mailing list about Java Server Pages specification and reference >[mailto:[EMAIL PROTECTED]]On Behalf Of Praveen Potineni > Sent: Wednesday, November 14, 2001 11:20 AM > To: [EMAIL PROTECTED] > Subject: Re: Login Authentication against database... > > > That's exactly what i did. I got only one record and test if the user >exist. Else it goes to login screen. > But i still get the same error. Well i figured that i'm getting problem >comparing the 2 strings... > string coming out of database and the string entered by user. Can u >check >the code and suggest me on this... > Thanks in advance > Praveen > > String sqlStr = "SELECT * FROM SAMM.UsersLogin WHERE UserLoginId = >'"+username+"' AND UserPassword = '"+password+"'"; > stmt = myConn.createStatement(); > myResultSet = stmt.executeQuery(sqlStr); > if(myResultSet.next() == false) > { > log("resulset is null."); > } > else{ > log("resultset is true"); > > String uid = myResultSet.getString("UserLoginId"); > String upin = myResultSet.getString("UserPassword"); > > if ((username.equalsIgnoreCase(uid)) && >(password.equalsIgnoreCase(upin))){ > validUser = "true"; > } > else{ > validUser = "false"; > } > log("validUser is : " +validUser); > } > myResultSet.close(); > stmt.close(); > } > catch(SQLException sqle){ > //System.out.println("User Does not exist Exception: >+sqle.toString()"); > //log("DBObject.validUserExists: Exception: "+sqle.toString()); > } > return validUser; > } > ----- Original Message ----- > From: Joe Cheng > To: [EMAIL PROTECTED] > Sent: Wednesday, November 14, 2001 1:45 PM > Subject: Re: Login Authentication against database... > > > Praveen- > > It looks like your query is retrieving the whole set of users and then >iterating in Java to see if any of them match the username/password the >user >entered. Why would you do that, rather than simply: > > SELECT * FROM users WHERE username = '<username entered by user>' AND >password = '<password entered by user>'; > > and see if any rows are returned. If there are no rows, the username >and/or password was wrong. This way you don't have to deal with so much >data, making it potentially much faster and less memory intensive. > > -jmc _________________________________________________________________ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp =========================================================================== To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST". For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST". Some relevant FAQs on JSP/Servlets can be found at: http://archives.java.sun.com/jsp-interest.html http://java.sun.com/products/jsp/faq.html http://www.esperanto.org.nz/jsp/jspfaq.jsp http://www.jguru.com/faq/index.jsp http://www.jspinsider.com =========================================================================== To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST". For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST". Some relevant FAQs on JSP/Servlets can be found at: http://archives.java.sun.com/jsp-interest.html http://java.sun.com/products/jsp/faq.html http://www.esperanto.org.nz/jsp/jspfaq.jsp http://www.jguru.com/faq/index.jsp http://www.jspinsider.com ========================= To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST". For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST". Some relevant FAQs on JSP/Servlets can be found at: http://archives.java.sun.com/jsp-interest.html http://java.sun.com/products/jsp/faq.html http://www.esperanto.org.nz/jsp/jspfaq.jsp http://www.jguru.com/faq/index.jsp http://www.jspinsider.com =========================================================================== To unsubscribe: mailto [EMAIL PROTECTED] with body: "signoff JSP-INTEREST". For digest: mailto [EMAIL PROTECTED] with body: "set JSP-INTEREST DIGEST". Some relevant FAQs on JSP/Servlets can be found at: http://archives.java.sun.com/jsp-interest.html http://java.sun.com/products/jsp/faq.html http://www.esperanto.org.nz/jsp/jspfaq.jsp http://www.jguru.com/faq/index.jsp http://www.jspinsider.com
