2

I'm logging my application's logs into database using JDBCAppender of log4j. At some point, I get "MySQLSyntaxErrorException" and I'm trying to find out the cause.

Here is my log4j.properties file;

# Define the DB appender
log4j.appender.DB=org.apache.log4j.jdbc.JDBCAppender
log4j.appender.DB.URL=jdbc:mysql://.../...
log4j.appender.DB.driver=com.mysql.jdbc.Driver
log4j.appender.DB.user=...
log4j.appender.DB.password=...
log4j.appender.DB.sql=INSERT INTO logs (EventDate,Level,Logger,Location,Message) VALUES( '%d{yyyy-MM-dd HH:mm:ss.SSS}', '%p' , '%c' ,'Test', '%m')
log4j.appender.DB.layout=org.apache.log4j.PatternLayout

And here is my table;

enter image description here

When I run my project I'm getting following error;

log4j:ERROR Failed to excute sql com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 ''.

If I change '%m' parameter with anything else, no exception occurs and log is inserted into DB successfully. So, I think the problem is with the message itself here but I cannot find out which message is causing it since it's a real huge project.

Is there any way that I can see the actual SQL statement? Or is there anything else that I can do to debug this more?

Cœur
  • 37,241
  • 25
  • 195
  • 267
josh
  • 409
  • 1
  • 5
  • 18
  • Can it be that you have an single qoute in your message? Which lead to a syntax error because it is not escaped? – Jens Nov 30 '18 at 08:56
  • @Jens This is what I'm thinking but I can't really find it anywhere. This is why I'm trying to find a way to see the insert statement. Or, is it possible to handle it(escape) inside my log4j.properties maybe? – josh Nov 30 '18 at 09:06
  • I think the right way is to set the column mapping https://logging.apache.org/log4j/log4j-2.0/manual/appenders.html and not a SQL query – Jens Nov 30 '18 at 09:12
  • or read here: https://stackoverflow.com/questions/2042070/how-to-sanitize-log-messages-in-log4j-to-save-them-in-database – Jens Nov 30 '18 at 09:14

1 Answers1

1

This could occur when the log message %m itself contains ' character. Did you try to replace '

INSERT INTO logs (EventDate,Level,Logger,Location,Message) VALUES( 'date_here', 'INFO' , 'com.test.Test' ,'loc', REPLACE('%m','\'','') );
Dushmantha
  • 2,911
  • 1
  • 14
  • 21