Search code examples
sqlsecurityjpasql-injection

Is it necessary to validate input for preventing SQL injection?

I am reading this article: https://cheatsheetseries.owasp.org/cheatsheets/SQL_Injection_Prevention_Cheat_Sheet.html I am using JPA with prepared statements (so thats the first point). There is also third point, which is talking about input validation with whitelist.

  • Do I need to take care of input validation when I am using prepared statements?
  • I dont understand that whitelist (point 3). Lets say, I would have an input, where you could write a name of the document. How am I going to validate this input? Could you give me some examples how to validate inputs for preventing SQL injection? So my JPQL query is:

SELECT d FROM Document d WHERE d.user.id=:id AND d.title=:title

Solution

  • As "Your Common Sense" (and hopefully also your common sense) says you will be protected from SQL injection in the example by using prepared statements (aka parameterized queries). When using prepared statements the parameters are never interpreted as SQL, they're simply processed by the database as data.

    But validation, when you can do it, is always good defensive coding. How is the data (the name of the document) going to be used after it is put in the database. Developers often treat data in the database as "trusted data" and don't properly leverage encoding or prepared statements, which can lead to a variety of issues such as second order SQL injection or stored XSS.

    White list validation of values is ideal, but that's not always possible. How do you validate a free form text such as the name of a document, as in your case? You may want to limit your name to certain characters (white list of characters) but that can be restricting and cause internationalization issues. At the very least:

    • You can require most fields to have a maximum length
    • You should usually verify that any string contains only valid characters for its encoding (e.g., no invalid UTF-8 sequences) - this can often be done more generally in a WAF or servlet filter
    • You may also want to restrict your input to printable characters

    Generally - you should always:

    • Validate on input as much as possible before putting something in the database (or passing it across any trust boundary)
    • Treat any data coming from another source (such as the database) as untrusted - and ensure you are using prepared statements, encoding or otherwise dealing with the data as untrusted