Prepared Statements - where to put them

[skygremlin]

The next step in my web site Dev work is converting over to prepared statements.  I was wondering if someone could help with some pretty general questions.

 

Some Basic Info:

 - DB:  MySQL - 5.5.25 Source distribution

 - PHP: 5.4.4

 

Below is some code I want to make into prepared statements.  It's simple code querying a DB displaying results. - My PHP pages are calling functions that are written in another functions file which returns data and then displayed.  

 

For example - call from my PHP page:

//Get the Summary for the Job

   $jb_summary_set = get_jb_summ($row["job_id"]); 

   if(!mysqli_num_rows($jb_summary_set) == 0){ 

      echo "<strong>Summary</Strong><br />";

      while ($job_summ = mysqli_fetch_array($jb_summary_set)){ 

         echo $job_summ['summary'];  

      }

    echo "<br /> ";

   }

Function:              

function get_jb_summ($job_id){ 

   global $connection;

   //Escape value

   $job_id = mysqli_real_escape_string($connection, $job_id);

   $jb_summ_set = mysqli_query($connection, "select summary from job where job_id = '$job_id' and summary !=\"\"");

      return $jb_summ_set; 

I've done some reading on prepared statements and have an idea on the purpose and syntax...  But what I don't fully understand is where do I put the pieces in my code?  Do I put my prepared statements at the top of my functions file and when that file gets loaded (includes statement in my main php page) will the statements be compiled and stored, then called from a function?  Or do I put them inside the function, or not use a separate functions file at all??

 

I would prefer to not have the queries in my php pages if possible. 

 

Also I have another site I use for a small business I run...  I only have one PHP page now that connects to another DB which displays some simple figures - Invoice totals, payements received, some other related info...  Those queries I have as stored procedures inside the DB.  Can I have prepared statements in the stored procedures?  

 

I know I'm mixing a number of things here (where my confusion is coming form).  I'm trying to get a process down so I can quickly and easily copy that code for a quick web page to display data...  Once I get this understood I'll start making templates for more visually appealing pages... 

[kicken]

Prepared statements would go in the same place your queries currently are, there is no need to move them anywhere. Changing to prepared statements just means that you'll be handling how you handle user-provided inputs with the queries. Rather than inserting them directly into the query by concatenating them to the query string, you put in placeholders and then bind the parameters. So your current function:

function get_jb_summ($job_id){ 
   global $connection;
   //Escape value 
   $job_id = mysqli_real_escape_string($connection, $job_id);
   $jb_summ_set = mysqli_query($connection, "select summary from job where job_id = '$job_id' and summary !=\"\"");
      return $jb_summ_set; 

Would just change to:

function get_jb_summ($job_id){ 
   global $connection;
   $stmt = mysqli_prepare($connection, "select summary from job where job_id = ? and summary != ''");
   mysqli_bind_param($stmt, 'i', $job_id);
   mysqli_stmt_execute($stmt);
   return $stmt;
}

Assuming I got my mysqli functions correct, I'm not very familiar with that API as I use PDO generally.

 

Can I have prepared statements in the stored procedures?

Technically yes, but it is not necessary and ill-advised. Prepared statements exist to prevent SQL Injection. The contents of your stored procedures should not have any need for that. You may use prepared statements to call your procedures from your script, and should if they accept parameters. Edited August 14, 2013 by kicken

[mac_gyver]

unfortunately, the result that the mysqli prepared statement produces for a SELECT/SHOW query (unless you have PHP 5 >= 5.3.0 using the mysqlnd driver, so that you can use the $stmt->get_result() method to convert it to a mysqli_result) won't be directly usable by your calling code.
 
to lessen the impact on your calling code, and keep your functions operating like black-boxes, if you don't have the $stmt->get_result() method available, you would need to return an instance of a result class that you write that emulates the properties/methods of a mysqli_result object. you could then convert your calling code to use OOP notation so that it would be the same regardless of if the $stmt->get_result() method is available or not.
 
an alternative would be to use PDO prepared queries instead of mysqli prepared queries as the PDOStatement object that a prepared PDO query returns is exactly the same as what a normal PDO query returns. you would need to modify your calling code to use the PDOStatement object's properties.

[skygremlin]

thank you