--0-666255837-1226077282=:60157
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

I'm am trying to find some definitive best practises on database connection=
s with php on both mysql and oracle.
=A0
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.
=A0
For example -=A0what's the best usage - prepared statements? And does it ha=
ve 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?
=A0
I have checked out the security area on the php manual and some users notes=
- some were useful.=A0But it didnt really have a lot of info and I dont th=
ink it is comprehenive or all inclusive.
=A0
Thanks in advance. PS I would like to switch the current site from jsp to p=
hp. I was going to look into Zend IDE. Comments? Suggestions?
=A0
thanks=0A=0A=0A
--0-666255837-1226077282=:60157--

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: christopher.jones [at] oracle.com Tel: +1 650 506 8630
Twitter: http://twitter.com/ghrd Free PHP Book: http://tinyurl.com/f8jad

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

On Fri, Nov 7, 2008 at 3:39 PM, Christopher Jones
<christopher.jones [at] oracle.com> 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 myself, but I do think it's a good
suggestion for small applications. I'm playing devil's advocate here.
But I think all PHP developers should have a solid understanding of
security before they release anything. Frameworks and prepared
statements are not an alternative to understanding security issues.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

--0-275401147-1226330879=:98230
Content-Type: text/plain; charset=us-ascii

Thank you Christopher - this gives me some much needed direction.

--- On Fri, 11/7/08, Christopher Jones <christopher.jones [at] oracle.com> wrote:

From: Christopher Jones <christopher.jones [at] oracle.com>
Subject: Re: [PHP-DB] sql injections/best practises
To: fmh102560 [at] yahoo.com
Cc: php-db [at] 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: christopher.jones [at] oracle.com Tel: +1 650 506 8630
Twitter: http://twitter.com/ghrd Free PHP Book: http://tinyurl.com/f8jad




--0-275401147-1226330879=:98230--

--0-1794711005-1226330937=:12547
Content-Type: text/plain; charset=us-ascii

thank you so much Fergus for all this great info - this will get me started.

--- On Sat, 11/8/08, Fergus Gibson <fgibson75 [at] gmail.com> wrote:

From: Fergus Gibson <fgibson75 [at] gmail.com>
Subject: Re: [PHP-DB] sql injections/best practises
To: php-db [at] lists.php.net
Date: Saturday, November 8, 2008, 12:42 PM

On Fri, Nov 7, 2008 at 3:39 PM, Christopher Jones
<christopher.jones [at] oracle.com> 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 myself, but I do think it's a good
suggestion for small applications. I'm playing devil's advocate here.
But I think all PHP developers should have a solid understanding of
security before they release anything. Frameworks and prepared
statements are not an alternative to understanding security issues.

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php





--0-1794711005-1226330937=:12547--

--0-1284968855-1226335741=:69518
Content-Type: text/plain; charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable

Hi Christopher
=A0
One other question. Our current site is written in jsp with Oracle. I'd lik=
e to use PHP. Do you have any thoughts on this?
=A0
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 bett=
er choice for imbedded html. For the most part the site mainly consist of r=
elatively simple db retrieval, for several of our products. Which then list=
s various documentation and reference material for each, all dynamic. And t=
hen we have a few very simple stand alone user input forms occasionally.
=A0
Oracle is the db on most of the site - a little mysql too.


--- On Fri, 11/7/08, Christopher Jones <christopher.jones [at] oracle.com> wrote=
:

From: Christopher Jones <christopher.jones [at] oracle.com>
Subject: Re: [PHP-DB] sql injections/best practises
To: fmh102560 [at] yahoo.com
Cc: php-db [at] 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 in=
put
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 a=
t
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: christopher.jones [at] oracle.com Tel: +1 650 506 8630
Twitter: http://twitter.com/ghrd Free PHP Book: http://tinyurl.com/f8ja=
d

-- PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

=0A=0A=0A
--0-1284968855-1226335741=:69518--

On Mon, Nov 10, 2008 at 8:49 AM, mignon hunter <fmh102560 [at] yahoo.com> 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

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: christopher.jones [at] oracle.com Tel: +1 650 506 8630
Twitter: http://twitter.com/ghrd Free PHP Book: http://tinyurl.com/f8jad

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php