MySQL Syntax error exception

Question

I'm not able to execute the query here.It says the above mentioned error.I have tried with 'Select * from customer' query and it is working.I can't figure out where am i going wrong.Please help and thanks in advance.

The full query is- SELECT CUSTOMER_ID,FIRST_NAME,LAST_NAME,COUNTRY,AGE,GENDER,EMAIL_ADDRESS FROM CUSTOMER WHERE FIRST_NAME='SHIVAM';

The error message is- com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErr orException: Unknown coloumn 'SHIVAM' in 'where clause'

DefaultTableModel model;
model=(DefaultTableModel)tblSearchCustomer.getModel();
try{
 Class.forName("java.sql.Driver");
 Connection con= DriverManager.getConnection("jdbc:mysql://localhost:3306/divign","root","password");
 Statement stmt=con.createStatement();

 String sfld=(String) searchfldCB.getSelectedItem();
//this stands for Search Field(ComboBox)
 String op=(String) opCB.getSelectedItem();
//this stands for operator(ComboBox) 
 String crit=criteriaTF.getText();
 //this stands for criteria
 String query="SELECT CUSTOMER_ID,FIRST_NAME,LAST_NAME,COUNTRY,AGE,GENDER,EMAIL_ADDRESS FROM CUSTOMER WHERE" + sfld+""+op+""+crit+" ;";
 //This Query is not Executing
 ResultSet rs=stmt.executeQuery(query);
 while(rs.next()) {
     model.addRow (new Object[ ] {

       rs.getInt(1),rs.getString(2),rs.getString(3),rs.getString(4),rs.getInt(5),
       rs.getString(6),rs.getInt(7)
     } );

 }
rs.close();
stmt.close();
con.close();
 }
catch(Exception e){   
JOptionPane.showMessageDialog(null,e.toString());
}

Please post the generated query and the error message. As a guess, you almost certainly are missing some spaces in WHERE" + sfld+""+op+""+crit+" ; — elixenide
– elixenide, Commented Nov 4, 2015 at 20:06
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErr orException: Unknown coloumn 'SHIVAM' in 'where clause' — Yashw
– Yashw, Commented Nov 4, 2015 at 20:14
SELECT CUSTOMER_ID,FIRST_NAME,LAST_NAME,COUNTRY,AGE,GENDER,EMAIL_ADDRESS FROM CUSTOMER WHERE FIRST_NAME='SHIVAM' ; — Yashw
– Yashw, Commented Nov 4, 2015 at 20:15

Nir Levy · Accepted Answer · 2015-11-04 20:26:36Z

2

When you generate your query, there are no single quotes in your where statement, which means you'll get WHERE FIRST_NAME=SHIVAM, so it tries to compare first_name to a column called SHIVAM which doesn't exist

answered Nov 4, 2015 at 20:26

Nir Levy

13k3 gold badges23 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Gord Thompson Over a year ago

This is the best answer so far, only because it does not offer a "solution" that continues to use dynamic SQL. It would be a much better answer if it mentioned that using a parameterized query would have avoided the problem in the first place, and that all of the other "solutions" are still vulnerable to SQL injection.

Yashw · Accepted Answer · 2015-11-05 16:03:39Z

1

So here is the corrected query-

String query="SELECT CUSTOMER_ID,FIRST_NAME,LAST_NAME,PASSWORD,ADDRESS,COUNTRY,AGE,GENDER,EMAIL_ADDRESS,PHONE_NUMBER FROM CUSTOMER WHERE " +sfld+" "+op+" '"+crit+"' ;";

Notice the inverted commas around crit....now if i type SHIVAM in jTextField (criteriaTF) the name will be executed in MySQL with inverted commas i.e. 'SHIVAM'

answered Nov 5, 2015 at 16:03

Yashw

9111 bronze badges

Comments

Sudha · Accepted Answer · 2015-11-04 20:15:06Z

0

String query="SELECT CUSTOMER_ID,FIRST_NAME,LAST_NAME,COUNTRY,AGE,GENDER,EMAIL_ADDRESS FROM CUSTOMER WHERE" + sfld+""+op+""+crit+" ;";

In the where condition you haven't mentioned the column name to assign the value of sfld+""+op+""+crit.

You have to provide the column name next to where clause.

answered Nov 4, 2015 at 20:15

Sudha

2302 silver badges16 bronze badges

Collectives™ on Stack Overflow

MySQL Syntax error exception

3 Answers 3

1 Comment

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Related