Recipient import -> SQL-Error

Use this forum to report bugs and to check for bugfixes and new releases of OpenEMM

Moderator: moderator

inteco
Posts: 11
Joined: Wed Apr 07, 2010 1:52 pm

Recipient import -> SQL-Error

Post by inteco »

Hello Folks, following Problem:

When I import recipients with a .csv i get an mysql-syntax-error. In the .csv there are singlequotes (') in the names of the recipients. It seems, that these quotes are causing the problem.

The strange thing is, that on the official demo-account of openemm, the import works...

The database was adopted from an older version of openemm

Here my Specs:
Openemm-Version is 6.0.1
Bugfixes 1-4 applied

The Test-CSV:
http://www.file-upload.net/download-241 ... t.csv.html

Output of openemm.log:

Code: Select all

2010-04-07 14:51:29,407: INFO  [resin-tcp-connection-*:8080-11] org.agnitas - NewImportWizard action: 3
2010-04-07 14:51:29,407: INFO  [resin-tcp-connection-*:8080-11] org.agnitas - NewImportWizard action: 3
2010-04-07 14:51:29,415: INFO  [resin-tcp-connection-*:8080-11] org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
2010-04-07 14:51:29,418: WARN  [resin-tcp-connection-*:8080-11] org.apache.commons.validator.ValidatorResources - Overriding default FormSet definition.
2010-04-07 14:51:29,418: INFO  [resin-tcp-connection-*:8080-11] org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
2010-04-07 14:51:29,422: INFO  [resin-tcp-connection-*:8080-11] org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
2010-04-07 14:51:29,425: INFO  [resin-tcp-connection-*:8080-11] org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
2010-04-07 14:51:29,429: INFO  [resin-tcp-connection-*:8080-11] org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
2010-04-07 14:51:29,454: INFO  [resin-tcp-connection-*:8080-11] org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
2010-04-07 14:51:29,454: INFO  [resin-tcp-connection-*:8080-11] org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
2010-04-07 14:51:29,455: INFO  [resin-tcp-connection-*:8080-11] org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
2010-04-07 14:51:29,455: INFO  [resin-tcp-connection-*:8080-11] org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
2010-04-07 14:51:29,523: ERROR [resin-tcp-connection-*:8080-11] org.agnitas - execute: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [UPDATE  customer_1_tbl SET mailtype=1, firstname='Gudrun', lastname='Rossi Dall'O', email='gudrun.rossi@hispeed.ch' WHERE email='gudrun.rossi@hispeed.ch']; nested exception is java.sql.BatchUpdateException: 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 'O', email='gudrun.rossi@hispeed.ch' WHERE email='gudrun.rossi@hispeed.ch'' at line 1
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:223)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:294)
org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:451)
org.agnitas.dao.impl.ImportRecipientsDaoImpl.updateExistRecipients(ImportRecipientsDaoImpl.java:789)
org.agnitas.service.impl.NewImportWizardServiceImpl.doValidate(NewImportWizardServiceImpl.java:296)
org.agnitas.service.impl.NewImportWizardServiceImpl.doParse(NewImportWizardServiceImpl.java:190)
org.agnitas.web.NewImportWizardAction.execute(NewImportWizardAction.java:174)
org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
javax.servlet.http.HttpServlet.service(HttpServlet.java:154)
javax.servlet.http.HttpServlet.service(HttpServlet.java:92)
com.caucho.server.dispatch.ServletFilterChain.doFilter(ServletFilterChain.java:106)
org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:174)

2010-04-07 14:51:29,523: ERROR [resin-tcp-connection-*:8080-11] org.agnitas - execute: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [UPDATE  customer_1_tbl SET mailtype=1, firstname='Gudrun', lastname='Rossi Dall'O', email='gudrun.rossi@hispeed.ch' WHERE email='gudrun.rossi@hispeed.ch']; nested exception is java.sql.BatchUpdateException: 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 'O', email='gudrun.rossi@hispeed.ch' WHERE email='gudrun.rossi@hispeed.ch'' at line 1
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:223)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:294)
org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:451)
org.agnitas.dao.impl.ImportRecipientsDaoImpl.updateExistRecipients(ImportRecipientsDaoImpl.java:789)
org.agnitas.service.impl.NewImportWizardServiceImpl.doValidate(NewImportWizardServiceImpl.java:296)
org.agnitas.service.impl.NewImportWizardServiceImpl.doParse(NewImportWizardServiceImpl.java:190)
org.agnitas.web.NewImportWizardAction.execute(NewImportWizardAction.java:174)
org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
javax.servlet.http.HttpServlet.service(HttpServlet.java:154)
javax.servlet.http.HttpServlet.service(HttpServlet.java:92)
com.caucho.server.dispatch.ServletFilterChain.doFilter(ServletFilterChain.java:106)
org.springframework.orm.hibernate3.support.OpenSessionInViewFilter.doFilterInternal(OpenSessionInViewFilter.java:174)

