CODEWITHSUNDEEP
mysqljsp
fresh learner
fresh learner

Reputation: 467

MySQL Statement error in JSP

I have an issue with an sql statement and i dont know how to handle it. Here is the problem:

query = "INSERT INTO `mmr`(`userID`, `RunningProjects`, `MainOrders`) VALUES ("
                        + session.getAttribute("id")
                        + ",'"
                        + request.getParameter("RunningProjects")
                        + "','"
                        + request.getParameter("MainOrders")')";

The values are obtained from the post form which contains free text. The problem is, whenever a user enters characters like ', i will get an error because that tells the compiler that the value is over here(i suppose) and now look for the next value. I don't know how to include these characters and send them to database without having an error. Any help would be appreciated. Thank you.

Upvotes: 0

Views: 74

Answers (3)

Ravinder Reddy
Ravinder Reddy

Reputation: 23982

The character ' is used to surround literals in MySQL. And if any data contains such character as part of it, we have to escape it. This can be done using Prepared Statement in Java.

Change your query code accordingly.

query = "INSERT INTO `mmr`(`userID`, `RunningProjects`, `MainOrders`) 
         VALUES ( ?, ?,? )";

Now define a PreparedStatement instance and use it to bind values.

PreparedStatement pst = con.prepareStatement( query );
pst.setString( 1, session.getAttribute("id") );
pst.setString( 2, request.getParameter("RunningProjects") );
pst.setString( 3, request.getParameter("MainOrders") );

int result = pst.executeUpdate();

And, I suggest use of beans to handle business logic.

Upvotes: 1

Abhinab Kanrar
Abhinab Kanrar

Reputation: 1552

I think you are using normal statement in your JDBC code. Instead, I would suggest you to use Prepared statement. Prepared statement is generally used to eliminate this kind of problem and caching issue. If you will use prepared statement I think your problem will be solved

Upvotes: 1

user3470953
user3470953

Reputation: 11315

change 

query = "INSERT INTO `mmr`(`userID`, `RunningProjects`, `MainOrders`) VALUES ("
                        + session.getAttribute("id")
                        + ",'"
                        + request.getParameter("RunningProjects")
                        + "','"
                        + request.getParameter("MainOrders")
                        + "')";

Upvotes: 1

Related Questions