将HTML存储到MySQL数据库中 [英] Store HTML into MySQL database

问题描述

我试图使用 Longtext 数据类型在MySQL数据库中存储包含HTML的 String 。但它总是说你的SQL语法有错误。我尝试存储一个正常的字符串并且它可以正常工作。

更新 p>

这是查询：

  st.executeUpdate（insert into website （URL，phishing，source_code，active）values（'+ URL +'，'+ phishingState +'，'+ sourceCode +'，'+ webSiteState +'）;）; 
  

我使用的是Java。

解决方案

SQL查询中的字符串通常由单引号包围。例如：

  INSERT INTO tbl（html）VALUES（'html'）; 
  

但是，如果HTML字符串本身也包含一个单引号，它会破坏SQL查询：

  INSERT INTO tbl（html）VALUES（'< form onsubmit =validate（'foo'，'bar'）> ）; 
  

您已经在语法高亮显示中看到了它，SQL值紧接在 foo ，而SQL解释器无法理解其后的内容。 SQL语法错误！

但这不是唯一的，它也为 wide .wikipedia.org/wiki/SQL_injectionrel =noreferrer> SQL注入（示例这里）。

在构建SQL查询期间，您确实需要清理SQL 。如何做到这一点取决于你用来执行SQL的编程语言。如果它是例如PHP，则需要 mysql_real_escape_string（） ：

  $ sql =INSERT INTO tbl（html ）VALUES（'。mysql_real_escape_string（$ html）。'）; 
  

PHP中的另一种选择是使用准备好的语句，它将为您处理SQL转义。

如果你'使用Java（ JDBC ），那么你需要 PreparedStatement ：

  String sql =INSERT INTO tbl（html）VALUES（？）; 
 preparedStatement = connection.prepareStatement（sql）; 
 preparedStatement.setString（1，html）; 
  

更新：事实证明您实际上正在使用Java。您需要更改代码，如下所示：

 字符串sql =插入到网站（URL，网络钓鱼，源代码，活动）VALUES（？，？，？，？）; 
 preparedStatement = connection.prepareStatement（sql）; 
 preparedStatement.setString（1，URL）; 
 preparedStatement.setString（2，phishingState）; 
 preparedStatement.setString（3，sourceCode）; 
 preparedStatement.setString（4，webSiteState）; 
 preparedStatement.executeUpdate（）; 
  

不要忘记正确处理JDBC资源。您可能会发现这篇文章有用于获得一些见解以正确的方式来完成基本的JDBC任务。希望这有助于。

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.

解决方案

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.

这篇关于将HTML存储到MySQL数据库中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！