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