Uploaded image for project: 'Mayam'
  1. MAYAM-34

DatabaseHandler crashes with single quote in Patient Name

    Details

    • Type: Bug
    • Status: Resolved
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: None
    • Fix Version/s: 0.7
    • Labels:
      None
    • Sprint:

      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

        Attachments

          Activity

            People

            • Assignee:
              babuhussain babuhussain
              Reporter:
              marcheschi Paolo Marcheschi
            • Votes:
              0 Vote for this issue
              Watchers:
              0 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: