CODEWITHSUNDEEP
javastring
Ean V
Ean V

Reputation: 5439

Handling apostrophes in string

Suppose that I have a query, something like SELECT * FROM my_table where name = 'Debbie O'Brian'. As you may know in SQL Server we just need to put another ' to make it ignore single quotes. Here is what I like to do automatically.

Briefly, I need to replace all occurrences of ' with '' between two enclosing '. Here is what I have so far:

String text = "SELECT * FROM my_table where name = 'Debbie O'Brian'";
String[] splitArray = text.split(" '");
for (int i = 0; i < splitArray.length; i++) {
    String str = splitArray[i];
    if (str.endsWith("'"))
        str = str + " ";

    // replace all single quotes
    str = str.replace("'", "''");

    // revert last replacement
    str = str.replace("'' ", "' ");

    splitArray[i] = str;
}

StringBuilder builder = new StringBuilder();
for (int i = 0; i < splitArray.length; i++) {
    builder.append(splitArray[i]).append(" '");
}
System.out.println(builder.substring(0, builder.length() - 2).toString());

The problem is that I am relying on existence of a white space before starting '. My question is that how can I do this without this pre-assumption? Any help (even suggestion of an algorithm) is much appreciated. Please mention that there might be more than one single quoted string in query.

EDIT: I am using Hibernate to execute native SQL and getting the whole SQL query as input.

Upvotes: 1

Views: 1593

Answers (4)

Ean V
Ean V

Reputation: 5439

OK, as I mentioned above, my problem was not SQL injection. What I was after, at this point seems to be impossible unless I implement a SQL parser which is obviously too much for the requirement. So I am going to stick with my pre-assumption and give up trying to fix the wrong input as two people mentioned in comments.

Thank everybody for your answers.

Upvotes: 0

Sean F
Sean F

Reputation: 2390

As you are using Hibernate, similar to what @B.O.B said use parameterised queries:

    String queryString = "SELECT * FROM my_table where name = :name";
    Query query = getSession().createSQLQuery(queryString);
    query.setString("name", name);

Upvotes: 1

Alex
Alex

Reputation: 11579

If you use JDBC use

PreparedStatement ps = conn.prepareStatement("SELECT * FROM my_table where name = ?")
ps.setString(1, name);

Upvotes: 4

B.O.B.
B.O.B.

Reputation: 143

The best way to handle single quotes and other special characters in an SQL query is by using Parametrized Queries. It is also more secure. I would recommend doing a search on 'SQL Injection'. That will teach you how to protect against and handle the single quotes in your query.

Upvotes: 4

Related Questions