相当于 PHP 的 mysql_real_escape_string() 的 Java

Java equivalent for PHP's mysql_real_escape_string()

提问by Kieran Tully

Is there a Java equivalent to PHP's mysql_real_escape_string() ?

是否有相当于 PHP 的 mysql_real_escape_string() 的 Java？

This is to escape SQL injection attempts before passing them to Statement.execute().

这是为了在将 SQL 注入尝试传递给 Statement.execute() 之前逃避 SQL 注入尝试。

I know I can use PreparedStatement instead, but let's assume these are one shot statements so preparing them will result in lower performance. I've already changed the code to use PreparedStatement but given the way the existing code was structured, an escape() function would make the code changes much simpler to review and maintain; I prefer easy to maintain code unless there is a compelling reason for the extra complexity. Also PreparedStatements are handled differently by the database, so this could expose us to bugs in the database that we haven't run into before, requiring more testing before releasing to production.

我知道我可以使用 PreparedStatement 代替，但让我们假设这些是一次性语句，因此准备它们会导致性能降低。我已经将代码更改为使用 PreparedStatement，但考虑到现有代码的结构方式，escape() 函数将使代码更改更易于和维护；我更喜欢易于维护的代码，除非有令人信服的理由造成额外的复杂性。此外，数据库对 PreparedStatements 的处理方式不同，因此这可能会使我们暴露在数据库中我们以前没有遇到过的错误中，需要在发布到生产环境之前进行更多测试。

Apache StringEscapeUtils escapeSQL()only escapes single quotes.

Apache StringEscapeUtils escapeSQL()只转义单引号。

Postscript: There are a lot of subtleties in the environment I inherited that I deliberately avoided in my question.

后记：我所继承的环境中有很多微妙之处，我在我的问题中特意回避了。

Two points to consider:

需要考虑的两点：

1) Prepared statements are not a panacea and do not provide 100% protection against SQL injection. Some database drivers instantiate parameterised queries using unsafe string concatenation, rather than pre-compiling the query to a binary form. Also, if your SQL relies on stored procedures, you need to ensure the stored procedures do not themselves build queries in unsafe ways.

1) 准备好的语句不是灵丹妙药，不能提供 100% 的 SQL 注入保护。一些数据库驱动程序使用不安全的字符串连接来实例化参数化查询，而不是将查询预编译为二进制形式。此外，如果您的 SQL 依赖于存储过程，则需要确保存储过程本身不会以不安全的方式构建查询。

2) Most prepared statement implementation bind the statement to the database connection the statement was instantiated on. If you are using database connection pooling, you need to be careful to
use the prepared statement reference only with the connection it was prepared on. Some pooling mechanisms do implement this transparently. Otherwise you could pool the prepared statements as well or (simplest but more overhead) create a new prepared statement for every query.

2) 大多数准备好的语句实现将语句绑定到语句被实例化的数据库连接。如果您正在使用数据库连接池，则需要小心地
将准备好的语句引用仅用于准备好它的连接。一些池化机制确实透明地实现了这一点。否则，您也可以将准备好的语句池化，或者（最简单但开销更大）为每个查询创建一个新的准备好的语句。

采纳答案by Daniel Schneller

As far as I know, there is no "standard" way to do it.

据我所知，没有“标准”的方法来做到这一点。

I strongly suggest using prepared statements despite your current concerns. The performance impact is going to be negligible - we have a similar situation with several thousand statements per second - most of them one-shots as well.

尽管您目前存在疑虑，但我强烈建议您使用准备好的语句。性能影响将可以忽略不计 - 我们有类似的情况，每秒有数千个语句 - 其中大多数也是一次性的。

The security you gain should be weighed much higher than a performance problem you have not even seen yet. In my opinion this is a clear situation of "Don't optimize prematurely".

您获得的安全性应该比您还没有见过的性能问题高得多。在我看来，这是“不要过早优化”的明显情况。

In any case should you really find out later that you run into performance problems, make sure that the prepared statements are really the cause by profiling carefully and then look for alternatives. Till then you should save yourself the hassle of trying to get the escaping right.

在任何情况下，如果您后来真的发现遇到了性能问题，请通过仔细分析确保准备好的语句确实是原因，然后寻找替代方案。在此之前，您应该避免尝试正确逃脱的麻烦。

This is even more important as I infer you are developing some sort of public facing site - internal apps seldom get enough traffic to be concerned about performance anyway.

