On 02/24/2012 09:58 AM, Tanstaafl wrote:
> Hi all,
>
> I have another question, this one related to storing text in fields.
>
> The DB in question is very old, and has an html/php based (so,
> basically just a lot of web forms with a Submit button on them) front
> end.
>
> We have recently upgraded the code so that it will run on newer
> versions of postgresql (9.1) (and php/apache). Everything seems to be
> working well, with one exception...
>
> We have always had a very few minor problems with saving some of the
> web forms if the text fields had certain characters in them.
>
> For example, one of the fields would simply not save if the text field
> had the characters 'char' anywhere in the field. So, if my comment was
> 'Charles is a bonehead', it wouldn't save this text.
>
> Since we updated, we no longer have this *particular* problem, but we
> have many other similar ones - for example, an apostrophe entered
> anywhere in the text will cause the record to not be saved properly (I
> think it may get saved somewhere, but not linked to the correct record).
>
> One problem is, the people using this DB will copy/paste stuff from
> all kinds of sources (emails, from web sites, etc) and paste the text
> into these fields (basically notes/comments fields), so we need to
> learn the correct way to 'sanitize' the text so that pretty much any
> characters that can be typed on a keyboard should be able to be used.
Your bigger problem is that you are going to be hacked. (Obligatory xkcd
reference: http://xkcd.com/327/). Type
"sql injection" into your search-engine pronto.
And this is not a PostgreSQL problem, it is a problem of ensuring that
you correctly sanitize *all* input and make sure to escape it as
appropriate to the next process in the line be it a database, bash
script, etc.
It's not a problem with how the data is stored, it is a problem with how
you are generating the SQL statements to store it. If you are just
taking their raw input, for example, and turning that into "insert into
foo values ('$rawinput');" that you send to the server then the moment
someone includes an apostrophe then your input string is closed.
Now suppose that $rawinput is (to be unoriginal)
Robert'); drop table students;--
Now you will submit
insert into foo values ('Robert'); drop table students;--');
Add Robert to foo and kiss the students table goodbye.
Now use your imagination to see how to list all tables from the pg_class
table and start deleting them or dumping their contents back to the user.
You must either properly escape your data with something like
pg_escape_string
(http://php.net/manual/en/function.pg-escape-string.php) or, better yet,
use prepared statements (see info in the PHP and PostgreSQL manuals).
Cheers,
Steve