unknown column 'abc' in where clause in mysql

[sandeshyadav]

i am doing a java project using jdbc and swing frames. The following code works

String strupdate = "delete from booktable where Book_ISBN = 88664488";
jcb.stat.executeUpdate(strupdate);

but when i want to make it a little advanced and general by writing

String strdeleteitem = JOptionPane.showInputDialog("Enter the Book_ISBN of the book");

String strupdate = "delete from booktable where Book_ISBN = strdeleteitem";
jcb.stat.executeUpdate(strupdate);

it doesnot work ..
shows the error
unknown column 'strdeleteitem' in where clause in mysql .........


please help me....

[DarrylBurke]

Using Prepared Statements (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)

db

[bnson]

You has try changed code from:

String strupdate = "delete from booktable where Book_ISBN = strdeleteitem";

to:

String strupdate = "delete from booktable where Book_ISBN = " + strdeleteitem;


Good luck!

[DarrylBurke]

You has try changed code from:

String strupdate = "delete from booktable where Book_ISBN = strdeleteitem";

to:

String strupdate = "delete from booktable where Book_ISBN = " + strdeleteitem;


Good luck!

Try it yourself first. It's wrong.

The right way to go is to learn and use PreparedStatement

db

[bnson]

I think this problem is bug, and how to fix it. and I will waiting answer of "sandeshyadav" .^-^.

Thank your sharing.

[DarrylBurke]

I think this problem is bug

It isn't.

db

[sandeshyadav]

Using Prepared Statements (The Java™ Tutorials > JDBC(TM) Database Access > JDBC Basics)

db

Thanx a lot ...... it worked with the Prepared Statement ....... Thanx again.....
I really appreciate ......

[DarrylBurke]

I'm happy you found the tutorial useful. Now to address this:

You has try changed code from:

String strupdate = "delete from booktable where Book_ISBN = strdeleteitem";

to:

String strupdate = "delete from booktable where Book_ISBN = " + strdeleteitem;


Good luck!

You would need to include single quotes as part of the String literal so as to wrap the String variable value passed to the Statement.

String strupdate = "delete from booktable where Book_ISBN = '" + strdeleteitem + "'";

These quirks, which facilitate SQL injection (Google it) and also cause problems with String variable values that themselves contain single or double quote characters, are the reason you should use PreparedStatement -- which takes care of any and all necessary quoting without you having to bother about it.

db

[sandeshyadav]

exactly ...... that is very true sir ........ i broke my head all day trying different quote tricks \'', \' + & and so on .... but none worked ... finally prepared statement gave me the solution ....

[DarrylBurke]

Why you should never use Statement in conjunction with user input:



(From xkcd: Exploits of a Mom)

db

[bnson]

This code for your use "Statement" and you have chosen solution "PreparedStatement", it is the smart choice.
I think I has learn more from "DarrylBurke" when offer a solution, thank you.

[sandeshyadav]

Can i use prepared statement for "(SELECT * FROM ?") ?

This code is showing SQL exception ...... Please help..............

pstat = con.prepareStatement("SELECT * FROM ?");
pstat.setString(1,strtable);
rs = pstat.executeQuery();

rsmd = rs.getMetaData();

int colcount =rsmd.getColumnCount();

for(int i = 1; i <= colcount; i++){

columns.addElement(rsmd.getColumnName(i));

}

while (rs.next()){

Vector<Object> row = new Vector<Object>();

for(int i = 1; i <= colcount; i++){
row.addElement(rs.getObject(i));
}

data.addElement(row);

}

rs.close();
pstat.close();
btable = new JTable(data, columns);
bscrollpane = new JScrollPane(btable);

[DarrylBurke]

You could find this for yourself -- learn to search the net.

db

[sandeshyadav]

thanx but i searched a lot .... when i could not find for a long time i asked . anyways thanx ........

[Tolls]

You can only bind values, not things like column names or table names.

[sandeshyadav]

Thank you ....