这一点更重要，因为我推断您正在开发某种面向公众的网站 - 内部应用程序很少有足够的流量来关注性能。

回答by Ralph Ritoch

Here is some code which achieves what you are looking for. Originally on the Vnet Publishing wiki.

这是一些实现您正在寻找的代码。最初在 Vnet Publishing wiki 上。

https://web.archive.org/web/20131202082741/http://wiki.vnetpublishing.com/Java_Mysql_Real_Escape_String

https://web.archive.org/web/20131202082741/http://wiki.vnetpublishing.com/Java_Mysql_Real_Escape_String

/**
  * Mysql Utilities
  *        
  * @author Ralph Ritoch <[email protected]>
  * @copyright Ralph Ritoch 2011 ALL RIGHTS RESERVED
  * @link http://www.vnetpublishing.com
  *
  */

 package vnet.java.util;

 public class MySQLUtils {

     /**
      * Escape string to protected against SQL Injection
      *
      * You must add a single quote ' around the result of this function for data,
      * or a backtick ` around table and row identifiers. 
      * If this function returns null than the result should be changed
      * to "NULL" without any quote or backtick.
      *
      * @param link
      * @param str
      * @return
      * @throws Exception 
      */

     public static String mysql_real_escape_string(java.sql.Connection link, String str) 
           throws Exception
     {
         if (str == null) {
             return null;
         }

         if (str.replaceAll("[a-zA-Z0-9_!@#$%^&*()-=+~.;:,\Q[\E\Q]\E<>{}\/? ]","").length() < 1) {
             return str;
         }

         String clean_string = str;
         clean_string = clean_string.replaceAll("\\", "\\\\");
         clean_string = clean_string.replaceAll("\n","\\n");
         clean_string = clean_string.replaceAll("\r", "\\r");
         clean_string = clean_string.replaceAll("\t", "\\t");
         clean_string = clean_string.replaceAll("\00", "\\0");
         clean_string = clean_string.replaceAll("'", "\\'");
         clean_string = clean_string.replaceAll("\\"", "\\\"");

         if (clean_string.replaceAll("[a-zA-Z0-9_!@#$%^&*()-=+~.;:,\Q[\E\Q]\E<>{}\/?\\\"' ]"
           ,"").length() < 1) 
         {
             return clean_string;
         }

         java.sql.Statement stmt = link.createStatement();
         String qry = "SELECT QUOTE('"+clean_string+"')";

         stmt.executeQuery(qry);
         java.sql.ResultSet resultSet = stmt.getResultSet();
         resultSet.first();
         String r = resultSet.getString(1);
         return r.substring(1,r.length() - 1);       
     }

     /**
      * Escape data to protected against SQL Injection
      *
      * @param link
      * @param str
      * @return
      * @throws Exception 
      */

     public static String quote(java.sql.Connection link, String str)
           throws Exception
     {
         if (str == null) {
             return "NULL";
         }
         return "'"+mysql_real_escape_string(link,str)+"'";
     }

     /**
      * Escape identifier to protected against SQL Injection
      *
      * @param link
      * @param str
      * @return
      * @throws Exception 
      */

     public static String nameQuote(java.sql.Connection link, String str)
           throws Exception
     {
         if (str == null) {
             return "NULL";
         }
         return "`"+mysql_real_escape_string(link,str)+"`";
     }

 }

回答by skaffman

Do not assume that PreparedStatements are slower. Try it, measure it, and then judge.

不要假设 PreparedStatements 更慢。尝试，测量，然后判断。

PreparedStatements should alwaysbe used in preference to Statement, pretty much without exception, especiallywhen SQL injection attacks are what you're trying to avoid.

PreparedStatements 应始终优先于 Statement 使用，几乎无一例外，尤其是当您试图避免 SQL 注入攻击时。

回答by Tomalak

The only sensible way to avoid SQL injection is to use prepared/parameterized statements.

避免 SQL 注入的唯一明智方法是使用准备好的/参数化语句。

For example the PreparedStatementyou are trying to avoid for some reason. If you do one-shot statements, the time to prepare them should be negligible ("one-shot" and "performance-critical" is a contradiction, IMHO). If you do things in a loop, prepared statements even cause performance to increase.

例如，您出于某种原因试图避免使用PreparedStatement。如果你做一次性陈述，准备它们的时间应该可以忽略不计（“一次性”和“性能关键”是矛盾的，恕我直言）。如果你在循环中做事情，准备好的语句甚至会导致性能提高。

