Thread: text field constraint advice
PostgreSQL users,
I would like to use a text field in a table and limit the size to
reduce the chance of denial-of-service/buffer overflow/etc. I assume I
can define table fields similar to the following
"field_name text check (len(field) < 160)" although my syntax is
probably wrong. Is checking text length considered a good idea? If so,
what would be a reasonable limit? I was thinking about 10 *
average_field_char_length (if avg value is 16 char, set limit to 160
char). Thanks in advance for your opinions.
Thanks,
Dale
On Wed, Jan 26, 2005 at 12:27:17AM -0600, Dale Sykora wrote: > I would like to use a text field in a table and limit the size to > reduce the chance of denial-of-service/buffer overflow/etc. I assume I > can define table fields similar to the following > "field_name text check (len(field) < 160)" although my syntax is > probably wrong. You could use varchar(n) instead of text with a check constraint. > Is checking text length considered a good idea? That depends on the application and the trustworthiness of the data source. If you know that values should never exceed a certain length and you want to prevent obviously bad values from being inserted, then enforcing a length limit makes sense. > If so, what would be a reasonable limit? I was thinking about > 10 * average_field_char_length (if avg value is 16 char, set > limit to 160 char). Again, that depends on the application. If you're storing product part numbers then most of them will probably fall close to the average length, so allowing ten times the average length would be unnecessary. On the other hand, if you're storing product descriptions then you might need to allow for greater variation. Use whatever makes sense for the type of data you're storing. -- Michael Fuhr http://www.fuhr.org/~mfuhr/
On Wed, 2005-01-26 at 00:08 -0700, Michael Fuhr wrote:
> On Wed, Jan 26, 2005 at 12:27:17AM -0600, Dale Sykora wrote:
>
> > I would like to use a text field in a table and limit the size to
> > reduce the chance of denial-of-service/buffer overflow/etc. I assume I
> > can define table fields similar to the following
> > "field_name text check (len(field) < 160)" although my syntax is
> > probably wrong.
>
> You could use varchar(n) instead of text with a check constraint.
In this case I would advise using text instead of varchar(n). The reason
is that varchar(n) implies an intrinsic, unchangable rule that the field
never exceed a length of n. An example of that might be a state
abbreviation.
The check constraint is more able to adjust to changing needs.
In fact, I may go so far as to say that it's the application's
responsibility to verify the length (at the same time that it's escaping
the SQL special chars). The reason for that is because the database
wouldn't be corrupt or invalid in any way if the text field contained
(for example) 161 chars. So, it should really be more a matter of
security against DoS attacks, which is the domain of the application.
Also the application is the only one that knows what to do in case the
string is too long, so why bother sending it to the database to see if
it is too long?
But from a technical standpoint, it's really all the same, so he can use
whatever he feels comfortable with.
As for choosing a maximum number, you basically want it high enough that
no significant number of well-meaning people are thwarted by it (you
don't want someone with a long name being upset with you and going to a
competitor), and low enough to make an attacker realize that he's not
going to accomplish anything and go away. If the number is 1000, it
might make the attacker think he's accomplishing something and he might
hang around longer looking for other openings.
Regards,
Jeff Davis
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Well, that's all fine as long as the hacker does not connect directly to the database server when attempting his attack. Check it in the app yes, but if this is really a genuine concern, it should be reinforced by the server as an added precaution. On Jan 26, 2005, at 3:01 AM, Jeff Davis wrote: > In fact, I may go so far as to say that it's the application's > responsibility to verify the length (at the same time that it's > escaping > the SQL special chars). The reason for that is because the database > wouldn't be corrupt or invalid in any way if the text field contained > (for example) 161 chars. So, it should really be more a matter of > security against DoS attacks, which is the domain of the application. > Also the application is the only one that knows what to do in case the > string is too long, so why bother sending it to the database to see if > it is too long? - ----------------------------------------------------------- Frank D. Engel, Jr. <fde101@fjrhome.net> $ ln -s /usr/share/kjvbible /usr/manual $ true | cat /usr/manual | grep "John 3:16" John 3:16 For God so loved the world, that he gave his only begotten Son, that whosoever believeth in him should not perish, but have everlasting life. $ -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (Darwin) iD8DBQFB95gk7aqtWrR9cZoRAvk0AJwPM0obldPGktkjJWkBC11iMrPtTQCgiQfa WbG/Bdj+yG9DSaTbSvRUlT0= =c4+z -----END PGP SIGNATURE----- ___________________________________________________________ $0 Web Hosting with up to 120MB web space, 1000 MB Transfer 10 Personalized POP and Web E-mail Accounts, and much more. Signup at www.doteasy.com
Generaly network security suggests that your database server should not allow connections from external addresses (including for services like ssh as well as pgsql). iptables can help acheive this if your servers are all on public IPs (also not a very good idea), otherwise the best place to configure this is at your firewall/router. Alex Turner NetEconomist On Wed, 26 Jan 2005 08:16:19 -0500, Frank D. Engel, Jr. <fde101@fjrhome.net> wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Well, that's all fine as long as the hacker does not connect directly > to the database server when attempting his attack. Check it in the app > yes, but if this is really a genuine concern, it should be reinforced > by the server as an added precaution. > > On Jan 26, 2005, at 3:01 AM, Jeff Davis wrote: > > > In fact, I may go so far as to say that it's the application's > > responsibility to verify the length (at the same time that it's > > escaping > > the SQL special chars). The reason for that is because the database > > wouldn't be corrupt or invalid in any way if the text field contained > > (for example) 161 chars. So, it should really be more a matter of > > security against DoS attacks, which is the domain of the application. > > Also the application is the only one that knows what to do in case the > > string is too long, so why bother sending it to the database to see if > > it is too long? > - ----------------------------------------------------------- > Frank D. Engel, Jr. <fde101@fjrhome.net> > > $ ln -s /usr/share/kjvbible /usr/manual > $ true | cat /usr/manual | grep "John 3:16" > John 3:16 For God so loved the world, that he gave his only begotten > Son, that whosoever believeth in him should not perish, but have > everlasting life. > $ > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.2.4 (Darwin) > > iD8DBQFB95gk7aqtWrR9cZoRAvk0AJwPM0obldPGktkjJWkBC11iMrPtTQCgiQfa > WbG/Bdj+yG9DSaTbSvRUlT0= > =c4+z > -----END PGP SIGNATURE----- > > ___________________________________________________________ > $0 Web Hosting with up to 120MB web space, 1000 MB Transfer > 10 Personalized POP and Web E-mail Accounts, and much more. > Signup at www.doteasy.com > > > ---------------------------(end of broadcast)--------------------------- > TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org >
On Wed, Jan 26, 2005 at 01:29:53PM -0500, Alex Turner wrote: > Generaly network security suggests that your database server should > not allow connections from external addresses (including for services > like ssh as well as pgsql). iptables can help acheive this if your > servers are all on public IPs (also not a very good idea), otherwise > the best place to configure this is at your firewall/router. Some people like to have constraints in the database in addition to whatever host- or network-based security is in place. The thinking is that the closer the constraints are to the data, the less likely they are to be circumvented, either intentionally or accidentally. Firewalls keep out unauthorized users, application checks provide a layer of defense against bogus data from authorized users, and constraints in the database itself prevent bad data from being entered by misbehaving applications or by users who are bypassing the application (e.g., somebody tweaking the data from a psql session). Constraints in table definitions also serve as self-documentation. -- Michael Fuhr http://www.fuhr.org/~mfuhr/