Java - 转义字符串以防止 SQL 注入

Java - escape string to prevent SQL injection

提问by Scott Bonner

I'm trying to put some anti sql injection in place in java and am finding it very difficult to work with the the "replaceAll" string function. Ultimately I need a function that will convert any existing \to \\, any "to \", any 'to \', and any \nto \\nso that when the string is evaluated by MySQL SQL injections will be blocked.

我正在尝试在 Java 中放置一些反 sql 注入，并且发现使用“replaceAll”字符串函数非常困难。最终，我需要一个函数来将任何现有的转换\为\\、任何"到\"、任何'到\'和任何\n到，\\n以便当字符串被 MySQL SQL 注入评估时将被阻止。

I've Hymaned up some code I was working with and all the \\\\\\\\\\\in the function are making my eyes go nuts. If anyone happens to have an example of this I would greatly appreciate it.

我已经添加了一些我正在使用的代码\\\\\\\\\\\，函数中的所有内容都让我看傻了眼。如果有人碰巧有这样的例子，我将不胜感激。

采纳答案by Kaleb Brasee

PreparedStatements are the way to go, because they make SQL injection impossible. Here's a simple example taking the user's input as the parameters:

PreparedStatements 是可行的方法，因为它们使 SQL 注入成为不可能。下面是一个以用户输入为参数的简单示例：

public insertUser(String name, String email) {
   Connection conn = null;
   PreparedStatement stmt = null;
   try {
      conn = setupTheDatabaseConnectionSomehow();
      stmt = conn.prepareStatement("INSERT INTO person (name, email) values (?, ?)");
      stmt.setString(1, name);
      stmt.setString(2, email);
      stmt.executeUpdate();
   }
   finally {
      try {
         if (stmt != null) { stmt.close(); }
      }
      catch (Exception e) {
         // log this error
      }
      try {
         if (conn != null) { conn.close(); }
      }
      catch (Exception e) {
         // log this error
      }
   }
}

No matter what characters are in name and email, those characters will be placed directly in the database. They won't affect the INSERT statement in any way.

无论姓名和电子邮件中是什么字符，这些字符都将直接放入数据库中。它们不会以任何方式影响 INSERT 语句。

There are different set methods for different data types -- which one you use depends on what your database fields are. For example, if you have an INTEGER column in the database, you should use a setIntmethod. The PreparedStatement documentationlists all the different methods available for setting and getting data.

不同的数据类型有不同的设置方法——你使用哪一种取决于你的数据库字段是什么。例如，如果数据库中有一个 INTEGER 列，则应该使用一个setInt方法。 PreparedStatement 文档列出了可用于设置和获取数据的所有不同方法。

回答by Cylon Cat

The only way to prevent SQL injection is with parameterized SQL. It simply isn't possible to build a filter that's smarter than the people who hack SQL for a living.

防止 SQL 注入的唯一方法是使用参数化 SQL。根本不可能构建一个比以黑客 SQL 为生的人更聪明的过滤器。

So use parameters for all input, updates, and where clauses. Dynamic SQL is simply an open door for hackers, and that includes dynamic SQL in stored procedures. Parameterize, parameterize, parameterize.

因此，对所有输入、更新和 where 子句使用参数。动态 SQL 对黑客来说只是一扇敞开的门，它在存储过程中包含动态 SQL。参数化，参数化，参数化。

回答by coobird

Using a regular expression to remove text which could cause a SQL injection sounds like the SQL statement is being sent to the database via a Statementrather than a PreparedStatement.

使用正则表达式删除可能导致 SQL 注入的文本听起来就像 SQL 语句是通过 aStatement而不是PreparedStatement.

One of the easiest ways to prevent an SQL injection in the first place is to use a PreparedStatement, which accepts data to substitute into a SQL statement using placeholders, which does not rely on string concatenations to create an SQL statement to send to the database.

首先防止 SQL 注入的最简单方法之一是使用 a PreparedStatement，它接受数据以使用占位符替换为 SQL 语句，它不依赖字符串连接来创建 SQL 语句以发送到数据库。