回答by Folee

org.apache.commons.lang.StringEscapeUtils.class in commons-lang.jar could solve your problem!

commons-lang.jar 中的 org.apache.commons.lang.StringEscapeUtils.class 可以解决您的问题！

回答by Tobore Igbe

According to Daniel Schneller, there is no standard way to handle PHP's mysql_real_escape_string() in Java What I did was to chain replaceAll method to handle every aspect that may be necessary to avoid any exception. Here is my sample code:

根据 Daniel Schneller 的说法，在 Java 中没有处理 PHP 的 mysql_real_escape_string() 的标准方法我所做的是链接 replaceAll 方法来处理可能需要避免任何异常的每个方面。这是我的示例代码：

public void saveExtractedText(String group,String content) { try { content = content.replaceAll("\\", "\\\\") .replaceAll("\n","\\n") .replaceAll("\r", "\\r") .replaceAll("\t", "\\t") .replaceAll("\00", "\\0") .replaceAll("'", "\\'") .replaceAll("\\"", "\\\"");

public void saveExtractedText(String group,String content) { try { content = content.replaceAll("\\", "\\\\") .replaceAll("\n","\\n") .replaceAll("\r", "\\r") .replaceAll("\t", "\\t") .replaceAll("\00", "\\0") .replaceAll("'", "\\'") .replaceAll("\\"", "\\\"");

        state.execute("insert into extractiontext(extractedtext,extractedgroup) values('"+content+"','"+group+"')");
    } catch (Exception e) {
        e.printStackTrace();

    }

        state.execute("insert into extractiontext(extractedtext,extractedgroup) values('"+content+"','"+group+"')");
    } catch (Exception e) {
        e.printStackTrace();

    }

回答by Béatrice Cassistat

I would not trust anything else than PreparedStatement to ensure security. But if you need to have a similar workflow when building queries you may use the code below. It uses a PreparedStatement underneath, works like a StringBuilder, adds escape functions and tracks the parameter indexes for you. It can be used like this:

除了 PreparedStatement 之外，我不会相信任何其他东西来确保安全。但是如果您在构建查询时需要类似的工作流程，您可以使用下面的代码。它在下面使用 PreparedStatement，像 StringBuilder 一样工作，添加转义函数并为您跟踪参数索引。它可以像这样使用：

SQLBuilder sqlBuilder = new SQLBuilder("update ").append(dbName).append(".COFFEES ");
sqlBuilder.append("set SALES = ").escapeString(sales);
sqlBuilder.append(", TOTAL = ").escapeInt(total);
sqlBuilder.append("where COF_NAME = ").escapeString(coffeeName);
sqlBuilder.prepareStatement(connection).executeUpdate();

Here's the code:

这是代码：

class SQLBuilder implements Appendable {
    private StringBuilder sqlBuilder;
    private List<Object> values = new ArrayList<>();

    public SQLBuilder() {
        sqlBuilder = new StringBuilder();
    }

    public SQLBuilder(String str)
    {
        sqlBuilder = new StringBuilder(str);
    }

    @Override
    public SQLBuilder append(CharSequence csq)
    {
        sqlBuilder.append(csq);
        return this;
    }

    @Override
    public SQLBuilder append(CharSequence csq, int start, int end)
    {
        sqlBuilder.append(csq, start, end);
        return this;
    }

    @Override
    public SQLBuilder append(char c)
    {
        sqlBuilder.append(c);
        return this;
    }

    // you can add other supported parameter types here...
    public SQLBuilder escapeString(String x)
    {
        protect(x);
        return this;
    }

    public SQLBuilder escapeInt(int x)
    {
        protect(x);
        return this;
    }

    private void escape(Object o)
    {
        sqlBuilder.append('?');
        values.add(o);
    }

    public PreparedStatement prepareStatement(Connection connection)
        throws SQLException
    {
        PreparedStatement preparedStatement =
            connection.prepareStatement(sqlBuilder.toString());
        for (int i = 0; i < values.size(); i++)
        {
            Object value = values.get(i);
            // you can add other supported parameter types here...
            if (value instanceof String)
                preparedStatement.setString(i + 1, (String) value);
            else if (value instanceof Integer)
                preparedStatement.setInt(i + 1, (Integer) value);
        }
        return preparedStatement;
    }

    @Override
    public String toString()
    {
        return "SQLBuilder: " + sqlBuilder.toString();
    }
}