> > Right, which in turn proves that if you use macro substitution to execute
> > concatenated SQL statements in VFP, you cannot construct a statement that
> > can damage your data. Which in turn supports my contention that one size
> > does not fit all when it comes to effective secure code--a contention that
> > was already supported when I demonstrated that use of ? parameters for SQL
> > only makes sense in monolithic apps.
> >
>If the app does not work with VFP data, multiple lines can be constructed
>inserting the line terminator, like ";" for mysql.
And as I said, I recognize that SQL injection can be done using a
brain-dead language that employs ordinary punctation symbols as end-of-line
characters. What I am saying is that it can't be done in VFP if you use
macro substitution to execute the command. So why would I bother rewriting
my VFP app to use ? parameters instead?
You would say, "because if you use a different back end with SQL
pass-through then you have a problem."
To which I reply, "My application already validates user input and won't
permit any SQL commands that I do not explicitly want to use to be inserted
into a variable." So again, I don't need to use ? parameters.
> > >"Select * From Users where username = '" + varUsername + "' And password
> > =
> > '>" + varPassword + "'"
> >
> > Not sure where you're going with that.
> >
>
>Have you even TRIED to run this code?
Yes, see below.
> >
> > You can't concatenate anything containing "&&" to a string in VFP. That
> > surprised me, frankly, but you can't. It won't work; you'll get "Command
> > contains unrecognized phrase/keyword":
> >
> > myvar = "Select * From Users where username = '" && ok
> > myvar = myvar + "admin' &&" && Error!
> > myvar = myvar + "admin'" && ok
> > myvar = myvar + " &&" && Error!
> > myvar = "" && ok
> > myvar = myvar + "&&" && Error!
> >
> >
>I am NOT doing that in my line of code, run it first.
Yes, you are. Your suggestion to enter admin ' && as the login name, and
anything as a password, will cause VFP to attempt to concatenate "&&" to
the SQL query string to be executed, which results in "Command contains
unrecognized phrase/keyword".
> > Also, it's insane to let users construct their own SQL commands, or to
> > allow them to select a table like "users" or a field like "password" from a
> > list of things that can be returned to them.
> >
> > So the example is not going to occur in the wild unless the application was
> > written by an idiot.
> >
> >
>I am not doing that either in my line of code.
Yes, you are. Your line of code was:
Select * From Users where username = '" + varUsername + "' And password
= '" + varPassword + "'"
Assuming correct login and password are supplied, it will return all of the
fields in the user's account. That's what "SELECT *" does. Why would I use
such a query to log somebody in?
> > When users log into my app, they fill in blanks for username and password,
> > and the app executes:
> >
> > "SELECT userid FROM users WHERE login == '" + mylogin + "' AND password ==
> > '" + mypassword "'"
> >
>
>EXACTLY my point. Enter [admin ' &&] (without square brackets) as username
>and a random password. ( or any admin user name, I bet your users know the
>username of their admin)
I just did it, using my own login. As expected, I got Error 36 ("Command
contains unrecognized phrase/keyword") and the application crashed harmlessly.
Did YOU try to run this code in VFP?
> >
> > I'm not arguing that SQL injection doesn't happen. I'm arguing that use of
> > ? parameters is not a viable solution in all situations, and is not
> > necessary in many situations. I am arguing, instead, that if you do not let
> > users insert SQL command terms into an expression, by controlling the
> > tables the user can query and the fields that can be returned, and by
> > validating what they do enter, at the GUI or business object level, then
> > you can be secure without ever using a single ?, which means you don't have
> > to bend your design out of shape in order to be in a position to use ?
> > parameters.
>Parameters should be passed as sql parameters in any situation. There is NO
>situation where concatenation works and sql parameters don't.
Yes there is: in distributed apps where the variables are not populated in
the same scope as the SQL is executed in. It is also, in my opinion, insane
to maintain potentially hundreds of private variables for all of the
possible parameters that might be used in queries in a complex application.
I do stuff that's a lot more complex than "SELECT orders.ordernum FROM
orders..."
And, in my opinion, it's also insane to maintain hundreds of parameterized
views, and have to add more whenever a user needs a new query that hasn't
already been anticipated, when instead we can write a well-designed query
engine that can create hundreds of queries on the fly, including queries
the developer never even thought of. This can be done a lot more easily
using input validation and concatenation than it can with private variables
and ? parameters. The front-end load of designing the validation system is
well worth the time saved in maintenance later on.
>The reverse
>was demonstrated already by you, in your first message (remember that
>commented EXECSCRIPT?)
My example was intended to demonstrate that macro substitution in VFP
solves this problem. I would never use EXECSCRIPT() to execute anything
whose content is not entirely within my control. However, macro
substitution is safe in VFP precisely because you can't execute more than
one line of code at a time, and a single line of valid SQL code cannot
contain anything like the scary scenarios that people talk about when they
warn about SQL injection. ALL of those scenarios rely on various brain-dead
implementations of SQL that use simple punctuation as end-of-line
characters. ALL of them, as far as I can see.
>In my example above, using sql parameters will NOT log in the user at all.
In your example above in VFP using concatenation will NOT log in the user
at all either.
> > There are no panaceas in life, or in programming. There are no "best
> > practices" that are not less than best in at least some situations.
> > Flexibility and creativity are to be valued. That is the general argument I
> > am making; use of ? parameters is just one of many millions of examples
> > where my argument holds true.
>
>This is wrong on so many levels that I don't even try to argue.
It is? I would hate to have to see the world through your eyes then.
Ken Dibble
www.stic-cil.org
_______________________________________________
Post Messages to: [email protected]
Subscription Maintenance: http://leafe.com/mailman/listinfo/profox
OT-free version of this list: http://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.
