CODEWITHSUNDEEP
sqljspjdbc
Kelan
Kelan

Reputation: 11

SQL error Column count doesn't match value count at row 1

Am I missing an error with the following to insert into a table with four columns, message_id (auto-incremented) message_sender, message_reciever, message_body. I have checked similar questions and haven't found the solution. '''

        <% String sender_id = request.getParameter("message_sender");
        String reciever_id = request.getParameter("message_reciever");
        String message = request.getParameter("message_body");

        try{
        Class.forName("com.mysql.jdbc.Driver");
        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/fyp", "root", "Kmd116352323!");
        Statement st = con.createStatement();

        st.executeUpdate("INSERT INTO messages(message_sender,message_reciever,message_body) VALUES('"+sender_id+", "+reciever_id+" , "+message+" ')");

        out.println("Your request has been noted."
                + " Please return to your user profile or log out");

        } catch(Exception e){
        out.println(e);
                }
        %>

Upvotes: 0

Views: 74

Answers (2)

Mark Rotteveel
Mark Rotteveel

Reputation: 109275

You should not concatenate values into a query string. It makes your code vulnerable to SQL injection, or mistakes like forgetting quotes around values. The specific problem in your case is that you have a quote before the first value and a quote after the last value, which makes it a single value instead of three separate values.

However, instead of fixing the immediate problem by adding those missing quotes, you should switch to using a prepared statement:

try (PreparedStatement pstmt = connection.prepareStatement(
        "INSERT INTO messages(message_sender,message_reciever,message_body) VALUES(?, ?, ?)") {
    pstmt.setString(1, sender_id);
    pstmt.setString(2, reciever_id);
    pstmt.setString(3, message);

    pstmt.executeUpdate();
}

As an aside, you really should not put data access in a JSP. It belongs in a DAO or service class.

Upvotes: 1

Jonathan Laliberte
Jonathan Laliberte

Reputation: 2725

This is incorrect:

VALUES('"+sender_id+", "+reciever_id+" , "+message+" ')

You have a ' wrapping the all the values... Which means sql will think you are only sending 1 value. If they are all text values it should be like this:

VALUES('"+sender_id+"', '"+reciever_id+"' , '"+message+"')

If you know some of the values are of INT type then you do not need the ' for that value:

VALUES("+sender_id+", "+reciever_id+" , '"+message+"')

Text values need to be wrapped with a '

Upvotes: 0

Related Questions