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:
int
values are forced to integers using the built-in PHP functionintval()
datetime
values are validated as dates using a regular expression filtervarchar
values 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.
Don’t forget to download the code from this article here.
Frequently Asked Questions (FAQs) about PHP and Microsoft SQL Server
How can I connect PHP to Microsoft SQL Server?
To connect PHP to Microsoft SQL Server, you need to use the SQLSRV extension provided by Microsoft. First, download and install the SQLSRV extension that matches your PHP version and server. Then, in your PHP script, use the sqlsrv_connect() function to establish a connection. This function requires server name, username, and password as parameters. If the connection is successful, it will return a connection resource that you can use for subsequent SQL operations.
How can I execute a SQL query using PHP?
To execute a SQL query using PHP, you can use the sqlsrv_query() function. This function takes three parameters: the connection resource, the SQL query string, and an optional array of parameters. The function returns a statement resource on success, or false on failure. You can then use other SQLSRV functions to fetch the results.
How can I handle errors in SQLSRV?
SQLSRV provides a function called sqlsrv_errors() to handle errors. This function returns an array of error arrays, each containing an SQLSTATE, code, and message. You can call this function after any SQLSRV function to get detailed error information.
How can I fetch data from a SQL query result?
You can fetch data from a SQL query result using the sqlsrv_fetch_array() function. This function retrieves the next row of data as an array. The array keys can be either field names or numeric indices.
How can I execute a stored procedure in PHP using SQLSRV?
To execute a stored procedure, you can use the sqlsrv_query() function. The SQL query string should contain the EXEC statement followed by the stored procedure name and any parameters. The function returns a statement resource that you can use to fetch the results.
How can I insert data into a SQL Server database using PHP?
To insert data into a SQL Server database, you can use the sqlsrv_query() function with an INSERT INTO statement. The SQL query string should contain the table name and the values to insert.
How can I update data in a SQL Server database using PHP?
To update data in a SQL Server database, you can use the sqlsrv_query() function with an UPDATE statement. The SQL query string should contain the table name, the column to update, and the new value.
How can I delete data from a SQL Server database using PHP?
To delete data from a SQL Server database, you can use the sqlsrv_query() function with a DELETE FROM statement. The SQL query string should contain the table name and the condition for the rows to delete.
How can I close a SQL Server connection in PHP?
To close a SQL Server connection, you can use the sqlsrv_close() function. This function takes the connection resource as a parameter and returns true on success, or false on failure.
How can I handle transactions in PHP using SQLSRV?
SQLSRV provides several functions to handle transactions. You can start a transaction with sqlsrv_begin_transaction(), commit a transaction with sqlsrv_commit(), and roll back a transaction with sqlsrv_rollback(). These functions take the connection resource as a parameter.
David is a senior programmer at Rastar Digital Marketing, where he helps develop database-driven marketing tools for various industries.