2010-04-07 14:51:55,995: INFO  [resin-tcp-connection-*:8080-3] org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
2010-04-07 14:51:55,997: INFO  [resin-tcp-connection-*:8080-3] org.agnitas - Onepixel: cust: 12153 mi: 137 ci: 1
2010-04-07 14:51:55,997: INFO  [resin-tcp-connection-*:8080-3] org.agnitas - Onepixel: cust: 12153 mi: 137 ci: 1
2010-04-07 14:51:56,073: INFO  [resin-tcp-connection-*:8080-15] org.agnitas - Onepixel: cust: 12153 mi: 137 ci: 1
2010-04-07 14:51:56,073: INFO  [resin-tcp-connection-*:8080-15] org.agnitas - Onepixel: cust: 12153 mi: 137 ci: 1
2010-04-07 14:53:43,906: INFO  [resin-tcp-connection-*:8080-13] org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
2010-04-07 14:53:43,907: INFO  [resin-tcp-connection-*:8080-13] org.agnitas - Onepixel: cust: 12133 mi: 137 ci: 1
2010-04-07 14:53:43,907: INFO  [resin-tcp-connection-*:8080-13] org.agnitas - Onepixel: cust: 12133 mi: 137 ci: 1
2010-04-07 14:56:49,703: INFO  [resin-tcp-connection-*:8080-21] org.agnitas - Onepixel: cust: 13334 mi: 137 ci: 1
2010-04-07 14:56:49,703: INFO  [resin-tcp-connection-*:8080-21] org.agnitas - Onepixel: cust: 13334 mi: 137 ci: 1
2010-04-07 14:57:24,256: INFO  [resin-tcp-connection-*:8080-17] org.hibernate.dialect.Dialect - Using dialect: org.hibernate.dialect.MySQLDialect
2010-04-07 14:57:24,258: INFO  [resin-tcp-connection-*:8080-17] org.agnitas - Onepixel: cust: 12407 mi: 137 ci: 1
2010-04-07 14:57:24,258: INFO  [resin-tcp-connection-*:8080-17] org.agnitas - Onepixel: cust: 12407 mi: 137 ci: 1
Last edited by inteco on Wed Apr 14, 2010 3:40 pm, edited 1 time in total.
pheelix
Posts: 101
Joined: Thu Nov 20, 2008 4:34 pm
Location: Dresden, Germany
Contact:

Post by pheelix »

2010-04-07 14:51:29,523: ERROR [resin-tcp-connection-*:8080-11] org.agnitas - execute: org.springframework.jdbc.BadSqlGrammarException: StatementCallback; bad SQL grammar [UPDATE customer_1_tbl SET mailtype=1, firstname='Gudrun', lastname='Rossi Dall'O', email='gudrun.rossi@hispeed.ch' WHERE email='gudrun.rossi@hispeed.ch'];
obvisious error ;)

check the manual that corresponds to your MySQL server version for the right syntax to use near 'O',

it is caused by the
lastname='Rossi Dall'O',
because of the apostrophe in front of the O which caused the end of the string
inteco
Posts: 11
Joined: Wed Apr 07, 2010 1:52 pm

Post by inteco »

Yes I know that the single quote causes the error, but like ive written above:

"The strange thing is, that on the official demo-account of openemm, the import works..."
maschoff
Site Admin
Posts: 2597
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Post by maschoff »

We will address this issue with a bug fix. Please follow our Twitter account to get notified as soon as this bug fix is available.
OpenEMM Maintainer
inteco
Posts: 11
Joined: Wed Apr 07, 2010 1:52 pm

Post by inteco »

Ok I figured out that the same error exists on the demo account.

The first Time the import works, but the second time the same error occours again. (Probably when the UPDATE-SQL-Query is build, then therse no escaping. With the INSERT-Query it works...)

The Problem doesnt occurs when in the recipients name no ' is present.

I've looked 4 the Ticket of this Bug and havent found it on the Bugtracker.

I'll check out the source code, it seems that its a small error and perhaps I can fix it...
(will report that of course)
maschoff
Site Admin
Posts: 2597
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Post by maschoff »

Fix:

Class: ImportRecipientsDaoImpl

replace line 741 with: query = query + column.getColName() + "='" + value.replace("'","\\'").replace("`","\\'") + "', ";
OpenEMM Maintainer
inteco
Posts: 11
Joined: Wed Apr 07, 2010 1:52 pm

Post by inteco »

Ohh... thats sad :cry:
I just figured it out myself and then I see you already posted the solution here... :wink:

Anyway, I made it with the org.apache.commons.lang.StringEscapeUtils and this also works as expected...

Thx 4 the fast answer!
effgee
Posts: 10
Joined: Wed Apr 28, 2010 10:17 pm

Post by effgee »

Is there a bugfix on the way? I am having the same exact issue and I don't see any instructions on how to compile from source to fix it.

Apparently I need to edit the source file and not the compile class file?
ma wrote:Fix:

Class: ImportRecipientsDaoImpl

