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

[Gareth Anderson]

Hi Jacinta,

> 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.

So far I have done 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.

Thanks :)

> 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:
> 
I'm currently doing a databaseConnect(), databaseClose(), databaseQuery().
However I could attempt to abstract it a little more like you have done below.

Also I've being using mysql_connect()...

>          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();

Hmm, this is very different from what I've being doing so far.
function databaseConnect()
{
  //connect to mysql
  $link = mysql_connect(host, user, pass) or die('Could not connect to
database: ' . mysql_error());
  //select the correct database
  mysql_select_db(dbname, $link) or die ("Unable to select database");

  return $link;
}

//close the link to the database
function databaseClose($link)
{
  //close link
  $result = mysql_close($link);
  
  //error closing
  if (!$result)
    {
      error("Error closing connection to the database: " . mysql_error());
    }
}

//performs a database query
function databaseQuery($query)
{
  $result = mysql_query($query);

  //checking results currently left to individual files/functions
  return $result;
}

And then using $row = mysql_fetch_assoc($result) to get to the results.

I should however attempt to abstract things a little more.

> 2.  Use placeholders religiously.
> 
>     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();
>               ...
>      }
> 
Are we using Perl or PHP at this point?
I wasn't aware of the @ids exisiting (the @ symbol). I've used foreach
without an @symbol before, could you please explain this?

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

That could be an optimization but probably outside the scope of my
current project (the lecturers emphasis is to get something working).

>     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.

I see, does this change at all with the fact that I'm using
mysql_connect() mysql_query() etc.?

>     Placeholders protect you from SQL injection attacks.  These are liable to
> occur anytime you see code like this:
> 
> 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.
> 

With statements like these "$db->prepare($search);"
Is that using objects or something in PHP, I'm not familiar with the
syntax there ? (something that I have not currently looked into). So
far I've being using PHP like a procedural language

Is there further information on placeholders? I tried to understand
your example, and I *think* I understand it.

What your saying is (?):
 $search = "SELECT title, author, date ... FROM entries WHERE id = ?";

We prepare the db query.
We then use the bind-param to change the ID we are querying for.
Then execute.

However since that function gets called multiple times, does that mean
that PHP remembers what it prepared last time?

> 3.  Don't ever pass around SQL queries if the user can change them
Point taken.
>     Don't assume that users can't edit hidden fields on your form.  They can.
Yes, currently I have disabled radio buttons in one of my forms, but
this is not the only point of security. If they did have a session
with the correct session variables, and they did submit a page which
say upgraded them from a user to a superuser (not allowed) they would
still get caught by the secuity checks.

> 
> 4.  Remember that security does matter

Security was part of the project, its not overly emphasized but its
going to be used in the real world so we can't be slack either.

> The lack of placeholders in code (not just PHP) has been one of the biggest
> security holes for script kiddies and machine compromises.

Do you have any recommended articles on placeholders in PHP? Or should
I just search the web?

Also so far I've being using a function from PHP's documentation with
minor modifications to prevent SQL injection attacks (it occurs on
everything thats submitted through $_POST in my PHP scripts):

function quoteSmart($value)
{
  // Stripslashes
  //if magic quotes are enabled then ' or " are automatically
backslashed and we don't want that..
  if (get_magic_quotes_gpc())
    {
      $value = stripslashes($value);
    }

  // Quote if not integer
  if (!is_numeric($value))
    {
      $value = "'" . mysql_real_escape_string($value) . "'";
    }
  else //we need to quote if its an integer so we don't lose any leading zero's
    {
      $value = "'" . $value . "'";
    }
  return $value;
}

I'm assuming I should still be using placeholders anyway right? :)

Regards,
Gareth