Re: Sql injection attacks - Mailing list pgsql-general

At 04:33 PM 7/26/2004 +0100, Geoff Caplan wrote:

>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:

I would like to emphasize that my suggestion is items 0 to 3 as a whole.
They were not really meant as independent items.

0) Make it much easier to the right thing than the wrong thing. Prepared
statements help. You may still need to make it simpler for the programmers.
1) validate/filter/escape all input to your program so that your program
(NOT other programs[1]) can deal with it.
2) validate/filter/escape output from your program to each destination
accordingly so that each destination can deal with it and treat it correctly.
3) Assume by default input could be from hostile sources, unless provable
otherwise.
---

More detailed example scenario:
First text is submitted to your program from a browser.
- You may wish to limit max submission size at the webserver level to X
megabytes (the size of the maximum submission - including attachment
uploads) so as to not use up too much memory in case of a malicious
submission.
- you may wish to limit the max text length at the program level to Y bytes
and do other sanity checking etc.
- You may wish to remove all ctrl characters except cr/lf from the submission.
- you may also wish to have a input parameter definining what sort of text
it is - preformatted text, normal text, restricted HTML.
  Say the text is to be treated as nonpreformatted normal text so:
  - You may wish to trim leading/trailing whitespace and squeeze extra
contiguous whitespace.
  - You may wish to convert all cr to lfs then squeeze contiguous lfs to
single lfs.

Next: Text is submitted from your program to the database.
- you use a standardized function to insert the text into the database.
e.g. dbdo("Error to throw if fail","insert into test (data) values
(?)","$thetext");
or to put a row into a table.
dbput("Error to throw if
fail","tablename","$whereclause_to_try_to_match",$ColsAndDataToInsertOrUpdateIfExist);

Note: avoid situations that could allow things like this:
update tablea set data=3-? where a=1;
If the parameter is -1 and you are not careful the SQL could become this:
update tablea set data=3--1 where a=1;
-- is a comment! Wow what a great design huh?

I'm not sure what is the best way around this but you could try:

update tablea set data='3'-'-1'::integer where a=1;
Or ensuring there's a space between ? and all other characters.

Next: text is read from the database into your program
- limit text length to Y bytes and other sanity checking.
- you filter/process the text accordingly

Next: text is sent from your program to a web browser.
  you read what sort of text it is.
  Assuming the text is to be treated as normal text instead of HTML or
preformatted text.
  - you filter and process the text, quoting & to & < to < and the
other usual suspects and convert all lf to <p>.

Result: The browser displays text as text and not some weird malicious
browser-subverting javascript. Phew :).

Error occurs: text is sent from your program to the logs.
You limit the text to a max length of A bytes.
You escape control characters and other junk so the logs and whatever you
use to view the logs don't do strange things.
You chop the text to pieces of max length B bytes
You send the text to the system logs and indicate dataloss/truncation if any.
Result: you can view the logs safely even on crappy terminals, and
backspaces etc don't result in stuff being hidden or rewritten.

Remember to make all that easy to do, otherwise it won't be done and one
day someone might abuse systems running your program!

Have a nice day,
Link.

p.s. sorry this is a bit off-topic. But tons of people seem to do things
the wrong way. Even the postgresql.org site had an SQL injection prob with
the prev survey form ;).