This week's book giveaway is in the Clojure forum.
We're giving away four copies of Clojure in Action and have Amit Rathore and Francis Avila on-line!
See this thread for details.
Win a copy of Clojure in Action this week in the Clojure forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Error: SQL Exception

 
Gaurav x Jain
Ranch Hand
Posts: 39
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The following code genrating an error:

<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.*" %>
<%@ page import="javax.naming.*" %>
<%@ page import="javax.sql.*" %>



<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>JDBC with JSP</title>
</head>
<body>
<%
ResultSet rs=null;
String output;
String str;
String str1;
try {
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String dataSourceName = "TestConversion";
String dbURL = "jdbcdbc:" + dataSourceName;
Connection con = DriverManager.getConnection(dbURL, "","");
// try and create a java.sql.Statement so we can run queries
Statement s = con.createStatement();
str=request.getParameter("fromcur");
str1=request.getParameter("tocur");
rs=s.executeQuery("select Con_Rate From TestConv WHERE From_Currency= '" + str + "' AND WHERE To_Currency= '" + str1 + "' " );
System.out.println(rs);
while(rs.next())
{

%>
<%= output=rs.getString("Con_Rate")%>
<%
}
s.close();
con.close();
}
catch(Exception ex)
{
System.out.println("Database Exception"+ex);

}

%>

</body>
</html>

Error:----> Database Exceptionjava.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'From_Currency= 'INR' AND WHERE To_Currency= 'INR''.

Please help
 
amit punekar
Ranch Hand
Posts: 544
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
rs=s.executeQuery("select Con_Rate From TestConv WHERE From_Currency= '" + str + "' AND WHERE To_Currency= '" + str1 + "' " );
 
Goutam Chowdhury
Ranch Hand
Posts: 44
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
From_Currency= 'INR' AND WHERE To_Currency= 'INR''.

Error is To_Currency= 'INR''. It should be To_Currency= 'INR' ''.



 
amit punekar
Ranch Hand
Posts: 544
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

rs=s.executeQuery("select Con_Rate From TestConv WHERE From_Currency= '" + str + "' AND WHERE To_Currency= '" + str1 + "' " );


The second WHERE before "To_Currency" is unnecessary.

And of course as Bear has pointed out refrain from writing Java code in JSP. You can use JST for SQL interaction otherwise.

Regards,
Amit
 
Goutam Chowdhury
Ranch Hand
Posts: 44
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,
Main error is
Your using Where two times

Should be like this
rs=s.executeQuery("select Con_Rate From TestConv WHERE From_Currency= '" + str + "' AND To_Currency= '" + str1 + "' " );

 
Rob Spoor
Sheriff
Pie
Posts: 20381
46
Chrome Eclipse IDE Java Windows
  • 0
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I really suggest you either start using PreparedStatement or use proper validation yourself on the request parameters, because right now your site is highly susceptible to SQL injection. What would you do if I would send as value for "tocur" the following (and nothing for "fromcur"):
The full query would become this:
Oops! Both statements will be executed, and because the WHERE clause of the second one is always true it will clear your entire table.
 
I agree. Here's the link: http://aspose.com/file-tools
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic