Escaping apostrophe from record to use in DB Query

Situation:

If any record contains an apostrophe and needs to be used in a Database Query. Then this apostrophe will make the SQL Query syntax invalid and you wouldn't be able to fetch data from Database. You may encounter below error in Adeptia if you are using DB Query or querying the DB through any DB tool. 

Error in execution : Exception thrown by extension function {public synchronized java.lang.Object com.adeptia.indigo.services.mapping.support.dbquery.MapperQueryExecutor.DBQuery(com.adeptia.indigo.services.mapping.MappingTransformation,java.lang.String,javax.security.auth.Subject,java.lang.String,java.lang.String,java.lang.String) throws com.adeptia.indigo.services.ServiceException}: com.adeptia.indigo.services.ServiceException: Error while executing query:select PERS_NBR, SSN from dbo.PERSON where LNAME = ''O''Hare'' and FNAME = ''TROY'' and DOB = TO_DATE( ''19770214'', ''yyyymmdd'')::ORA-00933: SQL command not properly ended

Due to this the DB Query used is not in proper format for Oracle Database.

select PERS_NBR, SSN from dbo.PERSON where LNAME = ''O''Hare'' and FNAME = ''TROY'' and DOB = TO_DATE( ''19770214'', ''yyyymmdd'')"  

encounters the error on Oracle Database.

Tip:-

To workaround this situation we need to use another apostrophe in O''Hare to escape the apostrophe. Doing this will make the query valid and we will be able to fetch the desired data.

select PERS_NBR, SSN from ELIG.PERSON where LNAME = 'O' 'Hare' and FNAME = 'TROY' and DOB = TO_DATE( '19770214', 'yyyymmdd').

For handling this situation in Data Mapper use substring-before, substring-after, and concat functions by the help of which you can insert an additional apostrophe in the string. There can be other rules as well for handling this situation.