Обсуждение: 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
Re: Sanitizing text being stored in text fields - some characters cause problems
От
Steve Crawford
Дата:
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
Re: Sanitizing text being stored in text fields - some characters cause problems
От
Steve Crawford
Дата:
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 > >
Re: Sanitizing text being stored in text fields - some characters cause problems
От
Steve Crawford
Дата:
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