Stored Procedures on PHP and Microsoft SQL Server

Though it's not as common a combination as PHP and MySQL, PHP and Microsoft SQL Server can be a powerful team. You can query SQL Server databases easily and effectively using the PEAR database abstraction layer, just as you would a MySQL database. But once you start trying to use one of the primary benefits of SQL Server over MySQL -- namely, stored procedures -- a few problems quickly become apparent:

First, your PHP code is often nearly as messy as if you were dynamically building SQL statements for execution. Take the following stored procedure definition:

GetCustomerList @StoreId int,
@CustomerType varchar(50)

and consider the PHP code needed to build the SQL statement that will execute this procedure from some page submission:

$sql = "EXEC GetCustomerList @StoreId=";
$sql .= intval($_GET['StoreId']);
$sql .= ', @CustomerType=';
if ($_GET['CustomerType'] == '') {
 $sql .= 'NULL';
}
else {
 $sql .= "'" . $_GET['CustomerType'] . "'" ;
}

// Assume you have an open PEAR database connection ($pearDB)
$arrResults = $pearDB->getAll($sql);

Not exactly the most readable or aesthetically pleasing chunk of code, is it?

Second, what about when you want to do something slightly more advanced than call a stored procedure that simply queries for a list of results? Say, for instance that you'd like to retrieve return values or use output parameters in your stored procedures? There’s nothing built directly into the PEAR database library that will allow for this.

Finally, and most importantly, consider security. The code listed above, which produces the SQL string necessary to call the GetCustomerList procedure, is not at all secure. Because the value of $_GET['CustomerType'], which is assumed to come from user input, is used directly in the SQL string, with no checks for unsafe content or escaping of quotes, the SQL string that is generated could easily produce unexpected and undesired results. Most of us have read about SQL Injection Attacks far too often to take them for granted (if not, I strongly suggest you read up on them now).

Happily, there are some features built into PHP that can help minimize the likelihood of these attacks happening -- “magic quotes” and the associated “stripslashes” function, for instance. This PHP functionality can be used to “automagically” escape single quotes in all strings input through GET or POST values, which makes these strings safe for use in database queries. If you’re at all like me, though, you may find the magic quotes option a bit cumbersome to work with after a while. Also, I personally believe that the fewer global settings that I depend on the better -- I’ve moved my code to new machines too many times to depend on identical server configurations ever being anything but the exception to the rule.

Enter: The SqlCommand Class

The SqlCommand class is an object that was designed to try to minimize each of these problems, and help you produce more readable (debuggable), powerful, and secure code. The basic usage is fairly simple, containing only 6 commonly-used public methods (optional parameters are shown in square brackets):

SqlCommand([$sCommandText], [$bGetReturnValue])

Class instantiation, normally used to define the stored procedure name.

addParam($sParamName, [$oValue], [$sType], [$iLength], [$bForOutput])

Configure a parameter that must be passed to the stored procedure. The $sType option shown here is the exact SQL Server name of the variable type. Supported values currently include: bit, int, smallint, tinyint, real, float, money, text, char, varchar, datetime, and smalldatetime.

execute($oDB)

Execute without obtaining a resultset (such as for insert/update/deletes).

getAll($oDB, [$mode])

Execute and obtain a resultset (such as for select statements).

getReturnValue()

Retrieve the return value of the stored procedure.

getOutputValue($sParamName)

Retrieve the value of any output parameter defined in the stored procedure.

To actually use the SqlCommand class, you must first instantiate a new object of SqlCommand type, configure the object with the name of the stored procedure you want to execute, and set any parameters that are required. Then you can execute your stored procedure with the option of returning a resultset or not (getAll() vs. execute()). Along the way, the SqlCommand object will validate parameter values to ensure they're safe to use (which includes escaping single quotes in string values), and gives you methods by which to easily retrieve return and output parameter values from your procedure.