For more information, Using Prepared Statementsfrom The Java Tutorialswould be a good place to start.

欲了解更多信息，使用预处理语句从Java教程将是一个良好的开端。

回答by Pascal Thivent

If really you can't use Defense Option 1: Prepared Statements (Parameterized Queries)or Defense Option 2: Stored Procedures, don't build your own tool, use the OWASP Enterprise Security API. From the OWASP ESAPIhosted on Google Code:

如果您真的不能使用防御选项 1：准备好的语句（参数化查询）或防御选项 2：存储过程，请不要构建自己的工具，请使用OWASP Enterprise Security API。来自托管在 Google Code 上的OWASP ESAPI：

Don't write your own security controls! Reinventing the wheel when it comes to developing security controls for every web application or web service leads to wasted time and massive security holes. The OWASP Enterprise Security API (ESAPI) Toolkits help software developers guard against security‐related design and implementation flaws.

不要编写自己的安全控制！在为每个 Web 应用程序或 Web 服务开发安全控制时，重新发明轮子会导致浪费时间和大量安全漏洞。OWASP 企业安全 API (ESAPI) 工具包帮助软件开发人员防范与安全相关的设计和实现缺陷。

For more details, see Preventing SQL Injection in Javaand SQL Injection Prevention Cheat Sheet.

有关更多详细信息，请参阅在 Java 中防止 SQL 注入和SQL 注入防止备忘单。

Pay a special attention to Defense Option 3: Escaping All User Supplied Inputthat introduces the OWASP ESAPIproject).

特别注意防御选项 3：转义引入OWASP ESAPI项目的所有用户提供的输入）。

回答by Bozho

In case you are dealing with a legacy system, or you have too many places to switch to PreparedStatements in too little time - i.e. if there is an obstacle to using the best practice suggested by other answers, you can try AntiSQLFilter

如果您正在处理遗留系统，或者您有太多地方可以PreparedStatement在太短的时间内切换到s - 即如果使用其他答案建议的最佳实践存在障碍，您可以尝试AntiSQLFilter

回答by Alan Moore

