CODEWITHSUNDEEP
javajakarta-ee
user1369219
user1369219

Reputation: 39

Getting Error Message (Line 1: Incorrect syntax near 'password'.) while running Servlet

I'm making servlet to work with J2ME, but whenever running this one getting below error message.

Error:

java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'password'.)

public class GetNpostServlet extends HttpServlet
{
@Override
protected void doGet(HttpServletRequest req, HttpServletResponse res) 
                     throws ServletException, IOException
{
// Same code appears in doPost()
// Shown both places to emphasize that data is received thru
// different means (environment variable vs stream), 
// yet processed the same inside the servlet
String acct = req.getParameter("account"),
        pwd = req.getParameter("password");    

String balance = accountLookup(acct, pwd);

if (balance == null)
{
  res.sendError(HttpServletResponse.SC_BAD_REQUEST, 
 "Unable to locate  account.");            
  return;
}

res.setContentType("text/plain");    
PrintWriter out = res.getWriter();
out.print(balance);
out.close();
}

@Override
protected void doPost(HttpServletRequest req, HttpServletResponse res) 
                    throws ServletException, IOException
{
// Same code appears in doGet()
// Shown both places to emphasize that data is received thru
// different means (stream vs environment variable), 
// yet processed the same inside the servlet
String acct = req.getParameter("account"),
        pwd = req.getParameter("password");    

String balance = accountLookup(acct, pwd);

if (balance == null)
{
  res.sendError(HttpServletResponse.SC_BAD_REQUEST, 
"Unable to locate account.");            
  return;
}

res.setContentType("text/plain");    
PrintWriter out = res.getWriter();
out.print(balance);
out.close();
}

/*--------------------------------------------------
 * Lookup bank account balance in database
 *-------------------------------------------------*/
private String accountLookup(String acct, String pwd)
{
Connection con = null;
Statement st = null;
StringBuilder msgb = new StringBuilder("");

try
{
  // These will vary depending on your server/database      
  Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");  
  con = DriverManager.getConnection("jdbc:odbc:acctInfo");

  Statement stmt = con.createStatement();
  ResultSet rs = stmt.executeQuery(
                     "Select balance from acctInfo where account = " +
                     acct + "and password = '" + pwd + "'");      

  if (rs.next())
    return rs.getString(1);
  else
    return null;
   }
   catch (Exception e)
   {                  
    return e.toString();
    }
    }

    }

Upvotes: 0

Views: 1674

Answers (1)

Jon Skeet
Jon Skeet

Reputation: 1503439

Most immediate problem: you're not quoting the acct string, and there's no space after it, so you'll end up with something like:

Select balance from acctInfo where account = fredand password = 'bloggs'

More important problem: you shouldn't be including values in your SQL like this in the first place. You should be using parameterized SQL. Currently your code is open to SQL injection attacks.

Further security problem: Your code suggests that the passwords are being held in plain text. Please don't do this.

Design problems:

  • You appear to be treating an account balance as a string. That's odd. BigDecimal would be a more natural representation.
  • You're catching Exception rather than specific exceptions
  • You're returning an exception string representation as if it were an account balance. Yikes! You should probably actually just let SQLException bubble up to the caller
  • You're never closing the connection, statement or result set

Upvotes: 5

Related Questions