aspose file tools*
The moose likes JDBC and the fly likes Error: SQL Exception Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Error: SQL Exception" Watch "Error: SQL Exception" New topic
Author

Error: SQL Exception

Gaurav x Jain
Ranch Hand

Joined: Feb 01, 2011
Posts: 39
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

Joined: May 14, 2004
Posts: 514
rs=s.executeQuery("select Con_Rate From TestConv WHERE From_Currency= '" + str + "' AND WHERE To_Currency= '" + str1 + "' " );


Regards,
Amit
Goutam Chowdhury
Ranch Hand

Joined: Jan 20, 2009
Posts: 44
Hi,
From_Currency= 'INR' AND WHERE To_Currency= 'INR''.

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




Thanks And Regards
Goutam Chowdhury (Scjp1.4 86%,SCWCD5 94%)
amit punekar
Ranch Hand

Joined: May 14, 2004
Posts: 514
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

Joined: Jan 20, 2009
Posts: 44
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

Joined: Oct 27, 2005
Posts: 19719
    
  20

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.


SCJP 1.4 - SCJP 6 - SCWCD 5 - OCEEJBD 6
How To Ask Questions How To Answer Questions
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Error: SQL Exception