CODEWITHSUNDEEP
javaservletsjdbc
Akshay
Akshay

Reputation: 65

SQL command not properly ended in servlets

i have a servlet which takes emailid and password from a form, which is then supposed to access the table called hr.faculty, and if the credentials found correct, creates a session. but i am getting an error: java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended when i try to login. plz help.

The code is as follows:

email=request.getParameter("email");
pass=request.getParameter("pass");       
try{
try {
Class.forName(driver);
} catch (ClassNotFoundException ex) {
out.println(ex);
}
Connection con=null;
con=DriverManager.getConnection(oracleURL,username,password);
DatabaseMetaData dmd=con.getMetaData();
Statement s=con.createStatement();
String q="SELECT password FROM HR.faculty WHERE email=" + email;
ResultSet rs=s.executeQuery(q);
pw=rs.getString("password");
if(pw.equals(pass)){
//session creation   
}

Upvotes: 1

Views: 167

Answers (3)

Durandal
Durandal

Reputation: 5663

Looks like a syntax error on your query, you're not using binding variables so you have to do the wrapping yourself. This would work: 

String q="SELECT password FROM HR.faculty WHERE email='" + email+"'";

In general using prepared statements is much preferred as it protects you from SQL injection.

Also looks like your retrieval of the result is invalid, you need to move the cursor in the ResultSet otherwise it will return an error. This would work:

ResultSet rs=s.executeQuery(q);
rs.first();//move to the first result
pw=rs.getString("password");

Upvotes: 2

Nick Humrich
Nick Humrich

Reputation: 15755

I kind of cringe every time I see code like this. You should be using prepared Statements instead. What you are doing allows SQL injection.

It will alleviate the security holes, and your syntax error if you use prepared statements.

Use PreparedStatment s = con.prepareStatement(mySqlQuery); instead of Statement s = con.createStatement();

Then in your sql string, you replace all "input" with a ?, so mySqlQuey = "SELECT password FROM HR.faculty WHERE email=?"

You then replace all question marks with s.setString(1, value). Or if you were replacing it with an int it would be s.setInt(1, value); etc.

The edit to your code:

Connection con=null;
con=DriverManager.getConnection(oracleURL,username,password);
DatabaseMetaData dmd=con.getMetaData();
PreparedStatement s=con.prepareStatement("SELECT password FROM HR.faculty WHERE email=?");
s.setString(1, email);
ResultSet rs=s.executeQuery(q);
pw=rs.getString("password");
if(pw.equals(pass)){
//session creation   
}

Upvotes: 0

Juan Pedro López Frechoso
Juan Pedro López Frechoso

Reputation: 31

Try

String q="SELECT password FROM HR.faculty WHERE email='" + email + "'";

But you look for prepared statements.

Upvotes: 0

Related Questions