select query error fired in mysql

Question

i am getting this error com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'NIRAV' in 'where clause' when i am trying to execute following code.

package all_forms;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import javax.swing.JOptionPane;

public class Select_operation {

private Connection con=null;
private PreparedStatement pstmt=null;
private ResultSet rs=null;
String query;
public Select_operation(Connection conn)
{
    con=conn;
}
public ResultSet select(String search)
{
    query="select * from student where id="+search+" or name like'"+search+"%'";
    try
    {
        pstmt=con.prepareStatement(query);
        rs=pstmt.executeQuery();
    }
    catch(Exception e)
    {
        JOptionPane.showMessageDialog(null, ""+e, "Error",JOptionPane.ERROR_MESSAGE);
    }
    return rs;
}

}

1) You're using ticks (') around like and not backticks (`) correct? 2) what is the value of search being passed in? Based on your name for this site and the value it's looking at, I'm guessing your searching on your name and the system is interpreting it as a column name instead of a search string. you really should consider using parameterized queries as it will reduce SQL injection and reduce the occurrence of this type of error. — xQbert
– xQbert, Commented May 3, 2013 at 17:30
Got it. JW already figured that out; your query with search = 'NIRAV' would translate to ... WHERE id=NIRAV or name like'NIRAV%', treating the id=NIRAV as comparing columns. Also take JW's advice on the prepared statements - it will lengthen your career :) — Ed Gibbs
– Ed Gibbs, Commented May 3, 2013 at 17:52
@EdGibbs thanks i am learning these all these coding and i really like it thanks for your support. — user2202010
– user2202010, Commented May 3, 2013 at 18:10

John Woo · Accepted Answer · 2013-05-03 17:27:50Z

3

the value of the ID should be wrap with a string because you are supplying a non numeric value of it.

query="select * from student where id='"+search+"' or name like '"+search+"%'";
                                      ^ HERE     ^

you should consider using PreparedStatement to avoid SQL Injection.

// con is your active connection

String sqlStatement = "SELECT * FROM student WHERE id = ? OR name LIKE ?";
PreparedStatement prest = con.prepareStatement(sqlStatement);
prest.setString(1, search);
prest.setString(2, search + "%");
ResultSet _set = prest.executeQuery();

you should also add this line on top of the class

import java.sql.*;

PreparedStatements avoid your code from sql injection.

more on this link

answered May 3, 2013 at 17:27

John Woo

265k70 gold badges509 silver badges500 bronze badges

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

Collectives™ on Stack Overflow

select query error fired in mysql

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related