将SQL查询字符串插入到SQL表中

Question

我想将一个SQL查询字符串插入到我的数据库中，但是由于单引号''，我总是会得到一个错误。我不能只执行两倍的''''，因为这样我就无法执行存储在SQL数据库中的SQL查询。下面是一个示例：

"INSERT INTO selections(selection_name, selection_sql, selection_besitzer, selection_sichtbarkeit, selection_standardSelektion)"
            + "VALUES ('"
            + "TestName"+"', '"
            + "Select * From customer where customer_adressnummer like '%1%';"+"', '"
            + "Select all from customer where X"+"', '"
            + "private"+"', '"
            + "0"+"')");

我的问题是:如何在不更改字符串的情况下将此查询插入SQL数据库？

插入它之后，我想用我的程序读取它，然后根据数据库中的字符串执行查询。

以下是错误消息：

com.mysql.jdbc.MysqlDataTruncation: Data truncation: Truncated incorrect DOUBLE value: 'Select * From customer where customer_adressnummer like '
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3374)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3308)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1837)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1961)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2543)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1737)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2022)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1940)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1925)
    at toolhouseserver.ExecutionThread.run(ExecutionThread.java:114)
    at java.lang.Thread.run(Thread.java:745)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617)
    at java.lang.Thread.run(Thread.java:745)

Answer 1

毫不奇怪，当尝试使用动态SQL将SQL字符串插入到SQL数据库时，遇到SQL注入问题的概率相当高。省去悲伤，只需使用参数化查询，如下所示：

PreparedStatement ps = conn.prepareStatement(
        "INSERT INTO selections (selection_name, selection_sql, selection_besitzer, selection_sichtbarkeit, selection_standardSelektion) " +
        "VALUES (?,?,?,?,?)");
ps.setString(1, "TestName");
ps.setString(2, "Select * From customer where customer_adressnummer like '%1%';");
ps.setString(3, "Select all from customer where X");
ps.setString(4, "private");
ps.setString(5, "0");
ps.executeUpdate();