Menu
▾
▴
#503 Writeback to Chado not working - unsanitized quotes?
We've got an Apollo->Chado annotation pipeline set up (Apollo is from svn checkout from April 24, 2012). User reported issue saving back anntoations when the synonym contains a ' (single quote). To reproduce, you can go here:
http://hymenopteragenome.org/beebase/sites/hymenopteragenome.org.beebase/files/data/Amel_45.apollo.jnlp
pull up any scaffold (I used Group14.14, from 159999 - 220000), pull down any gene into the annotation area, right click on annotation, select "Annotation Info Editor", click "add" under synonyms, add synonym 5'-nucleotidase, close window, save back to Chado. Apollo throws this exception below I think because it's writing bad SQL, in turn because it's not sanitizing the ' in 5'-nucleotidase.
DEBUG: INSERT ROW into feature_synonym (pub_id=1,feature_id=5160042,is_internal=false,is_current=true,synonym_id=1213941,feature_synonym_id=1927204)
DEBUG: INSERT INTO feature_synonym (pub_id, feature_id, is_internal, is_current, synonym_id, feature_synonym_id) VALUES (?, ?, ?, ?, ?, ?)
DEBUG: params: pub_id=1,feature_id=5160042,is_internal=false,is_current=true,synonym_id=1213941,feature_synonym_id=1927204
Adding synonym: 5'-nucleotidase
DEBUG: SELECT synonym_id FROM synonym WHERE name ='5'-nucleotidase' AND type_id=32786
ERROR: SQLException running SELECT synonym_id FROM synonym WHERE name ='5'-nucleotidase' AND type_id=32786
org.postgresql.util.PSQLException: ERROR: unterminated quoted string at or near "' AND type_id=32786"
at org.postgresql.util.PSQLException.parseServerError(PSQLException.java:139)
at org.postgresql.core.QueryExecutor.executeV3(QueryExecutor.java:152)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:100)
at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:43)
at org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:517)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:50)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:233)
at org.postgresql.jdbc1.AbstractJdbc1Statement.executeQuery(AbstractJdbc1Statement.java:221)
at apollo.dataadapter.chado.jdbc.JdbcChadoAdapter.executeLoggedSelectQuery(JdbcChadoAdapter.java:226)
at apollo.dataadapter.chado.jdbc.JdbcChadoAdapter.getSynonymId(JdbcChadoAdapter.java:4553)
at apollo.dataadapter.chado.jdbc.JdbcChadoAdapter.insertSynonymRowIfNeeded(JdbcChadoAdapter.java:4497)
at apollo.dataadapter.chado.jdbc.PureJDBCTransactionWriter.addSynonym(PureJDBCTransactionWriter.java:2284)
at apollo.dataadapter.chado.jdbc.PureJDBCTransactionWriter.addSynonyms(PureJDBCTransactionWriter.java:2260)
at apollo.dataadapter.chado.jdbc.PureJDBCTransactionWriter.addTranscript(PureJDBCTransactionWriter.java:916)
at apollo.dataadapter.chado.jdbc.JdbcChadoWriter.addTranscript(JdbcChadoWriter.java:746)
at apollo.dataadapter.chado.jdbc.PureJDBCTransactionWriter.addGene(PureJDBCTransactionWriter.java:803)
at apollo.dataadapter.chado.jdbc.JdbcChadoWriter.addAnnot(JdbcChadoWriter.java:264)
at apollo.dataadapter.chado.jdbc.JdbcChadoWriter.processFeatures(JdbcChadoWriter.java:180)
at apollo.dataadapter.chado.jdbc.JdbcChadoWriter.write(JdbcChadoWriter.java:109)
at apollo.dataadapter.chado.ChadoAdapter.commitChanges(ChadoAdapter.java:288)
at apollo.dataadapter.AbstractApolloAdapter.commitChanges(AbstractApolloAdapter.java:324)
at apollo.dataadapter.chado.ChadoAdapterGUI.doOperation(ChadoAdapterGUI.java:301)
at org.bdgp.swing.widget.DataAdapterChooser.doCommitWithExceptions(DataAdapterChooser.java:361)
at org.bdgp.swing.widget.DataAdapterChooser.doCommit(DataAdapterChooser.java:414)
at apollo.main.DataLoader$SaveAdapterChooser.doCommit(DataLoader.java:776)
at org.bdgp.swing.widget.DataAdapterChooser$CommitRunnable.run(DataAdapterChooser.java:136)
at java.lang.Thread.run(Unknown Source)
Exception in thread "Thread-16" java.lang.NullPointerException
at apollo.dataadapter.chado.jdbc.JdbcChadoAdapter.getSynonymId(JdbcChadoAdapter.java:4554)
at apollo.dataadapter.chado.jdbc.JdbcChadoAdapter.insertSynonymRowIfNeeded(JdbcChadoAdapter.java:4497)
at apollo.dataadapter.chado.jdbc.PureJDBCTransactionWriter.addSynonym(PureJDBCTransactionWriter.java:2284)
at apollo.dataadapter.chado.jdbc.PureJDBCTransactionWriter.addSynonyms(PureJDBCTransactionWriter.java:2260)
at apollo.dataadapter.chado.jdbc.PureJDBCTransactionWriter.addTranscript(PureJDBCTransactionWriter.java:916)
at apollo.dataadapter.chado.jdbc.JdbcChadoWriter.addTranscript(JdbcChadoWriter.java:746)
at apollo.dataadapter.chado.jdbc.PureJDBCTransactionWriter.addGene(PureJDBCTransactionWriter.java:803)
at apollo.dataadapter.chado.jdbc.JdbcChadoWriter.addAnnot(JdbcChadoWriter.java:264)
at apollo.dataadapter.chado.jdbc.JdbcChadoWriter.processFeatures(JdbcChadoWriter.java:180)
at apollo.dataadapter.chado.jdbc.JdbcChadoWriter.write(JdbcChadoWriter.java:109)
at apollo.dataadapter.chado.ChadoAdapter.commitChanges(ChadoAdapter.java:288)
at apollo.dataadapter.AbstractApolloAdapter.commitChanges(AbstractApolloAdapter.java:324)
at apollo.dataadapter.chado.ChadoAdapterGUI.doOperation(ChadoAdapterGUI.java:301)
at org.bdgp.swing.widget.DataAdapterChooser.doCommitWithExceptions(DataAdapterChooser.java:361)
at org.bdgp.swing.widget.DataAdapterChooser.doCommit(DataAdapterChooser.java:414)
at apollo.main.DataLoader$SaveAdapterChooser.doCommit(DataLoader.java:776)
at org.bdgp.swing.widget.DataAdapterChooser$CommitRunnable.run(DataAdapterChooser.java:136)
at java.lang.Thread.run(Unknown Source)
java console output