How to sanitize log messages in Log4j to save them in database
java, jdbc, log4j
Solution
Have a look at this non official Log4J JDBCAppender which fixes this issue and is distributed under the Apache 2.0 license. Quoting its features in comparision to `org.apache.log4j.jdbc.JDBCAppender`:
- Log to (relational) database
- Flexible connection handling (does not yet support DataSource)
- Flexible sql commands to execute actual logging
- Prepared Statements and Stored Procedures (J2SDK 1.4+) supported
- Enables logging of messages with special characters such as ' (single quote) and , (comma)
- Flexible table and column structure
- Flexible id generation
- Multiple PatternLayout applications allowed; in one or more columns
- Supports J2SDK 1.3, 1.4 and 1.5
- Supports Log4j 1.2.9 and current development
Or, and you should seriously consider this option, switch from log4j to its successor, logback (this is where things happen) which has a `DBAppender` that uses `PreparedStatement` (see the sources), that can use a JNDI datasource, connection pooling (this is a big plus), etc. For more information about this appender, refer to the online manual http://logback.qos.ch/manual/appenders.html#DBAppender
Problem
I'm trying to save log messages to a central database. In order to do this, I configured the following Appender in log4j's xml configuration: ``` <appender name="DB" class="org.apache.log4j.jdbc.JDBCAppender"> <param name="URL" value="jdbc:postgresql://localhost/logging_test" /> <param name="user" value="test_user" /> <param name="password" value="test_password" /> <param name="sql" value="INSERT INTO log_messages ( log_level, message, log_date ) VALUES ( '%p', '%m', '%d{yyyy-MM-dd HH:mm:ss}' )" /> </appender> ``` This works fine, except some of the messages contain ', and then the appender fails. Is there an easy way to do this?