Re: [PHP-DB] sql injections/best practises
Thank you Christopher - this gives me some much needed direction. --- On Fri, 11/7/08, Christopher Jones [EMAIL PROTECTED] wrote: From: Christopher Jones [EMAIL PROTECTED] Subject: Re: [PHP-DB] sql injections/best practises To: [EMAIL PROTECTED] Cc: php-db@lists.php.net Date: Friday, November 7, 2008, 5:39 PM mignon hunter wrote: I'm am trying to find some definitive best practises on database connections with php on both mysql and oracle. I'm starting to redesign a corporate website and am trying to find out more about security and the best practises for database queries and user input form handling. For example - what's the best usage - prepared statements? And does it have to be php 5? I need preferably a one stop shop as opposed to looking at dozens of different places. Can you advise a particular book? Website? I have checked out the security area on the php manual and some users notes - some were useful. But it didnt really have a lot of info and I dont think it is comprehenive or all inclusive. Thanks in advance. PS I would like to switch the current site from jsp to php. I was going to look into Zend IDE. Comments? Suggestions? thanks PHP 5.2 is the way to go for new projects: PHP 4 isn't being maintained. Binding/preparing statements is the way to go. Here are quotes about them with MySQL Oracle They are useful for speeding up execution when you are performing large numbers of the same query with different data. They also protect against SQL injection-style attacks. (From PHP and MySQL Web Development, 4th Edition, Luke Welling and Laura Thomson) If I were to write a book about how to build nonscalable [note the NON] Oracle applications, then 'Don't Use Bind Variables' would be the title of the first and last chapters. [...] If you want to make Oracle run slowly [...] just refuse to use bind variables (From Expert Oracle Database Architecture, Tom Kyte) Depending on the site needs, consider a DB abstraction layer or a framework. For high performance connections in PHP OCI8 for Oracle, use oci_pconnect() and pass the character set. There are a number of Oracle-PHP books available. One free, introductory one is the Underground PHP Oracle Manual, http://tinyurl.com/f8jad (A new edition will be released in the next couple of weeks) Chris -- Email: [EMAIL PROTECTED] Tel: +1 650 506 8630 Twitter: http://twitter.com/ghrdFree PHP Book: http://tinyurl.com/f8jad
Re: [PHP-DB] sql injections/best practises
thank you so much Fergus for all this great info - this will get me started. --- On Sat, 11/8/08, Fergus Gibson [EMAIL PROTECTED] wrote: From: Fergus Gibson [EMAIL PROTECTED] Subject: Re: [PHP-DB] sql injections/best practises To: php-db@lists.php.net Date: Saturday, November 8, 2008, 12:42 PM On Fri, Nov 7, 2008 at 3:39 PM, Christopher Jones [EMAIL PROTECTED] wrote: mignon hunter wrote: I'm am trying to find some definitive best practises on database connections with php on both mysql and oracle. Most security issues come back to a simple concept. Assume anything in your scripts that is not a constant or literal to be a threat. That means any and all user submitted data is a potential attack. Ideally you should also assume that any and all data read in from the database or files is a potential attack. Assume everything is tainted. Your job then is to clean any and all input through inspection and filtering before you use it. I recommend the book Essential PHP Security by Chris Shiflett (ISBN 0-596-00656-X). It deals with database security and more. I would be happy to go into more detail on this or provide examples if it would be helpful. For example - what's the best usage - prepared statements? And does it have to be php 5? I need preferably a one stop shop as opposed to looking at dozens of different places. Can you advise a particular book? Website? Prepared statements will prevent SQL injection, but that is only one potential vector for attack. Keep in mind too that prepared statements are not necessary to prevent SQL injection and they aren't always the most appropriate way to do it. That said, they are the simplest way to protect your database. I'll outline a way that a database was used to attack an application. The attack wasn't particularly dangerous, but it was embarrassing for the company involved. In this case, the application took form input from a site visitor and saved it in the database. Then the site owner could retrieve the input and view it. Unfortunately, some visitors decided to put script tags in containing a Javascript redirect. Since the application trusted the data coming back from the database (not a best practice), it didn't attempt to filter it in anyway before sending it to the browser. The result was that when the site owner tried to retrieve the form submission data, he would find himself redirect to another website of the attacker's choosing. While no data was compromised in the attack, it did raise doubts about the security of that company's products. This kind of attack could easily be prevented by assuming that the data coming out of the database is tainted and then filtering it with htmlentities(). The result of that would have been that the script didn't run and didn't redirect the browser. This was the solution that the company implemented. I hope this example highlights why it's important to have a full understanding of security and related best practices. Just understanding methods to defeat SQL injection is not enough to ensure that your application is secure, and the aforementioned book will give you a security mindset that you can apply to all threat vectors. You also asked about PHP versions. I do recommend you use PHP 5. As mentioned, PHP 4.4.9 is the last release of PHP 4. There is no promise to address any further security issues in PHP 4 if they are discovered. PHP 5 also has other, non-security advantages over PHP 4. Most notable is a robust object model for we OOP types, but I also like decisions they made to bundle in certain modules missing from PHP 4. Thanks in advance. PS I would like to switch the current site from jsp to php. I was going to look into Zend IDE. Comments? Suggestions? Ugh. That's my comment. I assume we're discussion Neon here, the new Eclipse-based Zend Studio. The installation is huge and bloated, and I find it doesn't work very well at all for remote files over FTP. I really didn't care for it. If you love Eclipse, though, you will probably like it. I believe there's a free trial of the Studio, so you should try it rather than listening too much to opinions from the peanut gallery. I use UEStudio. It's not perfect, but it's a very robust, general programmers' editor. It's much faster and it makes difficult Eclipse tasks easy. It also has full Javascript scripting built into it, so it's very extensible. You can download a trial: http://www.ultraedit.com/downloads/uestudio_download.html Depending on the site needs, consider a DB abstraction layer or a framework. You can rely on frameworks to provide security to your application, but keep in mind that frameworks can contain vulnerabilities and bugs. They are made by people who can make mistakes. More significantly, if you are making an intensive application, you may find it reaches a point where the framework isn't scalable. I love and use abstraction, but abstraction does come with a performance price. For simple
Re: [PHP-DB] sql injections/best practises
On Mon, Nov 10, 2008 at 8:49 AM, mignon hunter [EMAIL PROTECTED] wrote: One other question. Our current site is written in jsp with Oracle. I'd like to use PHP. Do you have any thoughts on this? Your post, mignon, was pretty clearly directed to Christopher, but I hope neither of you will be unhappy if I make my own comment. I look forward to seeing what Christopher thinks as well, but I do have some thoughts on this. First, I will say that I have never worked with JSP. I have worked with Java, and I rather enjoy it, but I have not travelled down any of the JSP trail. For that reason, I'm not going to attempt to assess JSP specifically, other than to say that it seems rather more time-consuming to learn than PHP. There is a simple reason that I choose PHP exclusively for web development. It has nothing to do with any intrinsic value of PHP. I actually think PHP is the worst designed platform I've ever worked with. There is so much about it I really don't like. But it has one overriding strength: it's everywhere. It's simple to integrate with any server, it's widely available, and hosting for other technologies like JSP, Ruby, and Python tends to be more expensive than PHP. And don't even get me started on ASP and ASP.Net, which are only truly supported on (shudder) MS servers. Now all of this said, I am leery of your idea to switch to PHP for this application you are working on. If you're not planning to move servers, the wider support for PHP isn't an advantage at all. Switching technologies because you think PHP is somehow cooler is a poor justification for what could be a costly exercise for the client, so you ought to have a really practical explanation. Why don't you want to continue working with JSP? Is there really a problem in using that platform? We're not really using Jsp as it was intended ( like using classes ) and I think it has alot of overhead and is overkill. It seems Php would be a better choice for imbedded html. This is probably true, but I am no authority on JSP. But in most cases, embedding PHP in HTML is not a best practice. Most professionals agree that there is a real value in separating presentation (HTML in this case) from logic (the PHP code). This is something all the frameworks attempt to help you do. This same idea was the impetus for the rise of model-view-controller (MVC) design pattern, its later application to web projects, and the development of Cascading Stylesheets (CSS) to separate presentation of web content from structure (HTML). As I mentioned previously, I am a little reluctant to use frameworks. For this reason I have implemented my own simple template script that allows me to put all my PHP logic in one file and all my presentation in a separate template file. My approach is similar to that of Brian Lozer. Brian is the author of bTemplate, but abandoned its development when he hit upon the real weakness in template engines and frameworks. Here's a link to his article on the subject. I'm not suggesting anyone use bTemplate; I'm encouraging people to understand why he decided it wasn't productive to use a convention template engine in the first place. http://massassi.com/php/articles/template_engines/ All the above notwithstanding, there's always an argument for saying a script is so simple the benefits of abstraction or design patterns or MVC are not of much value. But I think there are very few applications where this is true, and worst of all, I tend to find that even if you start off thinking that something is so simple and straightforward you should just bang it out in the most simple and direct way, you'll end up regretting it because the concept wasn't as simple as you thought or because the client keeps adding on to his original goal for the script 'til it becomes a messy monster you wished you designed properly in the first place... -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql injections/best practises
Hi Christopher One other question. Our current site is written in jsp with Oracle. I'd like to use PHP. Do you have any thoughts on this? We're not really using Jsp as it was intended ( like using classes ) and I think it has alot of overhead and is overkill. It seems Php would be a better choice for imbedded html. For the most part the site mainly consist of relatively simple db retrieval, for several of our products. Which then lists various documentation and reference material for each, all dynamic. And then we have a few very simple stand alone user input forms occasionally. Oracle is the db on most of the site - a little mysql too. --- On Fri, 11/7/08, Christopher Jones [EMAIL PROTECTED] wrote: From: Christopher Jones [EMAIL PROTECTED] Subject: Re: [PHP-DB] sql injections/best practises To: [EMAIL PROTECTED] Cc: php-db@lists.php.net Date: Friday, November 7, 2008, 5:39 PM mignon hunter wrote: I'm am trying to find some definitive best practises on database connections with php on both mysql and oracle. I'm starting to redesign a corporate website and am trying to find out more about security and the best practises for database queries and user input form handling. For example - what's the best usage - prepared statements? And does it have to be php 5? I need preferably a one stop shop as opposed to looking at dozens of different places. Can you advise a particular book? Website? I have checked out the security area on the php manual and some users notes - some were useful. But it didnt really have a lot of info and I dont think it is comprehenive or all inclusive. Thanks in advance. PS I would like to switch the current site from jsp to php. I was going to look into Zend IDE. Comments? Suggestions? thanks PHP 5.2 is the way to go for new projects: PHP 4 isn't being maintained. Binding/preparing statements is the way to go. Here are quotes about them with MySQL Oracle They are useful for speeding up execution when you are performing large numbers of the same query with different data. They also protect against SQL injection-style attacks. (From PHP and MySQL Web Development, 4th Edition, Luke Welling and Laura Thomson) If I were to write a book about how to build nonscalable [note the NON] Oracle applications, then 'Don't Use Bind Variables' would be the title of the first and last chapters. [...] If you want to make Oracle run slowly [...] just refuse to use bind variables (From Expert Oracle Database Architecture, Tom Kyte) Depending on the site needs, consider a DB abstraction layer or a framework. For high performance connections in PHP OCI8 for Oracle, use oci_pconnect() and pass the character set. There are a number of Oracle-PHP books available. One free, introductory one is the Underground PHP Oracle Manual, http://tinyurl.com/f8jad (A new edition will be released in the next couple of weeks) Chris -- Email: [EMAIL PROTECTED] Tel: +1 650 506 8630 Twitter: http://twitter.com/ghrdFree PHP Book: http://tinyurl.com/f8jad -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql injections/best practises
mignon hunter wrote: Hi Christopher One other question. Our current site is written in jsp with Oracle. I'd like to use PHP. Do you have any thoughts on this? My recommendation is to utilize the existing skills you have; this echoes Fergus's comment. However, PHP is very popular and if you have the luxury of being able to learn a new language, choosing PHP is not like choosing an esoteric language that someone will struggle to maintain when you move on. We're not really using Jsp as it was intended ( like using classes ) and I think it has alot of overhead and is overkill. It seems Php would be a better choice for imbedded html. For the most part the site mainly consist of relatively simple db retrieval, for several of our products. Which then lists various documentation and reference material for each, all dynamic. And then we have a few very simple stand alone user input forms occasionally. Oracle is the db on most of the site - a little mysql too. PHP will certainly help you get a working website up quickly. Oracle can easily be accessed in PHP to do the things you describe. Discussion of frameworks and abstraction layers is just a way to make you aware of their place and to ensure the application is architected to suit your current future requirements. Chris -- Email: [EMAIL PROTECTED] Tel: +1 650 506 8630 Twitter: http://twitter.com/ghrdFree PHP Book: http://tinyurl.com/f8jad -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] sql injections/best practises
On Fri, Nov 7, 2008 at 3:39 PM, Christopher Jones [EMAIL PROTECTED] wrote: mignon hunter wrote: I'm am trying to find some definitive best practises on database connections with php on both mysql and oracle. Most security issues come back to a simple concept. Assume anything in your scripts that is not a constant or literal to be a threat. That means any and all user submitted data is a potential attack. Ideally you should also assume that any and all data read in from the database or files is a potential attack. Assume everything is tainted. Your job then is to clean any and all input through inspection and filtering before you use it. I recommend the book Essential PHP Security by Chris Shiflett (ISBN 0-596-00656-X). It deals with database security and more. I would be happy to go into more detail on this or provide examples if it would be helpful. For example - what's the best usage - prepared statements? And does it have to be php 5? I need preferably a one stop shop as opposed to looking at dozens of different places. Can you advise a particular book? Website? Prepared statements will prevent SQL injection, but that is only one potential vector for attack. Keep in mind too that prepared statements are not necessary to prevent SQL injection and they aren't always the most appropriate way to do it. That said, they are the simplest way to protect your database. I'll outline a way that a database was used to attack an application. The attack wasn't particularly dangerous, but it was embarrassing for the company involved. In this case, the application took form input from a site visitor and saved it in the database. Then the site owner could retrieve the input and view it. Unfortunately, some visitors decided to put script tags in containing a Javascript redirect. Since the application trusted the data coming back from the database (not a best practice), it didn't attempt to filter it in anyway before sending it to the browser. The result was that when the site owner tried to retrieve the form submission data, he would find himself redirect to another website of the attacker's choosing. While no data was compromised in the attack, it did raise doubts about the security of that company's products. This kind of attack could easily be prevented by assuming that the data coming out of the database is tainted and then filtering it with htmlentities(). The result of that would have been that the script didn't run and didn't redirect the browser. This was the solution that the company implemented. I hope this example highlights why it's important to have a full understanding of security and related best practices. Just understanding methods to defeat SQL injection is not enough to ensure that your application is secure, and the aforementioned book will give you a security mindset that you can apply to all threat vectors. You also asked about PHP versions. I do recommend you use PHP 5. As mentioned, PHP 4.4.9 is the last release of PHP 4. There is no promise to address any further security issues in PHP 4 if they are discovered. PHP 5 also has other, non-security advantages over PHP 4. Most notable is a robust object model for we OOP types, but I also like decisions they made to bundle in certain modules missing from PHP 4. Thanks in advance. PS I would like to switch the current site from jsp to php. I was going to look into Zend IDE. Comments? Suggestions? Ugh. That's my comment. I assume we're discussion Neon here, the new Eclipse-based Zend Studio. The installation is huge and bloated, and I find it doesn't work very well at all for remote files over FTP. I really didn't care for it. If you love Eclipse, though, you will probably like it. I believe there's a free trial of the Studio, so you should try it rather than listening too much to opinions from the peanut gallery. I use UEStudio. It's not perfect, but it's a very robust, general programmers' editor. It's much faster and it makes difficult Eclipse tasks easy. It also has full Javascript scripting built into it, so it's very extensible. You can download a trial: http://www.ultraedit.com/downloads/uestudio_download.html Depending on the site needs, consider a DB abstraction layer or a framework. You can rely on frameworks to provide security to your application, but keep in mind that frameworks can contain vulnerabilities and bugs. They are made by people who can make mistakes. More significantly, if you are making an intensive application, you may find it reaches a point where the framework isn't scalable. I love and use abstraction, but abstraction does come with a performance price. For simple things, this cost is so slight you won't even notice it; but there is a point where the cost becomes significant. There's no simple way to evaluate that, though, because it depends on so many factors: traffic, server resources, specifics of the application, etc. I tend to stay away from frameworks
[PHP-DB] sql injections/best practises
I'm am trying to find some definitive best practises on database connections with php on both mysql and oracle. I'm starting to redesign a corporate website and am trying to find out more about security and the best practises for database queries and user input form handling. For example - what's the best usage - prepared statements? And does it have to be php 5? I need preferably a one stop shop as opposed to looking at dozens of different places. Can you advise a particular book? Website? I have checked out the security area on the php manual and some users notes - some were useful. But it didnt really have a lot of info and I dont think it is comprehenive or all inclusive. Thanks in advance. PS I would like to switch the current site from jsp to php. I was going to look into Zend IDE. Comments? Suggestions? thanks
Re: [PHP-DB] sql injections/best practises
mignon hunter wrote: I'm am trying to find some definitive best practises on database connections with php on both mysql and oracle. I'm starting to redesign a corporate website and am trying to find out more about security and the best practises for database queries and user input form handling. For example - what's the best usage - prepared statements? And does it have to be php 5? I need preferably a one stop shop as opposed to looking at dozens of different places. Can you advise a particular book? Website? I have checked out the security area on the php manual and some users notes - some were useful. But it didnt really have a lot of info and I dont think it is comprehenive or all inclusive. Thanks in advance. PS I would like to switch the current site from jsp to php. I was going to look into Zend IDE. Comments? Suggestions? thanks PHP 5.2 is the way to go for new projects: PHP 4 isn't being maintained. Binding/preparing statements is the way to go. Here are quotes about them with MySQL Oracle They are useful for speeding up execution when you are performing large numbers of the same query with different data. They also protect against SQL injection-style attacks. (From PHP and MySQL Web Development, 4th Edition, Luke Welling and Laura Thomson) If I were to write a book about how to build nonscalable [note the NON] Oracle applications, then 'Don't Use Bind Variables' would be the title of the first and last chapters. [...] If you want to make Oracle run slowly [...] just refuse to use bind variables (From Expert Oracle Database Architecture, Tom Kyte) Depending on the site needs, consider a DB abstraction layer or a framework. For high performance connections in PHP OCI8 for Oracle, use oci_pconnect() and pass the character set. There are a number of Oracle-PHP books available. One free, introductory one is the Underground PHP Oracle Manual, http://tinyurl.com/f8jad (A new edition will be released in the next couple of weeks) Chris -- Email: [EMAIL PROTECTED] Tel: +1 650 506 8630 Twitter: http://twitter.com/ghrdFree PHP Book: http://tinyurl.com/f8jad -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
