2

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

Arth
  • 331
  • 3
  • 14
  • You do not need to worry about SQL injection when you are only replacing parameter values in the query. You do if you construct the query and replace things like table or column names. – Gordon Linoff Sep 15 '19 at 14:54
  • thanks. Is it also safe even when user would want to login to application? So ONLY when I need to manipulate with tables/columns (replacing, creating etc..)? Do I understand that right? – Arth Sep 15 '19 at 15:08

2 Answers2

2

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
Egret
  • 739
  • 3
  • 8
0

You are using prepared statements for all SQL data literals (simply put, strings and numbers) to be put in your query from variables. No other validation is required. On the contrary, in the matter of using prepared statements, not a question of the data source (be it user input or anything else) should be ever considered. Just prepared statements, unconditionally, regardless of any circumstances.

In a rare case when you need to add any other SQL query part (such as an identifier or a keyword) from a variable, a whitelist-based filtering should be implemented. The approach simply stands for filtering the input variable against a list of values hardcoded in your program. Given the choice is always limited (as opposed to data variables), this is quite plausible. For example, in case the table order is based on the user input, the field name and the direction keyword (ASC or DESC) should be checked against the list of values pre-written in your code.

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345