How to sanitize log messages in Log4j to save them in database

Question

Hello,

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?

Answer 1

I'm not familiar with log4j or JDBC, but I do know JDBC supports prepared statements. Perhaps there is a way to use that with the JDBCAppender

Answer 2

I'd suggest creating a custom appender and overriding the flushBuffer and execute methods where you can escape your strings or use PreparedStatement :

public class MyJDBCAppender extends JDBCAppender {

}

To explain why you need to override flushBuffer - the appender puts LogEvent objects into a buffer which is later flushed towards the target (database in this case). Here, the flushBuffer method uses getLogStatement and (via execute) a normal Statement. You can replace that behaviour completely. Have a look a the current source code

Then register your appender istead of JDBCAppender.

Answer 3

As per the Javadocs, the offical JDBCAppender is quite limited, and in particular has no good way of dealing with this issue.

One way around it is to use an alternative appender, such as this one which aims to be functionally compatible with the Log4J one except, you know, work.

Answer 4

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

Answer 5

Rafael, can you post the code you ended up using? I'm trying to use a DBAppender as well (with MySql) and just can't get the hang of it..

Thanks!

Answer 6

Joao, sorry for being late, but here it is:

<appender name="DB" class="org.apache.log4j.db.DBAppender">                                                                                                             
                <connectionSource class="org.apache.log4j.db.DriverManagerConnectionSource">                                                                                       
                        <param name="driverClass" value="org.postgresql.Driver" />                                                                                                 
                        <param name="url" value="jdbc:postgresql://localhost/database" />                                                                                      
                        <param name="user" value="user" />                                                                                                                          
                        <param name="password" value="password" />                                                                                                                 
                </connectionSource>                                                                                                                                                
                <layout class="org.apache.log4j.PatternLayout">                                                                                                                    
                        <param name="ConversionPattern" value="%d %-5p [%t] %c - %m%n" />                                                                                          
                </layout>                                                                                                                                                          
        </appender>

Hope it helps!