anton lazarets anton lazarets - 2 months ago 17
Java Question

insert html code into sql

i'm making website using jsp and servlets, and i inserted there articles editor. it works with textarea and put all code from editor into it. then i want to receive this code in my servlet and insert it into the database. received code looks like this:

<p><strong>Inner text</strong></p>
<p>&nbsp;</p>
<p><strong>list item:</strong></p>
<ol>
<li><strong>first item</strong></li>
<li><strong>second item</strong></li>
<li><strong>third one</strong></li>
</ol>
<p>remember to turn off bold text</p>
<h1 style="text-align: center;">H1 title</h1>
<p><em>some common italic text</em></p>
<p><span style="text-decoration: line-through;">strikethrough text =3</span></p>
<p><span style="text-decoration: line-through;">&nbsp;</span></p>


then from servlet i called method which will make insert request to db
and that method looks like this:

public void insertArticle(String title, String content) {
Connection con = null;
Statement stmt = null;

String request = "insert into database_name.articles (title, content) values (" + title + ", " + content + ")";

try {
con = DriverManager.getConnection(URL, LOGIN, PASSWORD);
stmt = con.createStatement();
stmt.executeUpdate(request);
} catch (SQLException e) {
e.printStackTrace();
} finally {
if (stmt != null) try {
stmt.close();
} catch (Exception e) {
e.printStackTrace();
}
if (con != null) try {
con.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}


but i have such exception:

com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' <p><strong>Inner text</strong></p>
<p>&nbsp;</p>
<p><strong>list item:</stron' at line 1
at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:39)
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
at com.mysql.jdbc.Util.getInstance(Util.java:387)
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:942)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3966)
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3902)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2526)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2673)
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545)
at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1540)
at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2595)
at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1468)
at com.lanzdev.classes.DB.insertArticle(DB.java:430)
at com.lanzdev.servlets.article.Editor.doPost(Editor.java:16)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:643)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:723)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:290)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:206)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:233)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:127)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:103)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:109)
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:293)
at
org.apache.coyote.http11.Http11AprProcessor.process(Http11AprProcessor.java:879)
at org.apache.coyote.http11.Http11AprProtocol$Http11ConnectionHandler.process(Http11AprProtocol.java:610)
at org.apache.tomcat.util.net.AprEndpoint$Worker.run(AprEndpoint.java:1777)


i tried to insert that html code manually using mysql workbench and it works, but from application it doesn't

Answer

You should be using a PrepareStatement instead of concatenating your String.

public void insertArticle(String title, String content) {
    Connection con  = null;
    PreparedStatement  stmt = null;

    String request = "insert into database_name.articles (title, content) values (?, ?)";

    try {
        con = DriverManager.getConnection(URL, LOGIN, PASSWORD);
        stmt = con.prepareStatement(request);
        stmt.setString(1, title);
        stmt.setString(2, content);
        stmt.executeUpdate();
    } catch (SQLException e) {
        e.printStackTrace();
    } finally {
        if (stmt != null) try {
            stmt.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
        if (con != null) try {
            con.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

http://docs.oracle.com/javase/tutorial/jdbc/basics/prepared.html

Comments