MySQL Connections

Legacy Dynamo
1

Should you close the database connection after every page/script load?

mysql_close($connection_details)

Or does it matter, does MySQL close the connection anyway?

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

2

Just a little nudge up the list to see if anyone could answer?

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

3

If you open a persistent connection with mysql_pconnect, then you don’t need to close it ever. If you open a normal connection, then you’re supposed to close it, but I don’t know what the down-side might be.

Walter

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

4

Thanks Walter.

After doing the basics of creating / editing / sorting / adding / deleting entries etc from a database using php and Freeway. Working locally with MAMP, it has got me thinking more about MySQL security before releasing any of my ideas online. For example a customer email/address list, on a site. I know php is fairly easy to pick up for beginners. But should beginners really be doing this? With no formal database training.

You do have a responsibility for protecting the client data. So for this example, for a start I therefore send/receive the data over a SSL connection (https://). But what about other security issues.

With things like SQL injection, are beginners leaving themselves open to all sort of security issues? (its often said you have to assume all users are potential hackers)

I suppose the real danger is when you are posting data into the database, rather than just reading from it.

Do you know of any php/sql security checklist or sites you can use a security resource for guidance about this?

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

5

The most important thing to do is to run any user-generated content through several stages of sanitization. First would be to run trim(strip_tags($string)), and the second being mysql_real_escape_string($string). Have a look on Google for SQL injection for lots of scary stories about what can happen. But just remember that you are building a bridge into midair, and be mindful of each step and you should be fine.

Walter

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

6

For a couple of years I had been writing all my scripts for any
domains I have on my server (about 30 database backends making quite a
lot of connections) using mysql_pconnect, a couple of months back I
started to notice the SQL server failing, on looking closer I seen the
problem to be too many open connections that where not shutting down
quick enough. First I set the max connection time to 20 seconds in the
php.ini file, that didn’t work so I then disabled persistent
connections for MySQL in the php.ini file… this helped but still
didn’t stop the server failing. I finally changed * all * the
‘mysql_pconnect’ functions to ‘mysql_connect’ and since have not had
any problems with the server failing and no longer have open
connections. I checked several times a day with ‘mysqladmin proc
status’ which returned a much more healthy state, so my input on this
would be to use mysql_connect and close your connections.

HTH

On Dec 29, 2008, at 2:19 PM, waltd wrote:

If you open a persistent connection with mysql_pconnect, then you
don’t need to close it ever. If you open a normal connection, then
you’re supposed to close it, but I don’t know what the down-side
might be.

Walter

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

7

I am sure Walter will have his input on this but working with php and
MySQL is very simple and quick, scripts to do things can be written
with very few lines of code and very quickly… but, scripts written
like this will be very much recipes for security and reliability
disasters, I have always said that writing the scripts is easy and
quick but error checking and dealing with value checking and security
takes 10 times longer than writing the basic script. I worked on some
sites that have already had some php and database work and was
horrified to see what was done or rather not done in the scripts.

My input on this would be to check all the variable values before
entering into a database ‘or doing anything with it’, if a value
should be a number 8 digits long then make sure it is a number and is
no longer than 8 digits, if a value is not supposed to have a space
then make sure it doesn’t have one, check everything is within the
boundaries it is expected to be and that nothing more or less that
what is supposed to be passed is. On log-in scripts you might also
allow 3 incorrect attempts within 3 minutes and then block the user
out for a period of time etc.

You can write functions to do a lot of the variable checking for you
then these functions can be reused on all your sites, this will save
you time on the same and on future projects.

Use Google to pull up some sites on PHP security and see what input
they have to offer.

HTH

On Dec 29, 2008, at 2:54 PM, WebWorker wrote:

Thanks Walter.

After doing the basics of creating / editing / sorting / adding /
deleting entries etc from a database using php and Freeway. Working
locally with MAMP, it has got me thinking more about MySQL security
before releasing any of my ideas online. For example a customer
email/address list, on a site. I know php is fairly easy to pick up
for beginners. But should beginners really be doing this? With no
formal database training.

You do have a responsibility for protecting the client data. So for
this example, for a start I therefore send/receive the data over a
SSL connection (https://). But what about other security issues.

With things like SQL injection, are beginners leaving themselves
open to all sort of security issues? (its often said you have to
assume all users are potential hackers)

I suppose the real danger is when you are posting data into the
database, rather than just reading from it.

Do you know of any php/sql security checklist or sites you can use a
security resource for guidance about this?

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options

dynamo mailing list
email@hidden
Update your subscriptions at:
http://freewaytalk.net/person/options