CODEWITHSUNDEEP
javamysql
Yash Varshney
Yash Varshney

Reputation: 375

Single quote missing in query

i am passing date in sql query using java.Below is my code that retrieve no result.

java.sql.Date d1=java.sql.Date.valueOf(startDate);
        java.sql.Date d2=java.sql.Date.valueOf(enddate); 
        String url= "jdbc:jtds:sqlserver://"+serverName+"/"+database;;
        String driver = "net.sourceforge.jtds.jdbc.Driver";
        try {
            Class.forName(driver);
            conn = DriverManager.getConnection(url);
            System.out.println("Connected to the database!!! Getting table list...");

            Statement sta = conn.createStatement();


            String Sql = "Select INDEX from Table where DATE between "+d1+" and "+d2;
            System.out.println("sql="+Sql);
             rs = sta.executeQuery(Sql);

            }
        } catch (Exception e) {
            e.printStackTrace();}

Query returns no row because date should be passed as '2015-02-28' but query treats date as 2015-02-28 without single quote.Please suggest.

Upvotes: 0

Views: 1077

Answers (5)

singhakash
singhakash

Reputation: 7919

Add single quotes

String Sql = "Select INDEX from Table where DATE between '"+d1+"' and '"+d2+"'";

but the best option would be using PreparedStatement.

Upvotes: 0

Neeraj Jain
Neeraj Jain

Reputation: 7720

Replace Your Query with 

String Sql = "Select INDEX from Table where DATE between '"+d1+"' and '"+d2+"'";

I suggest you to use PreparedStatement Instead

String query="Select INDEX from Table where DATE between ? and ?";
PreparedStatement ps=con.prepareStatement(query);
ps.setDate(1,d1);
ps.setDate(2,d2);
ps.executeQuery();

Upvotes: 0

Jesper
Jesper

Reputation: 207006

Creating SQL statements by concatenating strings together makes your software vulnerable to SQL injection (if the values of the variables come from user input).

You should use PreparedStatement instead:

PreparedStatement sta =
    conn.prepareStatement("Select INDEX from Table where DATE between ? and ?");

sta.setDate(1, d1);
sta.setDate(2, d2);

rs = sta.executeQuery(Sql);

Upvotes: 1

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

You have multiple issues in the query

  1. INDEX is a reserved word and you need to escape it using backticks http://dev.mysql.com/doc/refman/5.5/en/reserved-words.html
  2. Date should be used within quotes

So the query should be

String Sql = "Select `INDEX` from Table where DATE between '"+d1+"' and '"+d2+"'";

Upvotes: 0

npinti
npinti

Reputation: 52205

Try it like so:

String Sql = "Select INDEX from Table where DATE between '"+d1+"' and '"+d2 + "'";

That being said, you should look into PreparedStatements.

Upvotes: 0

Related Questions