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
On 2/9/2017 6:49 PM, Gene Wirchenko wrote: ...
I have a lot of cases where I have multiple options for which theend 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 casesselect * from catx ...
...
select * from catx where trndate>=?datelow ...
...
select * from catx where trndate<=?datehigh and clcode=?theclcode ... select * from catx where trndate>=?datelow and trndate<=?datehigh and clcode=?theclcode ...
For your first question, no, it is not safe from SQL injection (WHERE clauses can have SELECTs - and from there all kinds of nasty stuff can happen). But, that's only a concern if you're letting the user 'free-type' data. if you've code that absolutely can only have a date from like a selection option, then yeah, you're probably safe. Just be advised, any free form text data entry that you allow the user to do will have a potential SQL injection problem. You could do things like validate/strip potentially damaging text (do some lookups on the web for that if you need it).
Quick aside on your string building: perhaps "pre-process" your variables. I know it's extra code, but consider:
cUseDateHigh = IIF(ISNULL(this.datehigh), DATE(9999,12,31), this.datehigh)
Then you would use "...?cUseDateHigh..." in the where.
But another idea that you already started on, is building the where string - just use some shortcuts.
thewhere = "" thewhere = thewhere + IIF(ISNULL(this.datelow), "", "trndate>=?this.datelow") thewhere = thewhere + IIF(ISNULL(this.datehigh), "", " and trndate<=?this.datehigh") thewhere = thewhere + IIF(ISNULL(this.theclcode), "", " and clcode = ?this.theclcode")
Of course you could combine those into a long, line continued, string concatenation, but you get the idea.
Now as to your second question/issue. I'm a little fuzzy on this but here is my take: - using the "?" and a variable name is not really "VFP" syntax. For VFP you'd just "... where trndate>=this.datelow and trndate<=this.datehigh..." - the "?" is needed for SQL Passthrough and parameterized views, etc. - if you want to use the same "string" construct for a SQL Passthrough statement as well as maybe a BROWSE clause, maybe 1) build the string for SQL Passthrough (with the question marks) 2) use macro substitution in the VFP BROWSE 3) CHRTRAN out the "?" for the VFP BROWSE So the VFP thing would be something like "BROWSE FOR &(CHRTRAN(thewhere, "?", "")) .... note: I'm not sure you can do it all at once. You may have to do the CHRTRAN() into another variable and then macro substitute that one for the FOR clause.
HTH, -Charlie
-
Charlie -
Gene Wirchenko