I'm building a query as a string in my application layer to be executed over jdbc.
The string is constructed as follows:
"SELECT COUNT(DISTINCT ROOT_ID) as rows1 FROM DANIEL.UNIQUE_PHYSICIAN "
+ "WHERE "
+ "UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN' AND "
+ "UNIQUE_PHYSICIAN.FIRST_NAME = '"
+ firstP.getFirstName()
+ "' AND "
+ "UNIQUE_PHYSICIAN.LAST_NAME = '"
+ firstP.getLastName()
+ "' AND "
+ "UNIQUE_PHYSICIAN.POSTAL_CODE = '"
+ firstP.getPostalCode() + "'";
This works fine, until we hit a doctor in our database containing a single quote, such as "JEANNE-D'ARC" as her first name. It turns the query into the following:
SELECT COUNT(DISTINCT ROOT_ID) as rows1
FROM DANIEL.UNIQUE_PHYSICIAN
WHERE UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN'
AND UNIQUE_PHYSICIAN.FIRST_NAME = 'JEANNE-D'ARC'
AND UNIQUE_PHYSICIAN.LAST_NAME = 'ANON'
AND UNIQUE_PHYSICIAN.POSTAL_CODE = 'AAA AAA'
How can we resolve this?
EDIT: as I understand it, I should be using parameters as something. This query is going into a program and we can't necessarily control what doctor's names come in, so I guess this is a SQL injection vulnerability, right?