(This is in answer to the OP's comment under the original question; I agree completely that PreparedStatement is the tool for this job, not regexes.)

（这是对原始问题下 OP 评论的回答；我完全同意 PreparedStatement 是这项工作的工具，而不是正则表达式。）

When you say \n, do you mean the sequence \+nor an actual linefeed character? If it's \+n, the task is pretty straightforward:

当您说 时\n，您是指序列\+n还是实际的换行符？如果是\+ n，则任务非常简单：

s = s.replaceAll("['\"\\]", "\\
s = s.replaceAll("\n", "\\n");
");

To match one backslash in the input, you put four of them in the regex string. To put one backslash in the output, you put four of them in the replacement string. This is assuming you're creating the regexes and replacements in the form of Java String literals. If you create them any other way (e.g., by reading them from a file), you don't have to do all that double-escaping.

要匹配输入中的一个反斜杠，您将其中四个放在正则表达式字符串中。要在输出中放置一个反斜杠，请将其中四个放置在替换字符串中。这是假设您以 Java 字符串文字的形式创建正则表达式和替换。如果您以任何其他方式创建它们（例如，通过从文件中读取它们），您就不必进行所有这些双重转义。

If you have a linefeed character in the input and you want to replace it with an escape sequence, you can make a second pass over the input with this:

如果输入中有换行符，并且想用转义序列替换它，则可以使用以下命令对输入进行第二次传递：

s = s.replaceAll("\n", "\\\\n");

Or maybe you want two backslashes (I'm not too clear on that):

或者你可能想要两个反斜杠（我不太清楚）：

public class SQLInjectionEscaper {

    public static String escapeString(String x, boolean escapeDoubleQuotes) {
        StringBuilder sBuilder = new StringBuilder(x.length() * 11/10);

        int stringLength = x.length();

        for (int i = 0; i < stringLength; ++i) {
            char c = x.charAt(i);

            switch (c) {
            case 0: /* Must be escaped for 'mysql' */
                sBuilder.append('\');
                sBuilder.append('0');

                break;

            case '\n': /* Must be escaped for logs */
                sBuilder.append('\');
                sBuilder.append('n');

                break;

            case '\r':
                sBuilder.append('\');
                sBuilder.append('r');

                break;

            case '\':
                sBuilder.append('\');
                sBuilder.append('\');

                break;

            case '\'':
                sBuilder.append('\');
                sBuilder.append('\'');

                break;

            case '"': /* Better safe than sorry */
                if (escapeDoubleQuotes) {
                    sBuilder.append('\');
                }

                sBuilder.append('"');

                break;

            case '2': /* This gives problems on Win32 */
                sBuilder.append('\');
                sBuilder.append('Z');

                break;

            case '\u00a5':
            case '\u20a9':
                // escape characters interpreted as backslash by mysql
                // fall through

            default:
                sBuilder.append(c);
            }
        }

        return sBuilder.toString();
    }
}

回答by Alan Moore

PreparedStatements are the way to go in most, but not all cases. Sometimes you will find yourself in a situation where a query, or a part of it, has to be built and stored as a string for later use. Check out the SQL Injection Prevention Cheat Sheeton the OWASP Sitefor more details and APIs in different programming languages.

PreparedStatements 是大多数情况下的方法，但不是所有情况。有时您会发现自己处于一种情况，必须构建查询或查询的一部分并将其存储为字符串以备后用。查看OWASP 站点上的SQL 注入预防备忘单，了解更多详细信息和不同编程语言的 API。

回答by Richard

You need the following code below. At a glance, this may look like any old code that I made up. However, what I did was look at the source code for http://grepcode.com/file/repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.31/com/mysql/jdbc/PreparedStatement.java. Then after that, I carefully looked through the code of setString(int parameterIndex, String x) to find the characters which it escapes and customised this to my own class so that it can be used for the purposes that you need. After all, if this is the list of characters that Oracle escapes, then knowing this is really comforting security-wise. Maybe Oracle need a nudge to add a method similar to this one for the next major Java release.

您需要以下代码。乍一看，这可能看起来像我编写的任何旧代码。但是，我所做的是查看http://grepcode.com/file/repo1.maven.org/maven2/mysql/mysql-connector-java/5.1.31/com/mysql/jdbc/PreparedStatement的源代码。爪哇。然后，我仔细查看了 setString(int parameterIndex, String x) 的代码，找到了它转义的字符，并将其自定义为我自己的类，以便它可以用于您需要的目的。毕竟，如果这是 Oracle 转义的字符列表，那么知道这在安全方面确实令人欣慰。也许 Oracle 需要推动为下一个主要 Java 版本添加一种类似于此方法的方法。

<filter>
<filter-name>RequestWrappingFilter</filter-name>
<filter-class>com.huo.filter.RequestWrappingFilter</filter-class>
</filter>

<filter-mapping>
<filter-name>RequestWrappingFilter</filter-name>
<url-pattern>/*</url-pattern>
</filter-mapping>




package com.huo.filter;

import java.io.IOException;

import javax.servlet.Filter;
import javax.servlet.FilterChain;
import javax.servlet.FilterConfig;
import javax.servlet.ServletException;
import javax.servlet.ServletRequest;
import javax.servlet.ServletReponse;
import javax.servlet.http.HttpServletRequest;

public class RequestWrappingFilter implements Filter{

    public void doFilter(ServletRequest req, ServletReponse res, FilterChain chain) throws IOException, ServletException{
        chain.doFilter(new MyHttpRequestWrapper(req), res);
    }

    public void init(FilterConfig config) throws ServletException{
    }

    public void destroy() throws ServletException{
    }
}




package com.huo.filter;

import java.util.HashMap;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletRequestWrapper;

import org.apache.commons.lang.StringEscapeUtils;

public class MyHttpRequestWrapper extends HttpServletRequestWrapper{
    private Map<String, String[]> escapedParametersValuesMap = new HashMap<String, String[]>();

    public MyHttpRequestWrapper(HttpServletRequest req){
        super(req);
    }

    @Override
    public String getParameter(String name){
        String[] escapedParameterValues = escapedParametersValuesMap.get(name);
        String escapedParameterValue = null; 
        if(escapedParameterValues!=null){
            escapedParameterValue = escapedParameterValues[0];
        }else{
            String parameterValue = super.getParameter(name);

            // HTML transformation characters
            escapedParameterValue = org.springframework.web.util.HtmlUtils.htmlEscape(parameterValue);

            // SQL injection characters
            escapedParameterValue = StringEscapeUtils.escapeSql(escapedParameterValue);

            escapedParametersValuesMap.put(name, new String[]{escapedParameterValue});
        }//end-else

        return escapedParameterValue;
    }

    @Override
    public String[] getParameterValues(String name){
        String[] escapedParameterValues = escapedParametersValuesMap.get(name);
        if(escapedParameterValues==null){
            String[] parametersValues = super.getParameterValues(name);
            escapedParameterValue = new String[parametersValues.length];

            // 
            for(int i=0; i<parametersValues.length; i++){
                String parameterValue = parametersValues[i];
                String escapedParameterValue = parameterValue;

                // HTML transformation characters
                escapedParameterValue = org.springframework.web.util.HtmlUtils.htmlEscape(parameterValue);

                // SQL injection characters
                escapedParameterValue = StringEscapeUtils.escapeSql(escapedParameterValue);

                escapedParameterValues[i] = escapedParameterValue;
            }//end-for

            escapedParametersValuesMap.put(name, escapedParameterValues);
        }//end-else

        return escapedParameterValues;
    }
}

回答by ToBe_HH

Prepared Statements are the best solution, but if you really need to do it manually you could also use the StringEscapeUtilsclass from the Apache Commons-Lang library. It has an escapeSql(String)method, which you can use:

Prepared Statements 是最好的解决方案，但如果你真的需要手动完成，你也可以使用StringEscapeUtilsApache Commons-Lang 库中的类。它有一个escapeSql(String)方法，您可以使用它：

import org.apache.commons.lang.StringEscapeUtils; … String escapedSQL = StringEscapeUtils.escapeSql(unescapedSQL);

import org.apache.commons.lang.StringEscapeUtils; … String escapedSQL = StringEscapeUtils.escapeSql(unescapedSQL);

回答by shareef

After searching an testing alot of solution for prevent sqlmap from sql injection, in case of legacy system which cant apply prepared statments every where.

在搜索了大量测试解决方案以防止 sqlmap 被 sql 注入后，以防旧系统无法在任何地方应用准备好的语句。

java-security-cross-site-scripting-xss-and-sql-injection topicWAS THE SOLUTION

java-security-cross-site-scripting-xss-and-sql-injection 主题是解决方案

i tried @Richard s solution but did not work in my case. i used a filter

我尝试了@Richard 的解决方案，但在我的情况下不起作用。我用了过滤器

The goal of this filter is to wrapper the request into an own-coded wrapper MyHttpRequestWrapper which transforms:

the HTTP parameters with special characters (<, >, ‘, …) into HTML codes via the org.springframework.web.util.HtmlUtils.htmlEscape(…) method. Note: There is similar classe in Apache Commons : org.apache.commons.lang.StringEscapeUtils.escapeHtml(…) the SQL injection characters (‘, “, …) via the Apache Commons classe org.apache.commons.lang.StringEscapeUtils.escapeSql(…)

此过滤器的目标是将请求包装到一个自己编码的包装器 MyHttpRequestWrapper 中，它会转换：

通过 org.springframework.web.util.HtmlUtils.htmlEscape(...) 方法将带有特殊字符（<、>、'、...）的 HTTP 参数转换为 HTML 代码。注意：Apache Commons 中有类似的类： org.apache.commons.lang.StringEscapeUtils.escapeHtml(...) SQL 注入字符（'、“、...）通过 Apache Commons 类 org.apache.commons.lang.StringEscapeUtils。逃逸Sql(…)

##代码##