Re: Sql injection attacks - Mailing list pgsql-general

Hi folks

Doug wrote:

>> If you're trusting the user (via GET or POST data) to hand you valid
>> SQL fragments, even just column names, you Deserve To Lose.  The only
>> things that come in via GET or POST should be data values, and they
>> should either be explicitly escaped, or used in prepared statements
>> where the driver takes care of the escaping.

I don't think we are disagreeing here - seems like sound advice to me.
I am just feeling my way into this database game, so perhaps I am not
expressing myself clearly...

Lincoln wrote:

>> They are not really different schools of thought.

>> My suggestion:

>> 1) validate/filter/escape all input to your program so that your program
>> (NOT other programs) can deal with it.

That makes sense. What I am currently planning is to:

a) Restrict application urls to a small set of safe characters,
excluding spaces. The front-controller will run a safety check on the
url and abort the request if it looks odd. This should make it
impossible to inject into a GET variable. All cookies will carry a
digest with secret key so they can't be spoofed. That only leaves
posted and uploaded data to worry about.

b) Only write raw SQL for internal queries with static data. All other
queries will be either parameterised prepared queries, or constructed
via an API that will quote every value. The info from another poster
that it is OK to quote numbers makes this simple.

>> Also for PostgreSQL at least do not forget that _ and % are wildcards for
>> LIKE and similar queries and these wildcards are probably NOT escaped
>> whether explicitly by postgresql's builtin escaping functions or
>> automatically by the parameterizing methods. When I last checked you have
>> to escape them yourself if you want to.

>> Though insertion of wildcards is not exactly SQL injection they
>> often cause applications to behave differently from design.

Very useful: I wouldn't have thought of this...

>>But in web work, you are often using GET/POST data directly in your
>>SQL clauses, so the untrusted data is part of the query syntax and not
>>just a value.

>> Don't do that. If you need to do that, you are doing things wrong. Very wrong.

Please disregard that statement - I wasn't thinking clearly. What I
had in mind was that something like a product id in a url might find
its way into, say, a WHERE clause. What I overlooked is that it is
still, of course, being used as a value. My inexperience is showing,
I'm afraid!

------------------
Geoff Caplan
Vario Software Ltd
(+44) 121-515 1154