记一个log4j写mysql的坑:单引号

记一个log4j写mysql的坑:单引号

作者个人博客：https://cherishpassion.cn/

描述

报错信息：

java.sql.SQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘t find template in cache for “index.ftl”(“zh_CN”, UTF-8, parsed); will try to lo’ at line 1

经排查后是携带信息中的单引号“ ’ ”被识别为了转义字符，导致异常。

---

可行的解决方案

在事务操作之前对数据进行调整，将“ ’ ”替换为“ ‘’ ”

• 首先，书写一个Adapter适配器，重写LoggingEvent中getThreadName()与getRenderedMessage()方法

  package com.*.log4jutil;
  
  import org.apache.log4j.Category;
  import org.apache.log4j.Priority;
  import org.apache.log4j.spi.LoggingEvent;
  
  /**
  * @Author ce
  * @Description adapter order to solve the problem that " ' " may cause sql error when log4j insert into mysql.
  */
  
  
  public class Log4jSqlAdapter extends LoggingEvent {
  
      /**
      * @Description 构造器
      * @param fqnOfCategoryClass
      * @param logger
      * @param level
      * @param message
      * @param throwable
      */
      public Log4jSqlAdapter(String fqnOfCategoryClass, Category logger, Priority level, Object message, Throwable throwable) {
          super(fqnOfCategoryClass, logger, level, message, throwable);
      }
  
  
      /**
      * @Description 重写getThreadName()
      * @return String threadName
      */
      @Override
      public String getThreadName() {
          String threadName = super.getThreadName();
  
          if(threadName.indexOf("'") != -1) {
              threadName = threadName.replaceAll("'", "''");
          }
  
          return threadName;
      }
  
      /**
      *@Description 重写getRenderedMessage()
      * @return String threadName
      */
      @Override
      public String getRenderedMessage() {
          String renderedMessage = super.getRenderedMessage();
  
          if(renderedMessage.indexOf("'") != -1){
              renderedMessage = renderedMessage.replaceAll("'", "''");
          }
  
          return renderedMessage;
      }
  }
  
  

  
  
• 随后,继承JDBCAppender，复写getLogStatement()方法，使Adapter生效

  package com.mineblog.utility.log4jutil;
  
  import org.apache.log4j.Category;
  import org.apache.log4j.LogManager;
  import org.apache.log4j.Priority;
  import org.apache.log4j.jdbc.JDBCAppender;
  import org.apache.log4j.spi.LoggingEvent;
  
  /**
  * @Author ce
  * @Date 2021/1/4
  * @Description appender of log4j for database
  */
  
  
  public class Log4jDatabaseAppender extends JDBCAppender {
  
      @Override
      protected String getLogStatement(LoggingEvent event) {
          String fqnOfCategoryClass = event.fqnOfCategoryClass;
  
          Category logger = LogManager.getRootLogger();
          Priority level = event.getLevel();
          Object message = event.getMessage();
  
          Log4jSqlAdapter log4jSqlAdapter = new Log4jSqlAdapter(fqnOfCategoryClass, logger, level, message, null);
  
          return super.getLogStatement(log4jSqlAdapter);
      }
  }
  
  

---

问题解决

作者其他主页：
   Github:：sketch747
   bilibili：想飞747的程序猿
   MicroBlog：想飞747的程序员