I see some possible issues with the SQL that is being generated by the
Every SQL is being generated on the fly by concatenating strings.
Can someone please explain why the SQL is being generated the way it is?
I cannot use this in real life for a couple of reasons:
This introduces a security hole because a user, accidentally or
deliberately, could potentially submit a string that alters the intent
of the query.
Given that the most expensive part of executing a query is often the
prepare phase, wouldn’t prepared statements make more sense, wherever
they are possible?
Currently, the framework is generating and running the string …
SELECT a.field from table WHERE (table."ID" = '--- :id ' )
Is something like this example mechanism feasible? (I would be prepared
to assist in the design and construction):
a. lookaside to see if we have already prepared a statement returning
these columns with this input parameter
b. if we have no prepared statement, generate similar parameterized SQL:
SELECT a.field from table WHERE (table."ID" = ? )
prepare the statement and keep the statement handle, with metadata so
we can apply parameters on demand
c. Apply parameters to the prepared statement and execute it
On 2nd and subsequent invocations, the prepare phase within
the DBMS (determining access paths) is sidestepped, the
bandwidth usage is reduced to only parameters, scalability is
improved enormously,and the security hole is plugged
so special characters cannot cause undesired behavior.