Cannot update in SQL with given field

Question

First of i want to apologise for the title, but when i explain you will understand that why i didn't know how to say this. Anyways, i am writing a chat programm and when i give the "UPDATE" command i follow it up with the name of the field that i want to update and then i give my new values. My problem is that it throughs me to an exception about wrong columns but i am sure after multiple checks that the name of the field i give is the same as the one in my SQL. I use MySQL with phpMyAdmin. Here is my code part:

Client:

if (mymessage.equals("UPDATE")) {
                String[] arr = input.nextLine().split(" ");
                message = id + "-=-" + arr[0].trim() + "-=-" + arr[1].trim();
                message2 = encrypt(message, serverAesKey);
                write.println(message2);
                write.flush();
            }

Server:

if (msg[2].trim().equals("UPDATE")) {
            System.out.println("waiting...");
            message = read.nextLine();
            command = Mediator.decrypt(message, Mediator.getServerAesKey());
            System.out.println(command);
            msg = command.split("-=-");
            int id = Integer.parseInt(msg[0].trim());
            String field = msg[1].trim();
            String update = msg[2].trim();
            System.out.println("....."+id+"-"+field+"-"+update);
            Mediator.updateDB(id, field, update);  >>this simply runs the method below



public void updateInfo(int id, String field, String update) {
    Connection connection;
    try {
        Class.forName("com.mysql.jdbc.Driver");
        connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/chat", "root", "");
        switch (field) {
            case "name":
                String SQLname = "UPDATE info SET name = " + update + " WHERE id = " + id + "";
                PreparedStatement pstmtName = connection.prepareStatement(SQLname);
                pstmtName.executeUpdate();
                break;
            case "password":
                String SQLpassword = "UPDATE info SET password = " + update + " WHERE id = " + id + "";
                PreparedStatement pstmtPwd = connection.prepareStatement(SQLpassword);
                pstmtPwd.executeUpdate();
                break;
        }
    } catch (ClassNotFoundException | SQLException e) {
        e.printStackTrace();
    }
}

And this is the exception i get:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'nick' in 'field list'
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
at java.lang.reflect.Constructor.newInstance(Constructor.java:525)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
at com.mysql.jdbc.Util.getInstance(Util.java:386)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1052)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3609)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3541)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2002)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2163)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2624)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2127)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2427)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2345)
at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2330)
at test.Database.updateInfo(Database.java:122)
at test.Mediator.updateDB(Mediator.java:100)
at test.ServerService.executeService(ServerService.java:108)
at test.ServerService.run(ServerService.java:73)
at java.lang.Thread.run(Thread.java:722)

This is my output, you can see that my message is properly split:

4-=-admin-=-UPDATE
waiting...
4-=-name-=-nick
.....4-name-nick

This is my database:

id|username|password|name|login
3 |user    |user    |user|0
4 |admin   |admin   |admin|0

(I would put a picture but i lack the reputation, still this is exactly how it's written.)

You can ignore anything about Mediator and encryption/decryption, they work just fine. What i want to know is why do i get and exception when i give the right fields.

I would imagin that UPDATE info SET name = " + update + " looks like UPDATE info SET name = nick to SQL, which doesn't make a lot of sense...Use PreparedStatement and bind the values to the columns instead, save yourself a lot of headaches in the long run... — MadProgrammer
– MadProgrammer, Commented Apr 15, 2014 at 0:19
that shouldn't be a problem since in another update i do in my program i say "UPDATE info SET login = 0" and it works perfectly — user3498565
– user3498565, Commented Apr 15, 2014 at 0:23
@ They are different data types man. A string literal in SQL gets quoted 'like this' — Mark W
– Mark W, Commented Apr 15, 2014 at 0:23
still i would like to know how i can bind them as you said. :) — user3498565
– user3498565, Commented Apr 15, 2014 at 0:23

MadProgrammer · Accepted Answer · 2014-04-15 00:30:26Z

Basically, column name is some sort of text data type (varchar etc...), so instead of...

String SQLname = "UPDATE info SET name = " + update + " WHERE id = " + id + "";
PreparedStatement pstmtName = connection.prepareStatement(SQLname);
pstmtName.executeUpdate();

The problem is, UPDATE info SET name = " + update + " WHERE id = " + id + " will look like UPDATE info SET name = nick WHERE id = 0 to the database...what does nick mean to the database? Not a lot I'd imagine.

You should be binding the values to the PreparedStatement

String SQLname = "UPDATE info SET name = ? WHERE id = ?";
PreparedStatement pstmtName = connection.prepareStatement(SQLname);
pstmtName.setString(1, update);
pstmtName.setInt(2, id);
pstmtName.executeUpdate();

This will save you a lot of headaches with data conversions (as the driver can take care of a lot of it) and possible SQL injections from malicious users

JHDev · Accepted Answer · 2014-04-15 00:35:50Z

0

To know exactly from where come the Exception I suggest that you put
System.out.println(SQLname) under :

String SQLname = "UPDATE info SET name = " + update + " WHERE id = " + id + "";

and System.out.println(SQLpassword) under :

String SQLpassword = "UPDATE info SET password = " + update + " WHERE id = " + id + "";

and put // :

//PreparedStatement pstmtName = connection.prepareStatement(SQLname);
//pstmtName.executeUpdate();

and :

//PreparedStatement pstmtPwd = connection.prepareStatement(SQLpassword);
//pstmtPwd.executeUpdate();

Execute your program. The SQL query (SQLpassword and SQLpassword) will be printed on console . Use MySQL command line to execute these query

answered Apr 15, 2014 at 0:35

JHDev

1,0032 gold badges28 silver badges42 bronze badges

Collectives™ on Stack Overflow

Cannot update in SQL with given field

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related