We're updating the issue view to help you get more done. 

DatabaseHandler crashes with single quote in Patient Name

Description

In italy we have names with single quote in patient names like " ' ":

d'onofrio, d'avellano,d'allonzo .....

The error I receive is:

Aug 11, 2010 10:50:06 AM in.raster.mayam.util.database.DatabaseHandler insertPatientData
SEVERE: null
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "onofrio" at line 1, column 59.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
..........
at java.lang.Thread.run(Thread.java:619)
Caused by: java.sql.SQLException: Syntax error: Encountered "onofrio" at line 1, column 59.
at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
... 24 more
Aug 11, 2010 10:50:06 AM in.raster.mayam.util.database.DatabaseHandler insertStudyData
SEVERE: null
java.sql.SQLIntegrityConstraintViolationException: INSERT on table 'STUDY' caused a violation of foreign key constraint 'SQL100616050025040' for key (CTPETPI1_CTPET-0074684728). The statement has been rolled back.
at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)

we need a method to escape the characters in a String to be suitable to pass to an SQL query.
If I do (from jdbc docs) :

static public String escapeLine(String s) {
String retvalue = s;
if (s.indexOf ("'") != -1 ) {
StringBuffer hold = new StringBuffer();
char c;
for(int i=0; i < s.length(); i++ ) {
if ((c=s.charAt) == '\'' ) {
hold.append ("''");
}else {
hold.append(c);
}
}
retvalue = hold.toString();
}
return retvalue;
}

conn.createStatement().execute("insert into " + patientTable + " values('" + escapeLine(dataset.getString(Tag.PatientID)) + "','" +escapeLine( dataset.getString(Tag.PatientName)) + "'," + dat + ",'" + dataset.getString(Tag.PatientSex) + "')");

it works !!

Do you know some other method in order to do so ?? (PreparedStatement ??)

Or I can Commit this?

Paolo

Environment

None

Status

Assignee

Kids & Science

Reporter

Paolo Marcheschi

Fix versions

Priority

Critical