java使用preparedstatement插入特殊字符失败

Question

我正在使用 HTML 表单，该表单

<input type=hidden name=longdesc value='SMARTNET%^" 8X5XNBD'>  

通过以下 javascript 代码完成发送：

function masinsert(id)
{
 var currentTime=new Date();


 var button = document.getElementById("m"+id);
 button.onclick="";
 button.value="Inserting";

 var itemdescription    = document.getElementById("itemdescription"+id).value;

 function handleHttpResponse()
  {
   if (http.readyState == 4) {
    button.value="Item Added";
   }
  }


 var http = getHTTPObject(); // We create the HTTP Object
 var tempUrl = "\AInsert";
 tempUrl +=  "itemdescription="+itemdescription+"&"+"itemshortdescription="+itemdescription.substring(0,37)+;
alert(tempUrl);
 http.open("GET", tempUrl, true);
 http.onreadystatechange = handleHttpResponse;
 http.send(null);   
}

到 java servlet。 AInsert.java

在 AInsert.java 文件中的

String itemdescription = request.getParameter("longdesc");

中，我执行了一个操作，然后将值发送到准备好的语句以运行插入查询。 在查询中，有时会出现一些特殊字符，导致查询失败。 例如，当我运行以下代码时

insert into itemdescription (longdesc) values ('SMARTNET%^" 8X5XNBD')

，这里是实际的代码片段：

PreparedStatement ps = conn.prepareStatement("INSERT INTO itemdescription (longdesc) values(?)");
ps.setString(1, itemdescription);
ps.executeUpdate();

它将产生一个错误，指出：无法将 NULL 值插入列“LongDesc”、表“App.dbo.itemdescription”； 列不允许为空。 插入失败

我已经尝试过 urlencode/urldecode

String encodedString = URLEncoder.encode(longdesc, "UTF-8");
String decitemdescription = URLDecoder.decode(itemdescription, "UTF-8"); 

并且我也尝试过这些功能

  //BEGIN URL Encoder 
    final static String[] hex = {
    "%00", "%01", "%02", "%03", "%04", "%05", "%06", "%07",
    "%08", "%09", "%0a", "%0b", "%0c", "%0d", "%0e", "%0f",
    "%10", "%11", "%12", "%13", "%14", "%15", "%16", "%17",
    "%18", "%19", "%1a", "%1b", "%1c", "%1d", "%1e", "%1f",
    "%20", "%21", "%22", "%23", "%24", "%25", "%26", "%27",
    "%28", "%29", "%2a", "%2b", "%2c", "%2d", "%2e", "%2f",
    "%30", "%31", "%32", "%33", "%34", "%35", "%36", "%37",
    "%38", "%39", "%3a", "%3b", "%3c", "%3d", "%3e", "%3f",
    "%40", "%41", "%42", "%43", "%44", "%45", "%46", "%47",
    "%48", "%49", "%4a", "%4b", "%4c", "%4d", "%4e", "%4f",
    "%50", "%51", "%52", "%53", "%54", "%55", "%56", "%57",
    "%58", "%59", "%5a", "%5b", "%5c", "%5d", "%5e", "%5f",
    "%60", "%61", "%62", "%63", "%64", "%65", "%66", "%67",
    "%68", "%69", "%6a", "%6b", "%6c", "%6d", "%6e", "%6f",
    "%70", "%71", "%72", "%73", "%74", "%75", "%76", "%77",
    "%78", "%79", "%7a", "%7b", "%7c", "%7d", "%7e", "%7f",
    "%80", "%81", "%82", "%83", "%84", "%85", "%86", "%87",
    "%88", "%89", "%8a", "%8b", "%8c", "%8d", "%8e", "%8f",
    "%90", "%91", "%92", "%93", "%94", "%95", "%96", "%97",
    "%98", "%99", "%9a", "%9b", "%9c", "%9d", "%9e", "%9f",
    "%a0", "%a1", "%a2", "%a3", "%a4", "%a5", "%a6", "%a7",
    "%a8", "%a9", "%aa", "%ab", "%ac", "%ad", "%ae", "%af",
    "%b0", "%b1", "%b2", "%b3", "%b4", "%b5", "%b6", "%b7",
    "%b8", "%b9", "%ba", "%bb", "%bc", "%bd", "%be", "%bf",
    "%c0", "%c1", "%c2", "%c3", "%c4", "%c5", "%c6", "%c7",
    "%c8", "%c9", "%ca", "%cb", "%cc", "%cd", "%ce", "%cf",
    "%d0", "%d1", "%d2", "%d3", "%d4", "%d5", "%d6", "%d7",
    "%d8", "%d9", "%da", "%db", "%dc", "%dd", "%de", "%df",
    "%e0", "%e1", "%e2", "%e3", "%e4", "%e5", "%e6", "%e7",
    "%e8", "%e9", "%ea", "%eb", "%ec", "%ed", "%ee", "%ef",
    "%f0", "%f1", "%f2", "%f3", "%f4", "%f5", "%f6", "%f7",
    "%f8", "%f9", "%fa", "%fb", "%fc", "%fd", "%fe", "%ff"
  };

  /**
   * Encode a string to the "x-www-form-urlencoded" form, enhanced
   * with the UTF-8-in-URL proposal. This is what happens:
   *
   * <ul>
   * <li><p>The ASCII characters 'a' through 'z', 'A' through 'Z',
   *        and '0' through '9' remain the same.
   *
   * <li><p>The unreserved characters - _ . ! ~ * ' ( ) remain the same.
   *
   * <li><p>The space character ' ' is converted into a plus sign '+'.
   *
   * <li><p>All other ASCII characters are converted into the
   *        3-character string "%xy", where xy is
   *        the two-digit hexadecimal representation of the character
   *        code
   *
   * <li><p>All non-ASCII characters are encoded in two steps: first
   *        to a sequence of 2 or 3 bytes, using the UTF-8 algorithm;
   *        secondly each of these bytes is encoded as "%xx".
   * </ul>
   *
   * @param s The string to be encoded
   * @return The encoded string
   */
  public static String encode(String s)
  {
    StringBuffer sbuf = new StringBuffer();
    int len = s.length();
    for (int i = 0; i < len; i++) {
      int ch = s.charAt(i);
      if ('A' <= ch && ch <= 'Z') {     // 'A'..'Z'
        sbuf.append((char)ch);
      } else if ('a' <= ch && ch <= 'z') {  // 'a'..'z'
           sbuf.append((char)ch);
      } else if ('0' <= ch && ch <= '9') {  // '0'..'9'
           sbuf.append((char)ch);
      } else if (ch == ' ') {           // space
           sbuf.append('+');
      } else if (ch == '-' || ch == '_'     // unreserved
          || ch == '.' || ch == '!'
          || ch == '~' || ch == '*'
          || ch == '\'' || ch == '('
          || ch == ')') {
        sbuf.append((char)ch);
      } else if (ch <= 0x007f) {        // other ASCII
           sbuf.append(hex[ch]);
      } else if (ch <= 0x07FF) {        // non-ASCII <= 0x7FF
           sbuf.append(hex[0xc0 | (ch >> 6)]);
           sbuf.append(hex[0x80 | (ch & 0x3F)]);
      } else {                  // 0x7FF < ch <= 0xFFFF
           sbuf.append(hex[0xe0 | (ch >> 12)]);
           sbuf.append(hex[0x80 | ((ch >> 6) & 0x3F)]);
           sbuf.append(hex[0x80 | (ch & 0x3F)]);
      }
    }
    return sbuf.toString();
  } //end encode

