Hello:
I have never bothered with variable substitution in SQL
statements. My tables are local. It did not seem to quite fit, and
there is my second question below.
However, I have two issues.
I have a lot of cases where I have multiple options for which
the end user might only select some of them. On one report that I am
currently updating for other reasons, there are three: date low, date
high, and client. All of these are optional.
I could write for the eight cases
select * from catx ...
select * from catx where trndate>=?datelow ...
select * from catx where trndate<=?datehigh ...
select * from catx where trndate>=?datelow and trndate<=?datehigh ...
select * from catx where clcode=?theclcode ...
select * from catx where trndate>=?datelow and clcode=?theclcode ...
select * from catx where trndate<=?datehigh and clcode=?theclcode ...
select * from catx where trndate>=?datelow and trndate<=?datehigh and
clcode=?theclcode ...
and this will work, but this would not be practical in cases where
there are more optional variables. One report has five.
To get around this, I build the expression as in this example:
* Build where expression.
local thewhere
thewhere=""
thewhere=thewhere+"trndate>=?this.datelow and trndate<=?this.datehigh"
if !empty(this.theclcode)
thewhere=thewhere+" and clcode=?this.theclcode"
endif
* Get Possible Transactions
SQLSEL * from catx;
where &thewhere;
into cursor rawdata readwrite nofilter
My first question is whether this is safe from a SQL injection
attack. It appears so, but I may be overlooking something.
My second question is whether it is possible to have this sort
of protection for other statements. I use browses, and the
substitution does not work there, that is
browse for clcode=?theclcode
does not work. (Missing operand error)
With browses, I have some with even more optional values. One
has twelve. 4096 different versions of a statement is really too much.
If I do it for one (SQL), I want to do it for the other (browses).
Sincerely,
Gene Wirchenko
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com/mailman/listinfo/profoxtech
Searchable Archive: http://leafe.com/archives/search/profox
This message:
http://leafe.com/archives/byMID/profox/816e2743ac47642e79acda92d67161b3@mtlp000084
** All postings, unless explicitly stated otherwise, are the opinions of the
author, and do not constitute legal or medical advice. This statement is added
to the messages for those lawyers who are too stupid to see the obvious.
