Results 1 to 16 of 16
Like Tree3Likes
  • 2 Post By DarrylBurke
  • 1 Post By DarrylBurke

Thread: unknown column 'abc' in where clause in mysql

  1. #1
    sandeshyadav is offline Member
    Join Date
    Feb 2012
    Posts
    13
    Rep Power
    0

    Default unknown column 'abc' in where clause in mysql

    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....

  2. #2
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,202
    Rep Power
    19

    Default Re: unknown column 'abc' in where clause in mysql

    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  3. #3
    bnson is offline Artemis
    Join Date
    Jul 2011
    Posts
    26
    Rep Power
    0

    Default Re: unknown column 'abc' in where clause in mysql

    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!

  4. #4
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,202
    Rep Power
    19

    Default Re: unknown column 'abc' in where clause in mysql

    Quote Originally Posted by bnson View Post
    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
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  5. #5
    bnson is offline Artemis
    Join Date
    Jul 2011
    Posts
    26
    Rep Power
    0

    Default Re: unknown column 'abc' in where clause in mysql

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

    Thank your sharing.

  6. #6
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,202
    Rep Power
    19

    Default Re: unknown column 'abc' in where clause in mysql

    Quote Originally Posted by bnson View Post
    I think this problem is bug
    It isn't.

    db
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  7. #7
    sandeshyadav is offline Member
    Join Date
    Feb 2012
    Posts
    13
    Rep Power
    0

    Default Re: unknown column 'abc' in where clause in mysql


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

  8. #8
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,202
    Rep Power
    19

    Default Re: unknown column 'abc' in where clause in mysql

    I'm happy you found the tutorial useful. Now to address this:
    Quote Originally Posted by bnson View Post
    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.
    Java Code:
    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
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  9. #9
    sandeshyadav is offline Member
    Join Date
    Feb 2012
    Posts
    13
    Rep Power
    0

    Default Re: unknown column 'abc' in where clause in mysql

    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 ....

  10. #10
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,202
    Rep Power
    19

    Default Re: unknown column 'abc' in where clause in mysql

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

    unknown column 'abc' in where clause in mysql-exploits_of_a_mom.png

    (From xkcd: Exploits of a Mom)

    db
    Fubarable and diamonddragon like this.
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  11. #11
    bnson is offline Artemis
    Join Date
    Jul 2011
    Posts
    26
    Rep Power
    0

    Default Re: unknown column 'abc' in where clause in mysql

    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.

  12. #12
    sandeshyadav is offline Member
    Join Date
    Feb 2012
    Posts
    13
    Rep Power
    0

    Default Re: unknown column 'abc' in where clause in mysql

    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);

  13. #13
    DarrylBurke's Avatar
    DarrylBurke is offline Member
    Join Date
    Sep 2008
    Location
    Madgaon, Goa, India
    Posts
    11,202
    Rep Power
    19

    Default Re: unknown column 'abc' in where clause in mysql

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

    db
    sandeshyadav likes this.
    If you're forever cleaning cobwebs, it's time to get rid of the spiders.

  14. #14
    sandeshyadav is offline Member
    Join Date
    Feb 2012
    Posts
    13
    Rep Power
    0

    Default Re: unknown column 'abc' in where clause in mysql

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

  15. #15
    Tolls is offline Moderator
    Join Date
    Apr 2009
    Posts
    11,949
    Rep Power
    19

    Default Re: unknown column 'abc' in where clause in mysql

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

  16. #16
    sandeshyadav is offline Member
    Join Date
    Feb 2012
    Posts
    13
    Rep Power
    0

    Default Re: unknown column 'abc' in where clause in mysql

    Thank you ....

Similar Threads

  1. Unknown column 'id' in 'field list'
    By peace76 in forum Java Servlet
    Replies: 6
    Last Post: 12-12-2013, 07:23 PM
  2. hibernate unknown column
    By arpho in forum New To Java
    Replies: 0
    Last Post: 10-15-2011, 11:27 AM
  3. Replies: 0
    Last Post: 04-02-2011, 10:22 AM
  4. Replies: 1
    Last Post: 07-06-2010, 04:50 PM
  5. Mysql LIKE clause problem
    By stalkerism in forum JDBC
    Replies: 1
    Last Post: 07-31-2009, 10:01 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •