MySQL Statement error in JSP

Question

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.

possible duplicate of [Java - escape string to prevent SQL injection](http://stackoverflow.com/questions/1812891/java-escape-string-to-prevent-sql-injection) — CBroe, May 20 '14 at 07:38
Are there no prepared statements in JSP ? Obviously the quotations are wrong. — Daniel W., May 20 '14 at 07:39
Understood! Thanks all for the quick response :) Much appreciated! — fresh learner, May 20 '14 at 07:40
Read more about [Using Prepared Statements](http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html). Move your code in Servlet. **Don't** mix business and database logic with presentation logic. — Braj, May 20 '14 at 07:42
Newbie and i'm no a short deadline. I need to read and understand more. Java, JSP, servlets... all pretty confusing right now. But i have to save my job right now :( and understand as time goes on. Thanks! — fresh learner, May 20 '14 at 07:45

score 1 · Answer 1 · answered May 20 '14 at 07:40

1

change

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

answered May 20 '14 at 07:40

user3470953

11,025
2
17
18

I made that error while copying here. The quotings were fine annd it worked until the user typed something string that could spoild the sql statement. Thanks for the correction though. :) – fresh learner May 20 '14 at 07:42

score 1 · Answer 2 · answered May 20 '14 at 07:40

1

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

answered May 20 '14 at 07:40

Abhinab Kanrar

1,532
2
20
46

1

okay.try using prepared statement.post here if face any issue – Abhinab Kanrar May 20 '14 at 07:47
'ps.setInt(1, session.getAttribute("id"));' getting error: The method setInt(int, int) in the type PreparedStatement is not applicable for the arguments (int, Object) – fresh learner May 20 '14 at 07:59
1

type cast session.getAttribute("id") with int or use parseInt to convert. because session.getAttribute(String) will always return an object. So you need to explicitly manage it according to your need – Abhinab Kanrar May 20 '14 at 07:59

score 1 · Accepted Answer · answered May 20 '14 at 07:46

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.

MySQL Statement error in JSP

3 Answers3