Search code examples
javajpaeclipselink

What are the risks when using StringBuilder and JPA native queries?

I need to create a native sql query and I plan to use StringBuilder for creating it.

StringBuilder sqlQuery = new StringBuilder();
    sqlQuery.append("SELECT ");
    sqlQuery.append("b.\"id\", ");
    sqlQuery.append("b.\"title\", ");
    sqlQuery.append("a.\"author_name\" as author ");
    sqlQuery.append("FROM ");
    sqlQuery.append(":dbName b ");
    sqlQuery.append("JOIN ");
    sqlQuery.append(":dbName2 a");
    sqlQuery.append(" ON a.\"id\" = b.\"author\"");

    ObjectQuery query = objectManager.createQuery(sqlQuery.toString());
    query.setParameter("dbName", "Book");
    query.setParameter("dbName2", "Author");

Is it safe to use the append of StringBuilder? Will this not result in SQL injection from an attacker? Will attacker be able to append a part of query that will drop the entire database? Any suggestions?

I know this query is simple and can be written in a single string but I have bigger queries with if statements and loops that append more lines depending on parameters

I know the named query is safer but in my case I don't know what the query will be like until the last moment.

Solution

  • If you set the parameter values with Query.setParameter() as in your sample code :

    query.setParameter("dbName", "Book");
query.setParameter("dbName2", "Author");

    you should not have SQL injection issues.

    SQL injection happens as you concatenate yourself the user data in the created query.
    But you are not in this case as the query created by the StringBuilder object is mastered totally by you and doesn't take any values from an external client.

    Is it safe to use the append of StringBuilder?

    While the StringBuilder variable (beware : StringBuilder is not thread-safe) is created and manipulated in a method scope, it is not more or less safe than another solution.

    It doesn't have any relationship with safety but as @HRgiger suggested, a good alternative would be using Criteria API.
    It makes sense as you explain that the construction of your queries is very dynamic :

    I have much bigger query with if statements that decides what to append

    It would produce a much more maintainable and readable code.