grootjans
commented
3 years ago Our Execute Query/Statement are merely wrapping around existing JDBC functionality, which limits the use of parameters to values in a query/statement. The use cases around the re-usability are for performance, as a database can re-use the form of the query, and it's associated query plan and sometimes some caches, and in security, as the driver and database can disallow parsing of the passed values, so it is not possible to do a form of SQL injection. The nature of the limitations in the usage of these parameters is most likely mired in a combination of the history of DB servers, the SQL standard and in a part the shared architecture of databases, but I doubt this should be seen as a bug in the driver(s).
Supporting this is not something that is on our roadmap. It is of course possible to build something with a formatted string, but we would advice being very careful with this, as if there is an avenue that a user can pass values that are taken along in this String you open yourself up to SQL injection.
When using the executeparameterizedquery activity it is not possible to have an order by statement in the template with dynamic parameters. So for instance Select * from table ORDER BY {1} {2}
where 1 is some string that denotes a column and 2 is either asc or desc
when toggling {2} between asc and desc the resultset will be the exact same although the given column should be sorted.
Apparantly this is some kind of bug in the jdbc drivers implementation? Here are 2 examples of people claiming that it is a bug in mysql jdbc driver and how to circumvent it
To circumvent it, you need to insert the order statement directly into the prepared statement rather then using variables/questionmarks.
It would be nice if there was a way to declare a string in a variable, so that the order by statement can be build inside a microflow dynamically instead of having to rely on multiple if statements and multiple executeParameterizedQuery activities.