2

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?

8
  • If Jeanne D'Arc is a doctor on your staff, then your patients have nothing to worry about! :-)
    – Vérace
    Jun 12, 2015 at 19:18
  • This is a Java question - have you tried putting various combinations of '' and/or ''' into your string? Is there a QuotedString(My_String) function (or similar) in Java? This happens a lot with Irish names (O'Brien &c...). Try here.
    – Vérace
    Jun 12, 2015 at 19:25
  • I don't think it's a java question. I need to store JEANNE-D'ARC in my database, which requires running that select statement in the SQL through the java, but the statement breaks with teh apostrophe. I could with no problem at all remove or change the apostrophe, but I want to keep it and have it be able to be inserted. Jun 12, 2015 at 19:32
  • You could try a shadow column - uppercase everything, remove spaces, accents and apostrophes - you have Name = "Jeanne D'Arc", SeachName = "JEANNEDARC" - that's what I do for Irish names. With computed columns now for many servers, no need to do any calculations at all.
    – Vérace
    Jun 12, 2015 at 19:35
  • 1
    Regardless of RDMS, it's always better to use prepared statements and bind variables in applications. There are many reasons for that, such as parsing time and server resources, data escaping, SQL injection , etc.
    – a1ex07
    Jun 12, 2015 at 19:44

1 Answer 1

2

You can escape single quotes by using 2 single quotes, that way your query would work. I'm not sure about the java syntax for a replace but your query should look something like this

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'

instead of this

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'

In any case, as has been suggested parametrized queries or stored procedures should be investigated (again, i'm not sure how to parametrize queries in java) .

You should also be very careful about SQL injection, what happens for example if someone enters a name "';drop table patients;--" ? Suppose the line breaks are not sent to SQL as I would expect from your example you get this:

SELECT COUNT(DISTINCT ROOT_ID) as rows1 
FROM DANIEL.UNIQUE_PHYSICIAN 
WHERE UNIQUE_PHYSICIAN.SOURCE_TABLE = 'STI.PHYSICIAN' 
AND UNIQUE_PHYSICIAN.FIRST_NAME = ''; drop table patients;--ARC'     AND UNIQUE_PHYSICIAN.LAST_NAME = 'ANON'     AND UNIQUE_PHYSICIAN.POSTAL_CODE = 'AAA AAA'

OWASP is a good resource on security and has a resource on Java SQL injection and prepared statements here

From that page:

Prepared Statements

Variables passed as arguments to prepared statements will automatically be escaped by the JDBC driver.

Example: ps.1

String selectStatement = "SELECT * FROM User WHERE userId = ? ";
PreparedStatement prepStmt = con.prepareStatement(selectStatement);
prepStmt.setString(1, userId);
ResultSet rs = prepStmt.executeQuery();
1
  • I tired with above but it doesn't escape the single quotes. String pt1 = "'Gov't, Public Services, & Community','Reeeeetail','Prol Services','Reeeeeal Estate','Restaurhhhhants'"; PreparedStatement stmt = conn.prepareStatement(query); stmt.setString(1, pt1); ResultSet rs = stmt.executeQuery();
    – selvi
    Jul 20, 2018 at 7:42

Not the answer you're looking for? Browse other questions tagged or ask your own question.