[prog] MySQL connections from PHP files - Close or keep it open for queries?

[Jacinta Richardson]

 > However is it perferred to keep an open
> connection to the database with multiple queries?
> Or to have an individual connection/disconnection with each query?

The simplest answer is that it's preferred and encouraged to connect to the 
database once per script invocation.  This is because connecting to the database 
is slow so you want to minimise the number of times you do that.

However, you question leads me to comment on a number of other often made 
database programming mistakes.  I'll write them up as guidelines to follow, 
rather than criticisms and hope these help you be a better coder.

1.  Abstract out your connect to database and ideally your SQL as much as possible.

    I'm not saying perform every possible SQL query before your program runs and 
then only use what you need.  But instead, write yourself a library which does 
your connection and has your common SQL queries in it (as functions).  Thus your 
library might contain:

         db_connect($database, $host, $username, $password) {
              /* Error checking? Filling in values from a config file?  etc */

              $db = mysqli($host, $username, $password, $database);
         }

         function search_entry($id) {
              /* Error checking? etc */

              $search = "SELECT title, author, date ...
                         FROM entries
                         WHERE id = ?";

              $sth = $db->prepare($search);
              $sth->bind_param("s", $id);
              $sth->execute();

              ...

              return $results;
         }

and then you'd use these exclusively throughout your program rather than 
rewriting them.

    It's a little extra work to do this.  But should you ever need to change 
from mysql to postgresql or (heaven forbid) oracle, it becomes *much* easier. 
It also means that when you need to write another program which uses the same 
database, half of the work is already done for you!

2.  Use placeholders religiously.

    Did you notice that strange question mark in my search_entry function up 
there?  Hopefully it's not that strange.  That's a placeholder.  I write a ? 
where I'd otherwise write data and then allow the database driver to make sure 
it's properly quoted and handled.

    The prepare statement allows me to create a statement once (with or without 
placeholders) and then execute it multiple times (with different values per 
placeholder) if desired.  Thus I could write:

     /* prepare the statement */
     $sth = $db->prepare($search);

     /* Tell the statement to look at the $id
     /* variable for the placeholder value */
     $sth->bind_param("s", $id);

     /* Look over ids and execute the statement for each one */
     foreach @ids as $id {
              $sth->execute();
              ...
     }


although there are better ways to do that particular query which don't involve 
hitting the database so much (ie using IN ( ) ).

    Using prepare allows the database to get ready for (optimise) your query. 
This means that if you do run it more than once you immediately get significant 
time savings.  It is particularly useful for multiple updates, deletes or more 
complex selects.

    Placeholders protect you from SQL injection attacks.  These are liable to 
occur anytime you see code like this:

       $id = $_POST['id'];
       $search = "SELECT * FROM entries WHERE id='$id'";

If I pass in the $id value of:

       ' OR id='1

then the query becomes:

       "SELECT * FROM entries WHERE id='' OR id='1'";

In this particular case the SQL injection is fairly benign.  However if instead 
it were:

       $allow = "SELECT * FROM users
                 WHERE username='$username' and password='$password'";

Then we could submit:

          username: jarich
          password: ' OR '' = '

to get:

       "SELECT * FROM users
        WHERE username='jarich' and password='' OR ''=''";

Since '' always equals '' I'd be able to log in as any user without their password!

    Using placeholders would ensure that the database would look for the user 
jarich and the password: ' OR '' = '  which is unlikely to be my password.

    Unfortunately placeholders are a fairly new addition to PHP and thus the 
interface for them is not as clean as it could be.  As a result there is an 
awful lot of PHP code out there which doesn't use them.  This includes 
commercial software as well.  Without placeholders it becomes a *lot* harder to 
ensure that your code is safe from SQL injection attacks.


3.  Don't ever pass around SQL queries if the user can change them

Hopefully this doesn't need to be said.  However even I have been guilty of 
building websites which created get strings like the following:

       some.site.au/index.php?search="SELECT%20....WHERE%20id='5'"

and then of writing:

       mysql_query($search);

of course then a user who was smarter than me edited the GET string and sent in:

       some.site.au/index.php?search="DELETE%20....WHERE%20id='5'"

which was enough to make my week very interesting.  :)

    Don't assume that users can't edit hidden fields on your form.  They can.


4.  Remember that security does matter

    I'm regularly amazed at how many people feel that since they're only writing 
web services, they don't need to learn anything about security in their 
programming language.  You're writing web services!  Of course you need to learn 
about security!  As a PHP programmer you are writing code which allows otherwise 
unprivileged people to make things happen on your web server.  Thus it is your 
responsibility to ensure that they can only make the things happen which you 
want them to.  It is your responsibility to ensure that your upload script 
doesn't allow them to upload PHP code which then effectively gives them shell 
access.  It's your responsibility to ensure that your code which provides access 
to view files on the system can't be fooled into showing them /etc/passwd.  It's 
your responsibility to ensure that the users of your program can't bring down 
the server due to resource mismanagement.

    Likewise the Perl CGI programmers are responsible for their programs.


I've writter rather more than I originally intended.  I hope the above helps. 
I've been doing this kind of thing for 10+ years now.  I've made most of the 
mistakes and learned a fair amount the hard way.  Even if you ignore most of my 
other advice I cannot stress enough how important it is to use placeholders. 
The lack of placeholders in code (not just PHP) has been one of the biggest 
security holes for script kiddies and machine compromises.

All the best,

       Jacinta

-- 
    ("`-''-/").___..--''"`-._          |  Jacinta Richardson         |
     `6_ 6  )   `-.  (     ).`-.__.`)  |  Perl Training Australia    |
     (_Y_.)'  ._   )  `._ `. ``-..-'   |      +61 3 9354 6001        |
   _..`--'_..-_/  /--'_.' ,'           | contact at perltraining.com.au |
  (il),-''  (li),'  ((!.-'             |   www.perltraining.com.au   |