replace line 741 with: query = query + column.getColName() + "='" + value.replace("'","\\'").replace("`","\\'") + "', ";
effgee
Posts: 10
Joined: Wed Apr 28, 2010 10:17 pm

Post by effgee »

Actually I'm getting a different error during import.

Code: Select all

2010-05-13 16:04:10,846: ERROR [resin-tcp-connection-*:8080-21] org.agnitas - execute: org.springframework.jdbc.BadSqlGrammarException: PreparedStatementCallback; bad SQL grammar [INSERT INTO customer_1_tbl (mailtype, datasource_id, gender, email, creation_date) VALUES (?, ?, ?, ?, current_timestamp)]; nested exception is java.sql.SQLException: No value specified for parameter 4
org.springframework.jdbc.support.SQLStateSQLExceptionTranslator.translate(SQLStateSQLExceptionTranslator.java:88)
org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:257)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:493)
org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:505)
org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:769)
org.agnitas.dao.impl.ImportRecipientsDaoImpl.addNewRecipients(ImportRecipientsDaoImpl.java:580)
org.agnitas.service.impl.NewImportWizardServiceImpl.doValidate(NewImportWizardServiceImpl.java:294)
org.agnitas.service.impl.NewImportWizardServiceImpl.doParse(NewImportWizardServiceImpl.java:190)
org.agnitas.web.NewImportWizardAction.execute(NewImportWizardAction.java:190)
org.apache.struts.chain.commands.servlet.ExecuteAction.execute(ExecuteAction.java:58)
org.apache.struts.chain.commands.AbstractExecuteAction.execute(AbstractExecuteAction.java:67)
org.apache.struts.chain.commands.ActionCommandBase.execute(ActionCommandBase.java:51)
org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
org.apache.commons.chain.generic.LookupCommand.execute(LookupCommand.java:304)
org.apache.commons.chain.impl.ChainBase.execute(ChainBase.java:190)
org.apache.struts.chain.ComposableRequestProcessor.process(ComposableRequestProcessor.java:283)
org.apache.struts.action.ActionServlet.process(ActionServlet.java:1913)
org.apache.struts.action.ActionServlet.doPost(ActionServlet.java:462)
javax.servlet.http.HttpServlet.service(HttpServlet.java:154)
javax.servlet.http.HttpServlet.service(HttpServlet.java:92)

effgee wrote:Is there a bugfix on the way? I am having the same exact issue and I don't see any instructions on how to compile from source to fix it.

Apparently I need to edit the source file and not the compile class file?
ma wrote:Fix:

Class: ImportRecipientsDaoImpl

replace line 741 with: query = query + column.getColName() + "='" + value.replace("'","\\'").replace("`","\\'") + "', ";
effgee
Posts: 10
Joined: Wed Apr 28, 2010 10:17 pm

Post by effgee »

Nevermind. I'm retarded.

I applied bugfixes 1-6 and for some reason, I didn't rename the files outlined in

Code: Select all

Fix #05: Replace files ImportRecipientsDaoImpl.class and five inner classes
         ImportRecipientDaoImpl$1.class to *$5.class in directory
         /home/openemm/webapps/core/WEB-INF/classes/org/agnitas/dao/impl
         Please note: You have to replace the underscore '_' with '$' in each
         inner class file name because SourceForge does not accept character
         '$' in file names.
As well as the overwritten files didn't have the appropriate permissions.
inteco
Posts: 11
Joined: Wed Apr 07, 2010 1:52 pm

Post by inteco »

I updated again some new Recipients, with quite "ugly" data (strange characters in it, e.g. backslashes). I found out that the same error in 6.1 is present. It seems that the developers actually changed some lines in the code, but still there occurring errors.

Here is what i found out and what i changed:

This on Line 803 in the File ImportRecipientsDaoImpl.java causes the Problems (also present on Line 636):
value.replace("`","'").replace("'","\'")

I made following out of it:
value.replace("\'","\'\'").replace("\\","\\\\")

First I worked with the apache StringEscapeUtils -> those did not escape the backslashes at all. So I made it with the String replace method.

I tested with phpmyadmin how the three special-characters ( ` \ ' ) would be escaped and changed it accordingly. It seems that ` isnt escaped at all, so i removed it.

Hope thats right in the eyes of the developers. At least it workes 4 me...

Probably there are some more character which can cause problems, i will post them, when i run into them.
maschoff
Site Admin
Posts: 2597
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Post by maschoff »

Thanks for your input - sounds reasonable! If it works for us too we will change the code in the mainline accordingly.
OpenEMM Maintainer
maschoff
Site Admin
Posts: 2597
Joined: Thu Aug 03, 2006 10:20 am
Location: Munich, Germany
Contact:

Post by maschoff »

Actually, this is what we recommend and will fix in the next release:
1. Leave line 636 as it is
2. Replace line 803 with

Code: Select all

query = query + column.getColName() + "='" + value.replace("\\","\\\\").replace("'", "\\'") + "', ";
to include the backslash. it is not necessary to escape the tick character (`).
OpenEMM Maintainer
Post Reply