Oracle DB: Detect missing use of prepared statements in SQLs
A quite common and recurring problem by developers is the use of literals in SQLs instead of prepared statements with bind variables. This problem pattern has been haunting me for years, there was a post about that in this blog also from 2017. It is now time to sum up the ideas that have emerged in the meantime to quickly identify the most problematic occurrences on this topic. The severity of this issue depends on the variety of the used literals. The more different literals are used in the SQL, the greater the problem becomes. If the used value does not come from outside (SQL injection), it could be tolerable if using a limited number of values for a filter condition directly in the SQL resulting in some 10 or 100 variations of a SQL statement. On the other hand, it could also be helpful in these cases to use the clear values in SQLs instead of a bind variables to support the optimizer in the use of histograms for different optimal execution plans. However, if thousands or mi...