java - Store HTML into MySQL database

Question

I'm trying to store a String which contains HTML in a MySQL database using Longtext data type. But it always says "You have an error in your SQL syntax". I tried to store a normal String and it works.

Update:

This is the query:

st.executeUpdate("insert into website(URL,phishing,source_code,active) values('" + URL + "','" + phishingState + "','" + sourceCode + "','" + webSiteState + "');");

I'm using Java.

Answer 1

Strings in a SQL query are -usually- surrounded by singlequotes. E.g.

INSERT INTO tbl (html) VALUES ('html');

But if the HTML string itself contains a singlequote as well, it would break the SQL query:

INSERT INTO tbl (html) VALUES ('<form onsubmit="validate('foo', 'bar')">');

You already see it in the syntax highlighter, the SQL value ends right before foo and the SQL interpreter can't understand what comes thereafter. SQL Syntax Error!

But that's not the only, it also puts the doors wide open for SQL injections (examples here).

You'll really need to sanitize the SQL during constructing the SQL query. How to do it depends on the programming language you're using to execute the SQL. If it is for example PHP, you'll need mysql_real_escape_string():

$sql = "INSERT INTO tbl (html) VALUES ('" . mysql_real_escape_string($html) . "')";

An alternative in PHP is using prepared statements, it will handle SQL escaping for you.

If you're using Java (JDBC), then you need PreparedStatement:

String sql = "INSERT INTO tbl (html) VALUES (?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, html);

Update: it turns out that you're actually using Java. You'll need to change the code as follows:

String sql = "INSERT INTO website (URL, phishing, source_code, active) VALUES (?, ?, ?, ?)";
preparedStatement = connection.prepareStatement(sql);
preparedStatement.setString(1, URL);
preparedStatement.setString(2, phishingState);
preparedStatement.setString(3, sourceCode);
preparedStatement.setString(4, webSiteState);
preparedStatement.executeUpdate();

Don't forget to handle JDBC resources properly. You may find this article useful to get some insights how to do basic JDBC stuff the proper way. Hope this helps.