，

//decode url   
    private static String unescape(String s) {
    StringBuffer sbuf = new StringBuffer () ;
    int l  = s.length() ;
    int ch = -1 ;
    int b, sumb = 0;
    for (int i = 0, more = -1 ; i < l ; i++) {
      /* Get next byte b from URL segment s */
      switch (ch = s.charAt(i)) {
    case '%':
      ch = s.charAt (++i) ;
      int hb = (Character.isDigit ((char) ch) 
            ? ch - '0'
            : 10+Character.toLowerCase((char) ch) - 'a') & 0xF ;
      ch = s.charAt (++i) ;
      int lb = (Character.isDigit ((char) ch)
            ? ch - '0'
            : 10+Character.toLowerCase ((char) ch)-'a') & 0xF ;
      b = (hb << 4) | lb ;
      break ;
    case '+':
      b = ' ' ;
      break ;
    default:
      b = ch ;
      }
      /* Decode byte b as UTF-8, sumb collects incomplete chars */
      if ((b & 0xc0) == 0x80) {         // 10xxxxxx (continuation byte)
    sumb = (sumb << 6) | (b & 0x3f) ;   // Add 6 bits to sumb
    if (--more == 0) sbuf.append((char) sumb) ; // Add char to sbuf
      } else if ((b & 0x80) == 0x00) {      // 0xxxxxxx (yields 7 bits)
    sbuf.append((char) b) ;         // Store in sbuf
      } else if ((b & 0xe0) == 0xc0) {      // 110xxxxx (yields 5 bits)
    sumb = b & 0x1f;
    more = 1;               // Expect 1 more byte
      } else if ((b & 0xf0) == 0xe0) {      // 1110xxxx (yields 4 bits)
    sumb = b & 0x0f;
    more = 2;               // Expect 2 more bytes
      } else if ((b & 0xf8) == 0xf0) {      // 11110xxx (yields 3 bits)
    sumb = b & 0x07;
    more = 3;               // Expect 3 more bytes
      } else if ((b & 0xfc) == 0xf8) {      // 111110xx (yields 2 bits)
    sumb = b & 0x03;
    more = 4;               // Expect 4 more bytes
      } else /*if ((b & 0xfe) == 0xfc)*/ {  // 1111110x (yields 1 bit)
    sumb = b & 0x01;
    more = 5;               // Expect 5 more bytes
      }
      /* We don't test if the UTF-8 encoding is well-formed */
    }
    return sbuf.toString() ;
  }

但解码不会将其更改回原始特殊字符。

有任何想法吗？

提前致谢

更新： 我尝试添加这两个语句来获取请求

String itemdescription = URLDecoder.decode(request.getParameter("itemdescription"), "UTF-8");
        String itemshortdescription = URLDecoder.decode(request.getParameter("itemshortdescription"), "UTF-8");
        System.out.println("processRequest | short descrip ");

，但如果有帮助的话，这也会失败。

UPDATE2：我创建了一个 html 表单，并使用编码的项目描述进行了直接插入，例如

，插入可以正确地处理特殊字符和所有内容。 我猜我的 javascript 提交出了问题。 对此有什么想法吗？

Answer 1

如果您使用的是PreparedStatement，为什么不使用绑定变量呢？

插入 itemdescription (longdesc) 值 ('SMARTNET%^" 8X5XNBD')

应该是

PreparedStatement ps = conn.prepareStatement("INSERT INTO itemdescription (longdesc) values(?)");
ps.setString(1, itemdescription);
ps.executeUpdate();

Answer 2

看起来您在 javascript 中构建的 url 没有正确将 itemdescription 设置为请求参数。 itemdescription 应以“?”为前缀 字符，因为它是 url 查询字符串中的第一个参数。 这应该有效：

var tempUrl = "\AInsert";


tempUrl +=  "?itemdescription="+itemdescription+"&"+"itemshortdescription="+itemdescription.substring(0,37)+;