Stored Procedures on PHP and Microsoft SQL Server
By David Clark
November 26th 2003
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.
As an example, to call the sample stored procedure defined above, you would use the following lines of code:
$oQuery = new SqlCommand('GetCustomerList');
$oQuery->addParam('StoreId', $_GET['StoreId'], 'int');
$oQuery->addParam('CustomerType', $_GET['CustomerType'], 'varchar', 50);
// Assume you have an open PEAR database connection ($pearDB)
$arrResults = $oQuery->getAll($pearDB);
That seems a little more readable than dynamically building the SQL string to call this procedure yourself, doesn't it? And, because you define each parameter to the stored procedure with the actual database field type, the SqlCommand class is able to perform variable "typing" for security -- a strong method of preventing SQL Injection Attacks. For example:
intvalues are forced to integers using the built-in PHP functionintval()datetimevalues are validated as dates using a regular expression filtervarcharvalues are trimmed to meet the maximum size requirements if necessary, and filtered to escape single quotes
What about return values and output parameters?
Right, I did mention those, didn't I? Well, the ability to retrieve return values is enabled by default with the SqlCommand class (as mentioned above). So, in our example, if you wanted the actual return value of the GetCustomerList procedure, all you would need to do is call $oQuery->getReturnValue().
Output parameters are almost as easy to retrieve, but do require an extra line of code when configuring your SqlCommand object. Let's assume we have a different stored procedure that actually uses some output parameters, like the following:
GetStoreCustomerCount @StoreId int, @CustomerCount int OUTPUT
In this case, you can modify your configuration of the SqlCommand class, and easily obtain the value of this new output parameter, as in the following:
$oQuery = new SqlCommand('GetStoreCustomerCount');
$oQuery->addParam('StoreId', $_GET['StoreId'], 'int');
$oQuery->addParam('CustomerCount', null, 'int');
// One extra method call is required to configure a parameter for output
$oQuery->setParamDirection('CustomerCount', true);
// Assume you have an open PEAR database connection ($pearDB)
$arrResults = $oQuery->getAll($pearDB);
// Now retrieve the value of your output parameter
$count = $oQuery->getOutputValue('CustomerCount');
That's all there is to it. You can configure any number of output parameters, and retrieve their values (as well as the return value of the stored procedure), plus any query results your stored procedures happen to yield, via SELECT statements.
What's really going on behind the scenes?
If you've ever executed a stored procedure yourself directly through the Microsoft Query Analyzer, you'll know that return values and output parameters are not returned automatically there, either -- you have to do a little extra work. For instance, in order to find the return value for a stored procedure, you need to write something along these lines:
DECLARE @res int
EXEC @res = GetCustomerList 1, 'SomeType'
SELECT @res
The query result for SELECT @res contains the return value of your stored procedure call. Retrieving the values of output parameters is somewhat similar, typically like this:
DECLARE @out_value int
EXEC GetStoreCustomerCount 1, @out_value OUTPUT
SELECT @out_value
Every return value or output parameter that you want to track must first be declared appropriately (of the correct data type), then included in your actual stored procedure call. The values themselves can then be returned using a SELECT statement following the execution. The SqlCommand class automatically wraps your database queries with these types of statements to provide the stated functionality of each.
Conclusion
Stored procedures are a powerful tool that should be utilized extensively when you develop software that interacts with SQL Server. Although you can use them directly in PHP -- similar to dynamically constructed query statements -- your code may benefit from using a more structured approach. The SqlCommand class should provide this structure, and in the process help improve the simplicity, maintainability and security of your project.
08/03/2014 10:13:31