Обсуждение: Sanitizing text being stored in text fields - some characters cause problems

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.

Wo, would someone point me to the section(s) in tfm that relate
specifically to the proper way to store text that could contain
basically any of the characters that you can type on a keyboard? And is
the proper place/way to fix this on the back-end (in postgresql), or in
the php/html code - or a combination?

As you may have surmised, I am not a programmer, I'm simply trying to
get some pointers for our developers. Like I said in my last email, they
are not very well versed in postgresql yet - they are mainly Microsoft
SQLSERVER guys who are learning postgresql in preparation to switching
to it as their primary SQL DB engine for all of their projects, but our
project is what they are cutting their teeth on (shudder)... and yes,
I'm pushing one or more of them to sign up to these lists themselves,
and I'm sure they will, but they have a lot on their plate at the moment.

Thanks, and again, pointers to documentation that I can pass on to our
developers on this question are most welcome.

Simon

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

On 02/24/2012 09:58 AM, Tanstaafl wrote:
> As you may have surmised, I am not a programmer, I'm simply trying to
> get some pointers for our developers. Like I said in my last email,
> they are not very well versed in postgresql yet

I have to expand a bit on my prior email. I'm trying to be charitable,
but validating and properly escaping inputs is a basic mandatory part of
professional software development. No TODO later. No "when I get time."
No exceptions. The manager of your developers may need to pull a
Khrushchev and pound the table with his shoe to get everyone's
attention. Certainly no unvalidated inputs should get through a code-review.

Sadly, you are in good company. Sony Pictures, PBS, HBGary Federal (a
security company no-less) and even mysql.com made the news in the last
few months due to breaches tied to SQL injection vulnerabilities.

One of my standard interview questions is "what are two or three of the
top 10 software security-flaws/programming-errors." SQL injection has
been #1 on the CWE/SANS most-dangerous software error list
(http://cwe.mitre.org/top25/) for so many years that I assume the
question is a softball. Unfortunately I often just get blank stares.

Given the situation you described related to SQL there is a reasonable
chance you are at risk of OS command injection, buffer-overflow and
cross-site scripting attacks (#s 2, 3 and 4) as well. Fortunately,
proper validation and escaping is the common theme for all of them.

Don't assume that nobody will notice or figure out the vulnerability.
Automated SQL-injection vulnerability scanners are a dime a dozen.

Cheers,
Steve

Thanks very much Steve for the candid response, and more importantly the
links to get us started down the straight and narrow...

I will be taking this all to heart, and have already scheduled a 'come
to Jesus' meeting for Monday for the Project Manager.

Simon

On 2012-02-24 2:33 PM, Steve Crawford <scrawford@pinpointresearch.com>
wrote:
> On 02/24/2012 09:58 AM, Tanstaafl wrote:
>> As you may have surmised, I am not a programmer, I'm simply trying to
>> get some pointers for our developers. Like I said in my last email,
>> they are not very well versed in postgresql yet
>
> I have to expand a bit on my prior email. I'm trying to be charitable,
> but validating and properly escaping inputs is a basic mandatory part of
> professional software development. No TODO later. No "when I get time."
> No exceptions. The manager of your developers may need to pull a
> Khrushchev and pound the table with his shoe to get everyone's
> attention. Certainly no unvalidated inputs should get through a
> code-review.
>
> Sadly, you are in good company. Sony Pictures, PBS, HBGary Federal (a
> security company no-less) and even mysql.com made the news in the last
> few months due to breaches tied to SQL injection vulnerabilities.
>
> One of my standard interview questions is "what are two or three of the
> top 10 software security-flaws/programming-errors." SQL injection has
> been #1 on the CWE/SANS most-dangerous software error list
> (http://cwe.mitre.org/top25/) for so many years that I assume the
> question is a softball. Unfortunately I often just get blank stares.
>
> Given the situation you described related to SQL there is a reasonable
> chance you are at risk of OS command injection, buffer-overflow and
> cross-site scripting attacks (#s 2, 3 and 4) as well. Fortunately,
> proper validation and escaping is the common theme for all of them.
>
> Don't assume that nobody will notice or figure out the vulnerability.
> Automated SQL-injection vulnerability scanners are a dime a dozen.
>
> Cheers,
> Steve
>
>

On 02/24/2012 01:14 PM, Tanstaafl wrote:
> Thanks very much Steve for the candid response, and more importantly
> the links to get us started down the straight and narrow...
>
> I will be taking this all to heart, and have already scheduled a 'come
> to Jesus' meeting for Monday for the Project Manager.

Don't take anyone out to the woodshed. Yet. Though I consider sanitizing
input a basic part of programming, some recent surveys have found that
many if not most college and university programming courses give only a
passing look at security if they discuss security at all.

I have no inkling about the nature of your organization or where you fit
in it. As a general guide, you may be dealing with multiple issues:

1. Lack of experience and training. This can be addressed with
appropriate mentoring, training, etc. The overall development process
can play a role here. Code reviews are a good way of locating problems
and, in the process, educating programmers. You don't want code reviews
to be adversarial but rather team-oriented and educational. Nonetheless,
they fact that someone will be reviewing your code is a deterrent to
taking short-cuts.

2. Laziness and sloppiness. If you have someone who, for whatever
reason, can't be bothered with secure programming then perhaps they need
to find another place to work. Even if they are the "productive"
prima-donna.

3. Misaligned incentives or unrealistic expectations. This is the
hardest to tackle as it requires managerial discipline, patience and
understanding that is both visible and constant. It's easy to have the
"come to Jesus" meeting then immediately slip back into "we have to have
it by tomorrow", "the sales-guy is yelling that his commission is on the
line", "the client needs it yesterday - we'll have to skip the
code-review". It takes managers who will push back and tell their
superiors "we can't have it till end-of-month". Products are visible.
Security isn't.

It takes an understanding that security isn't free. Training and
mentoring take time. Code reviews take time. Testing takes time. But at
least when "Charles O'Leary" visits your site it won't croak and with
luck you will stay off the front page of the Times.

Cheers,
Steve