be*************@gmail.com wrote:
Presumably I should build some server-side checking into the process,
to ensure whatever XML/SQL arrives is authorized to execute. I've not
really thought about how to do that yet. I guess it means keeping track
of the 'state' of the client. Do you have any comments / suggestions on
this?
I am pretty conservative when it comes to constructing dynamic SQL on
the fly. I do not want to let SQL execute unless I'm the one who
designed the query. Who knows if the user will cause a horrendous query
to crash my server. It's very easy to do:
"SELECT * FROM tablename, tablename, tablename, tablename, tablename
ORDER BY 1"
Even if the table specified contains as few as 100 rows, the above query
will probably kill your server, as it tries to sort the result set of
10^10 rows!
So I think the "best practice" is to include only _values_ in your XML
request, not names of tables or names of columns.
So a good rule of thumb is to follow the same restrictions as exist when
you PREPARE the SQL statement, using parameter placeholders. Then when
you get the XML request, plug in data values when executing the prepared
query.
Query parameters for prepared statements can be values, but not table or
column names, and not any other syntax element. In other words:
prepare "SELECT * FROM tablename WHERE columname = ?" -- LEGAL,
parameter will be interpreted as a literal value
prepare "SELECT * FROM tablename WHERE ? = 1234" -- the parameter will
be interpreted as another literal value, not a column name.
prepare "SELECT * FROM ? WHERE columname = 1234" -- NOT LEGAL
prepare "SELECT * FROM tablename WHERE columname ? 1234" -- NOT LEGAL,
you can't parameterize operators such as '=' vs. '!='
Regards,
Bill K.