On 2/9/2017 6:49 PM, Gene Wirchenko wrote:
...
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 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
_______________________________________________
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/[email protected]
** 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.
