PHP, Stored Procedures, and SQL Server

While working on ezmp3, I had occasion to use MS SQL Server stored procedures from PHP. A couple of people I know always moan about blogs filling up the internet with useless junk, so here, for the benefit of all mankind, is something I hope some of you may find useful!

The MS SQL/PHP documentation on this was a little sparse at the time, so I’ve decided to put together an example here for other people to see/use.

Getting a database connection is very similar to what you might do with PostgreSQL or MySQL:

<?php
function &get_conn()
{
   global $conn;
   $myServer = "localhost";
   $myUser   = "markuser";
   $myPass   = "markpass";
   $myDB     = "markdb"; 

   if(!$conn)
   {
      $conn = mssql_connect($myServer, $myUser, $myPass)
         or die("Couldn't connect to SQL Server on $myServer");
      mssql_select_db($myDB, $conn)
         or die("Couldn't select database $myDB");
   }
   return $conn;
}

/* get a connection */
$conn =& get_conn();
?>

(Some of you may frown at the use of global $conn. I don’t normally use this, but it was a quick and dirty hack, not for production use….)

So far, all pretty standard stuff.

When executing a stored procedure, you have two choices.

1. (BAD) Execute it as a standard SQL statement:

EXEC myprocedure param1, param2, param3

or

EXEC myprocedure @id=param1, @name=param2, @email=param3

This is great from the command line when using isql, or when using SQL query analyzer (damn the Americans and their Z’s!).

2. (GOOD) A far more robust solution is to use the mssql_init, mssql_bind and mssql_execute methods, and bind named (and typed) parameters to your stored procedure object. The following snippet example gives you an idea of how to do this:

<?php
/* prepare the statement resource */
$stmt=mssql_init("myprocedure", $conn);

/* now bind the parameters to it */
mssql_bind($stmt, "@id",    $id,    SQLINT4,    FALSE);
mssql_bind($stmt, "@name",  $name,  SQLVARCHAR, FALSE);
mssql_bind($stmt, "@email", $email, SQLVARCHAR, FALSE);    

/* now execute the procedure */
$result = mssql_execute($stmt);
?>

There are two great benefits to using this approach:

1. Security - You don’t have to worry about escaping characters (like apostrophes) in your variable names. This makes nasty things like SQL injection impossible.
2. Typed variables - If you try and put text in a field that should be an integer you will get an error. While nobody likes errors, it’s much better to get them here than at the database end.

So that’s it in a nutshell! I needed my stored procedure to return stuff to me, but I was having problems getting my output parameters to work. I’ve just seen the following in the online PHP documentation, posted by a chap called Matt, that may help here:

1) you need to pass your php variable by reference. mssql_bind($stmt, "@outParam", &$outParam, true)

2) php-4.3.1-win32 had a binding bug and output parameters do not bind. 4.1 and 4.3.2rc1 do not have the bug. I have not tested other versions.

I was running PHP on Windows, so it may have been this. I may not have passed the output variable by reference either, though I have a feeling I did try this… Anyway, I got around the problem by returning a normal result/record set from my procedure, and getting it in the normal way:

<?php
/* execute the procedure */
$result = mssql_execute($stmt);

/* get the row that is returned */
$row = mssql_fetch_assoc($result);

/* get my value out */
$my_id = $row['album_id'];
?>

Right, that’s about it. I hope some of you find this useful. I’m no guru on this sort of stuff, but it seemed to do the job for me. When I have MS SQL server available I try to use stored procedures whenever possible as they are so quick, they are far more robust, and they are extremely secure. In fact, I normally use the permissions manager to prevent my web user from running SELECT, INSERT, UPDATE and DELETE statements, and just give them EXEC permissions on specified stored procedures. I love SQL server… but that’s another story

Related Link: RAID Data Recovery Even the best-configured RAID system can fail due to intermittent drive failure resulting in RAID degradation or RAID array configuration lost.