4 ms·
Parametrization is not enough, there are plenty of scenarios where you need to programmatically construct queries based on user input in more complex ways than
by tsimionescu 4y ago
Parametrization is not enough, there are plenty of scenarios where you need to programmatically construct queries based on user input in more complex ways than parameterized queries allow - including subqueries, lists (IN clauses), sort orders, and perhaps others.
- lupire 4y agoBut instead of inventing giant piles of workarounds, put your energy into fixing the database software.
- qw 4y ago> you need to programmatically construct queries based on user input You still need to build the queries and have control of what parametres are in use. Why not build the parameters list dynamically as well?. There are some databases or drivers that have limitations where you need to sanitize data yourself, but I'm not sure if that applies to the majority of the scenarios. This is a simple example of code for constructing dynamic queries: query = "... WHERE country = :country"; params.set("country", country); if (userId != null) { query += " AND user_id = :userId"; params.set("userId", userId); } It may become more complex as you add sub queries or if the database does not support adding a list as a parameter, but the principle is the same. You can always just build the list programatically using parameters instead of concatenating the values given by the user directly. If there are drivers or databases that does not support this, you will need to sanitize the data yourself of course. But I don't think that is the case for most users. (btw, database drivers that support named parameters are much more convenient than using "?")
- MattPalmer1086 4y agoI guess there may be SQL frameworks or ORMs that make that hard. I worked on something that allowed parameterisation of any user supplied value, no matter how complex the query. It wasn't that hard, but we could make it do whatever we wanted.