jdbc dymanic sql query with variable containg '

问题:

I have asked this beacause i was not able to find the answer.

what i am doing is

 String selectTableSQL = "SELECT * FROM diseaseinfo WHERE diseaseName =""+diseaseName +'" AND name = '"+username+"'";

it is perfecty running unless and until diseases does not contain 's type of word like

Wilms' tumor

Addison's disease

etc....

so query like

SELECT * FROM diseaseinfo WHERE diseaseName = 'Adult Still's disease' AND name = 'add';

wont execute because of ' 's on 'Adult Still's

and also in java i cant start string with String selectTableSQL = ' '; it will always be in String selectTableSQL = " ";

any solution?

回答1:

To avoid this case and any syntax error or SQL Injection you have to use PreparedStatement instead :

String selectTableSQL = "SELECT * FROM diseaseinfo WHERE col1 = ? and col2 = ?";
try (PreparedStatement ps = connection.prepareStatement(selectTableSQL)) {

    ps.setString(1, value_1);
    ps.setString(2, value_2);
    ResultSet rs = ps.executeQuery();
    while(rs.next()){
        //...
    }
}

回答2:

The correct way to use queries in JDBC is to use PreparedStatement and bind variables.

But in your case, try replacing the single quotes ' in your values with \'.

You can use a simple diseaseName.replace("'", "\\'"); to do it.