Re: text column constraint, newbie question

On Mar 23, 2009, at 10:11 PM, RebeccaJ wrote:

> On Mar 22, 12:36 pm, scott(dot)marl(dot)(dot)(dot)(at)gmail(dot)com (Scott Marlowe) wrote:
>> ayup. As long as they're legal for your encoding, they'll go right
>> in.
>> If you wanna stuff in anything no matter the encoding, use a database
>> initialized for SQL_ASCII encoding.
>
> Thanks, everyone, for your contribution to this thread. I'm
> approaching the database design of my web application differently,
> now. Before, I was planning to have CHECK constraints in all of my
> text or char fields, to keep out all semicolons, single quotes, and
> anything else that looked dangerous. Now I'm thinking that I'll be
> using htmlentities(), pg_escape_string() and pg_query_params() as
> safety filters, and otherwise allowing users to store whatever they
> want to, in the fields where I store/retrieve user input.

Yes indeed.

But don't use together:
* use pg_escape_string() or pg_query_params() to escape data that goes
INTO your database, and
* use htmlentities() on data that comes OUT of it, and only once it
gets printed to the page.

Otherwise you'll end up with data in your database that is hard to
search in (there is no collation on html entities in text fields
AFAIK, so if someone searches for "é" in your forms it doesn't match
"é" in your database) or data in your scripts that is hard to
compare (the value from a GET or POST request does not contain
entities while the value read and converted from the database does).

Alban Hertroys

--
If you can't see the forest for the trees,
cut the trees and you'll see there is no forest.

!DSPAM:737,49c81dc9129743370417724!