2

I use Intellij but I do not know the reason why I always get the following error:

"status" is provided externally to the method and not sanitized before use.

My method:

...
    public List getActionIdByTypeAndCodeAndStatus(String type, String code, String status) throws Exception {
                String sql = "select action_id from action where type = '" + type + "' and code = '" + code + "' and status = '" + status + "' ";
                Query checkWriteLog = entityManager.createNativeQuery(sql);
                return checkWriteLog.getResultList();
            }

The line which throws the error is

 Query checkWriteLog = entityManager.createNativeQuery(sql);

Question: Do you know the reason? How can I fix it?

Ole V.V.
  • 81,772
  • 15
  • 137
  • 161
user3205761
  • 291
  • 1
  • 4
  • 18

2 Answers2

5

The problem

You're concatenating string to form your sql query. This is prone to SQL injection attacks.

given

 String sql = "select action_id from action where type = '" + type + "' and code = '" + code + "' and status = '" + status + "' "

We can pass in the following string for status to wreck your db:

'; DROP TABLE action; --

Why? The '; will finish your query and run it, we then provide another query (; closed the first one) which is "DROP TABLE action;" and finally we add two dashes to ignore everything that follows

This results in a drop table of the table action and could be disastrous. Read more about this on the wiki page.

The solution

Use prepared statements like so:

Query query = JPA.em().createNativeQuery("select action_id from action where type = ':type' and code = ':code' and status = :status ");
query.setParameter("type", type);
query.setParameter("code", code);
query.setParameter("status", status);

This, in an easy to understand way, will basically send the query to the database and tell it "run that, but I'll give you values to add in later" and will then send the values to it. This means whatever you send will be placed between the "" and will NOT be treated as a query. **

** This is not what actually happens, it's a way of understanding of how it works. Read the wiki page if you need an actual explanation.

Rick van Lieshout
  • 2,276
  • 2
  • 22
  • 39
3

You are creating your SQL query by concatenting strings provided by the caller. You should escape and validate the strings before running the query to avoid SQL injection attacks.

See this question on how to sanitize your inputs.

Community
  • 1
  • 1
sdgfsdh
  • 33,689
  • 26
  • 132
  • 245