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

[Jacinta Richardson]

Gareth Anderson wrote:

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

> //performs a database query
> function databaseQuery($query)
> {
>   $result = mysql_query($query);
> 
>   //checking results currently left to individual files/functions
>   return $result;
> }

Checking results might be better handled here.  Since everything should check 
their results.  It depends a little on whether you have a standard way of 
dealing with errors though.

The point I was making is that ideally you will end up with *NO* SQL mixed in 
with your program code.  It'll all be inside your library instead.  This is a 
good thing because it means you don't duplicate your SQL queries for each and 
every script.  Thus if you later go and add another column to a table in your 
database you only need to update the SQL in one place.  You'll then need to 
update the *code* in the script which uses it but all your other scripts should 
continue to work perfectly (assuming you're handling your results in an 
associative array rather than positionally).

>>     /* 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?

Sorry, my mistake.  I program in Perl more than I program in PHP and that just 
snuck in.  It should be a PHP array.  so:  foreach $ids as $id ...  or whatever 
the syntax is again.  I usually look it up.

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

If you're using mysql_* because you don't know any better, then yes it can 
change because you can choose to use the mysqli (improved) options instead. 
You can find more about this over here:

	http://docs.php.net/en/ref.mysqli.html

Note that you don't have to use it in an OO sense if you don't want to.  Things 
just get much longer names if you don't.

mysql_connect etc don't allow you to prepare the database for your queries and 
thus you don't get the time savings and other benefits.

> 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

Yup, that's using objects.  This is not required though.  You could write:
	
	$sth = mysqli_prepare($db, $query);

	mysqli_stmt_bind_param($sth, "s", $id);

	mysqli_stmt_execute($sth);

to achieve the same effect.

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

That's correct.

Using placeholders allows us to signal to PHP that data goes here and then 
provide this data separately.  Doing this gives the database driver an 
opportunity to pre-process both the query and the data before binding the data 
to the SQL.  This vastly increases the speed of the query execution and also 
increases the security of our code (by disabling SQL injection attacks).

You should notice in the previous example that the question marks are not
enclosed in quote marks even though quote marks would be required
to provide valid SQL.  This is because the database driver takes care of
this factor.  This means that we don't have to be concerned about which
placeholders to put quotes around and which ones to leave without (numbers
and timestamps don't need quotes).

There are a few restrictions on where you can use placeholders: placeholders can 
only take the place of data.  You cannot use them in place of the fields you 
wish to fetch from of the database, nor the name of the tables.  They are also 
restricted to representing single scalar values. This means than you cannot use 
a placeholder to provide the whole IN list within a SELECT.  Instead you'll have 
to use placeholders for each value in the list.

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

Placeholders are usually implemented differently in each language and I'm not 
certain how they have been implemented in PHP internally.  What I *assume* 
occurs is that when you call bind_param it makes an alias to the variable you enter.

When execute is then called, execute looks up that variable (through the alias 
or reference) and uses it's *current* value.  Then you change the variable's 
value and then call execute again (which then uses the new value) etc.

As long as you use the same statement handle ($sth in this case) PHP will 
remember which prepared query we're working with and will instruct the database 
accordingly.

You can have multiple prepared queries at the same time, each stored in a 
different statement handle (variable).

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

Unfortunately PHP doesn't have much history with placeholders yet.  They've been 
around with a few other programming languages for quite some time so you'll be 
able to find ample "why use placeholders" articles in general but not that many 
for PHP specifically.

When I was organising reviews for a conference last year I was shocked to see 
that a very prominent PHP and MySQL author submitted his paper on using the two 
together with no reference to placeholders at all.  His only concession to the 
danger of what his examples were doing was a comment saying "live dangerously". 
  Unsurprisingly I returned his paper with a request that at the very least he 
show *one* example doing the right thing.  He later explained that he assumed 
everyone knew about placeholders by now.

I thought that response amazing too, as Perl has had placeholders for at least 6 
years and I still have to encourage people to use them.  :)

Searching the web is good but you might not find much.  You should read:

http://docs.php.net/en/security.database.html

for further explanations of what can go wrong, but it doesn't recommend 
placeholders yet either.  :(

I don't have any good articles I can suggest off the top of my head.  Sorry.


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

I hate magic quotes too.

>   // 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? :)

Ideally you should if you can.  Placeholders help protect you from the times 
that you forget to call quoteSmart.  For example when you're reading in some of 
your values out of your config file.

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   |